Worked Executable Requirement Example

Executable Requirements

A video game company wants a bedside lamp simulation where the player can:

  1. Plug it in (plug-in)
  2. Unplug it (unplug)
  3. Click the switch (switch)

The lamp may turn on or turn off the light based on these inputs.

Clarifying the User Story

The user story sounds simple enough. I bought a lamp in the real world to use as my basis and discovered that the real lamp didn’t work so simply as the story implied.

Now what?

I could just assume I understand what the user wants. I’ve got a very good chance of being wrong, but technical people know better, right? No, actually, we don’t.

Instead, I’m going to need to show the customer enough that they can give me a decision on what behavior they want for the lamp in their game. And I may actually need to show a few different kinds of lamp.

The question becomes, then, how do I capture their intention in such a way that all stakeholders understand the same thing? The text didn’t do it … I’ll need to do something better.

A Developer’s Impatience

I could build a state machine diagram!

Building a state machine diagram can be done for design or for analysis. In this case, where the purpose is to capture something in the problem domain the state diagram isn’t about code but rather is about the real world.

Here’s a UML state diagram for the lamp, where set(intensity) is an action and off and not(off) are guards for if the light is lit or not (needed only when pulling the plug).

There are a few useful points about the diagram’s intention that matter to a programmer and directly affect the nature of the API to the simulation.

For instance, if the light is off and the plug is pulled, there is no action performed. This is crucial: it’s legal for an event to have no action performed.

The developers reading this might already see a basic code interface and implementation. It would be easy enough to write. Though, I must ask all developers reading this: what should the code do?

That’s a strange question. Here are three radically different ways of looking at just that machine when considering the raw code written:

  1. The code should accept an event and return an action (possibly null/None)
  2. The code should accept an event and carry out the action internally (requiring the code know how to cause the bulb intensity to change in the game engine!)
  3. The code should accept an event and a light and carry out the action against the light given which would have the code in the game engine

There are doubtless other solutions, but those three show that a developer needs to know not only the problem, but the means of implementing the solution.

This diagram doesn’t dictate which of the three approaches (or many others) is taken. It only reflects that three events lead to a series of optional actions.

The three approaches have radically different dependencies, though. The first is pure functional with no knowledge of the game engine. The second requires that the game engine be known to the lamp code. The third requires that there be a “light interface” and that the lamp be dependent on the interface though not on the game engine. Testing the three would involve very different trade-offs.

As written, it’s not sufficient for a developer. To make it useful to a developer, it would need supporting class diagrams and some kind of interaction diagram to show the events and actions between origin of event and receiver of action. The suite of directions would need to indicate how to address the dependencies, and even the testing.

All of this is, honestly, premature. Until the client agrees with what the lamp does (in-game), building more detail is fun but not useful. I rushed to implementation by this diversion. An easy mistake and painfully common.

I still need to propose to the client that I follow the real lamp. This diagram isn’t going to be comprehensible to the client. I need to clarify the requirement, not start the design!

Creating a More Useful Executable Requirement

A disclaimer: this requirement, even though it’s led to a lot of confusion in the real world when I was making the exercise and testing it against a real lamp, is far more trivial than almost any requirement of a modern enterprise system, hard real time system, or even game development effort. I’m beating a decaying horse for illustrative purposes. The use of Excel to make a Proof of Concept (PoC) is powerful, and I’ve written elsewhere about the ubiquity of Excel. Even so, it would be easy to tackle the simple prose for this user-story and clarify it.

That said, there are actually many reasons besides communication for making a PoC as part of the requirement stage (be it Excel or any other executable language). Among them:

  1. People can rarely state their needs but they can often say “That is not what I meant” when they play with something that runs. This is why mockups are so crucial for UX design.
  2. Building the PoC forces assumptions to be addressed — the act of coding always does that since computers have no assumptions.
  3. A PoC is an excellent basis for a User Acceptance Test.

An executable requirement is a kind of PoC. To be a requirement, the non-technical customer must be able to consume it. For a PoC, that means run it.

That’s the main driver for using Excel for requirement PoC creation: the XLSX file can be opened and interacted with by almost any customer. Even a Python program would require that the customer install Python on their machine to run it. Building something compiled requires targeting a customer’s OS (and possibly version of the OS or hardware platform). Excel solves all of these issues immediately. Granted, a web page could be made and hosted that had a simulation (and there are some amazing sites that make it easy to build these) and that’s incredibly useful for the mockups of UX. However, pure logic is hard to show in imagery. You’d end up building the entire lamp (in a simulator) instead of examining its logic.

How, then, to build an Excel document that captures this intent, complete with the sequential steps through the various light intensities and being energized or not, all in response to events and indicating what to do to the light.

I’ve already written about how to represent the sequential intensity and stepping through them in Executable Requirements Concept. If you haven’t read it, please do so, as I’m going to continue with the document and extend it to be complete.

First, I’ll add the events into a named region so that I can pretty-print the event. I’ve highlighted the events named region in the image:

Now, it’s possible to create an instance of a lamp. This means the lamp before any events have ever been sent. So it just has initial values.

On the Work tab, I make sure it’s clear of everything and start anew with the lamp powered down.

Immediately, there’s a problem. There isn’t a setting for the intensity when there’s no power. As humans we understand that no power means off. However, that’s not something the computer knows. I’ll add a new bit of knowledge to the Tables tab:

Notice that the name light_no_power is set to F2.

The lamp’s initial values must be used as the values “before” the first event. Later “after” values become the new “before” values. In other words, causes occur, and produce effects. The effects are present when the next causes come, producing more effects. Causality is what we’re capturing: inputs are processed into outputs, changes happen.

The sheet must show incoming events, the before, the after, and the action that results. Since spreadsheets don’t loop, each event and it’s processing get a row and copy/paste of a row provides the next iteration of the loop. This is very different from how most programming languages work, but it has the benefit of showing all computation at once instead of attempting to trace a log.

By convention, to ensure that the computation advances in a way that doesn’t confuse the reader, two rules are followed in layout of the working sheet:

  1. All rows above the current row on a working sheet may be referenced freely (this represents reference to prior computation, inputs, and results)
  2. On the current row, a formula in a cell may only refer to columns that are left of it (this represents intermediate values already prepared)

Breaking these rules in a spreadsheet is easy, but in a later article I’ll show that it makes some programs literally impossible to write for handling real data outside the PoC work.

Here, then, following those two rules, is the initial layout for processing the lamp:

The Action Id of column L is a brightness command for a light. I happened to re-use the same numbers as the lights status table. This isn’t a rule or even a guideline. It was just a convenient side-effect. In a system where the actions are different, there would have been another table.

Id values as numbers are used purely because it’s easier to punch in a single digit than to type in (and spell properly) the text of the event names and action names. Vlookups are used so that when the user types in the event code, they can see what it means. Likewise, the Action Id has a Meaning next to it from a vlookup as well.

The power of Excel comes when formulas are entered into the cells to perform computation. Creation of the row that processes input entails creating a formula for each cell in the row for each column that has a name. In our case, the following formulas must be created:

  • B: Event Name must show what the Event Code means
  • D: Before Event – Light must show the light after the PRIOR computation
  • E: Before Event – Meaning must show the friendly status of the light (the text) after the PRIOR computation
  • F: Before Event – Energized must show if the power was present (TRUE) or absent (FALSE) after the PRIOR computation
  • H: After Event – Energized must show if the power is present or absent resulting from the current event
  • I: After Event – Light must show the status of the light resulting from the current event
  • J: After Event – Meaning must show the friendly status of the light (the text) resulting from the current event
  • L: Action – Id must show what (if any) signal resulting from the current event is performed or returned
  • M: Action – Meaning must show the friendly intention (the text) resulting from the current event

The input event is an Event Code in column A. The input area is colored (per the default style) to make it clear where the data is entered. The formula cells are default color, and reflect the work as it’s being performed. The Action Id is the official result and is colored (per the default style) to show the computation’s official result.

Most of the formulas are actually trivial. All the Before Event columns are direct references to the After Event column of the same name on the prior row. For instance, D4 holds =I3.

The actual logic for the entire lamp lives in the formulas in cells After Event – Energized (H) and After Event – Light (I).

The logic for being energized is summarized in prose as: when the event is plug-in, energized is true; when the event is unplug, energized is false; otherwise, energized = prior energized. In Excel:

The formula is much more succinct than the prose. It’s not the sort of formula that someone doing number crunching would write, but it’s clearly well within Excel’s range. Such logic is very common in requirements — and a business analyst needs to be able to be explicit and clear whether in formula or prose in order to adequately capture a requirement.

The benefit to expressing it in formula is that it can be exercised. A user can fill in a value into A4 and see the field in H4 update. A user. Not on a Power Point slide, not demoed by a developer, but the user personally can interact with the sheet. Excel provides the UX and the analyst provides what is understood and the user can run it and agree or not.

The new setting for the light is a more complex expression. It’s built the same way, with use of if and other logical expressions:

That formula is not trivial, but it’s not unusual either.

The Action Id in L4 is the resulting delta from what was before and what is after:

The Action Meaning in M4 is the formula =IF(L4<>””,J4,””) which reflects blank or J4 if present.

So, what does the PoC tell us? That if the lamp is plugged in the light comes on bright. What a lot of work to show that!

How is all this useful? Let’s hit the switch a few times and see … copy the row from 4 to 5, then put ‘3’ as input, then copy that row a few times …

The copied rows reflect the changes as the lamp responds. Each flick of the switch advances it, which makes sense. Let’s put in some other sequences…

Switching a turned-off lamp has no effect.

What about a bunch of unplug?

A user, playing with Excel, can drive the code to their own tests. So can QA (or SDET engineers) when they build tests.

Allowing the Customer to Understand

Writing the Excel took a few minutes — getting it right, a few more. It’s not realistic to do this sitting with a customer unless it’s shockingly simple (this one would have been complex enough that only someone really well versed in Excel logic could have done it with a user beside them).

The result, though, can be exercised sitting beside a normal person. They can be given a copy to take with them. The document, when done, isn’t dead text.

And it’s not just the customer. The rest of the team can make sense of it as well.

Programmers won’t be able to “code” from it directly. I’ll write a later article to show why. But for a hint: put an illegal value into an Event Code cell.


The Excel file rich-lamp.xlsx is a document that can be reviewed. It can also be run. Our requirement for this lamp and it’s stateful behavior is precisely expressed and any sequence of legal operations can be attempted by the non-programmer user through entering values into cells to get results.

There can be errors in the formulas, the user can be wrong in what they ask for, but there’s no human interpretation of what is express. The machine will indicate what the logic says. This is much stronger than a prose description, which is subject to interpretation by all parties.

These benefits are present in all prototypes, PoCs, mockups, etc. It’s crucial that requirements be clear, which is brutally hard in prose.

Let’s make our requirements executable. This is how to do it with Excel.

Keep the Light,

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s