Planning an Excel Solution: Requirements and Design

"Can you make me a monthly sales dashboard?" sounds like a request, but it isn't a specification. It doesn't say who will use the workbook, what they'll decide with it, how current it must be or how anyone will know it's right. Expert builders settle those questions before writing a formula, because they decide the structure of everything that follows. This tutorial walks through turning that one sentence into a design brief you can build from and test against.

Start with the decision, not the dashboard

Every useful workbook answers one question for one named person. Before opening Excel, ask the requester:

• Who will use it? A person or a role, not "the team".
• What will they decide with it? If nothing changes based on the numbers, it's a data dump, not a solution.
• When do they decide? Weekly meeting, month-end review, board pack.
• What would they do if a number looked wrong? The answer tells you which numbers need the most checking.

For the sales request, the conversation might end with: The regional VP decides each month whether a region is on pace to hit its monthly target, and moves support to regions that are behind. That one sentence rules a lot out. It doesn't need product-level detail, real-time data or a color scheme debate. It does need targets, a clear definition of "on pace" and region totals that match the books.

Pin down inputs, outputs and refresh cadence

Next, write down what goes in, what comes out and how often it has to be current.

• Inputs: every source, who provides it and in what form. "Monthly sales export from the ERP as CSV, from Finance" and "regional targets, typed by Sales ops once a year".
• Outputs: what the reader actually sees. "One page: each region's sales to date, target, percent of pace and an on-pace flag, plus a trend chart."
• Refresh cadence: how often the numbers must be updated, and what triggers it.

The cadence shapes the build more than the data does. The same sales data needs a very different workbook depending on how often it refreshes:

For the regional VP, monthly, once the books close is right: the sales numbers aren't final before then, so a daily refresh would show figures that later change.

Draw a workbook map

A workbook map is the list of layers the data passes through, in order, with what each one holds. Sketch it on paper before you build:

Data flows in one direction, from Source to Report, and QA reads from both ends. A small workbook can combine layers, such as Staging and Model in one Power Query step list, but the order never changes. When a number on the Report is wrong, the map tells you where to look next.

Make every number traceable

The map is also what gives you lineage: the ability to point at any number on the Report and trace it back to the source rows behind it. Without lineage, you can only hope a number is right. With it, you can defend it. A few habits make lineage possible:

• Keep an identifier on every row (order number, invoice ID) all the way through, so a total can be broken back down into rows.
• When you combine files, keep the file name as a column, so every row says which export it came from.
• Use Formulas› then Formula Auditing› then Trace Precedents to follow a worksheet formula back to its inputs, and the Query Dependencies view in the Power Query Editor (View› then Dependencies› then Query Dependencies) to see which queries feed which.

Keep an assumptions register

Every rate, cutoff or judgment call the model depends on gets an entry in the assumptions register: one visible cell, a name, a one-line reason and an owner. Nothing gets typed into a formula where the next person to edit it won't find it.

fx

	A	B	C	D	E
1	Assumption	Value	Name	Reason	Owner
2	On-pace threshold	95%	PaceThreshold	95% of the pro-rated target counts as on pace	Sales ops
3	Close day	5	CloseDay	Books close on the 5th working day	Finance
4	Tie-out tolerance	1	TieTolerance	Rounding can move a total by up to 1	Report owner

Name each value cell (click it, type the name in the Name Box and press Enter) and use the name in formulas. With a region's sales to date in C2 and its pro-rated target in D2, the on-pace flag reads:

=IF(C2/D2>=PaceThreshold,"On pace","Behind")Copy

Sales of 47,500 against a pro-rated target of 50,000 is exactly 95%, so the result is On pace. If Sales ops later decides 90% is enough, they change one cell and every flag updates. The Owner column says who is allowed to make that call.

Name a maintenance owner

A workbook with no owner is one nobody trusts six months later. The maintenance owner is a named person who:

• runs the refresh on the agreed cadence and checks the QA sheet before sharing,
• fixes the workbook when a source changes shape,
• keeps the assumptions register and change log current,
• knows who takes over when they're away, and hands over properly when they leave.

Write the owner and a backup at the top of the Notes sheet. If nobody is willing to own it, treat that as a sign the workbook shouldn't be built in this form.

Write acceptance criteria you can test

Acceptance criteria say what "done" means. Write them before you build, agree them with the requester and test the finished workbook against them. Each criterion should be a statement someone else could check and answer yes or no:

The left-hand criteria translate directly into QA checks. A QA sheet compares a figure from the source with the same figure on the report, allows a stated tolerance and returns a pass or fail:

fx

	A	B	C	D	E
1	Check	Source	Report	Tolerance	Result
2	Total revenue	486,250	486,250.4	1	PASS
3	Order rows	1,248	1,247	0	FAIL
4	Regions	4	4	0	PASS
5
6	Ready to publish?	FALSE

Each result uses ABS so it doesn't matter which side is larger:

=IF(ABS(B2-C2)<=D2,"PASS","FAIL")Copy

The revenue figures differ by 0.4, within the tolerance of 1, so E2 shows PASS. The row counts differ by 1 with a tolerance of 0, so E3 shows FAIL. One summary cell then tells the owner whether the workbook can go out:

=COUNTIF(E2:E4,"FAIL")=0Copy

Put it together: a one-page design brief

The design brief collects everything above on one page. For the sales request it might read:

Show the brief to the requester before you build anything. Disagreements are cheap to fix on one page and expensive to fix in a finished workbook.

Common mistakes

• Building before asking. The first version answers the wrong question, and the rework costs more than the conversation would have.
• No decision owner. A report "for everyone" gets checked by no one.
• Choosing a cadence the data can't support. Daily reporting on numbers that are only final monthly shows figures that later change.
• Assumptions inside formulas. =IF(C2/D2>=0.95,…) hides a business rule. Put 95% in the register.
• Acceptance criteria written after the build. They end up describing what was built instead of what was needed.
• Untestable criteria. "Looks good" can't fail, so it can't pass either.

Practice

Practice

Write a real design brief

1. Pick a vague request you've received, such as "can you clean this up" or "can we track this".
2. Write the decision it supports and the person who makes that decision.
3. List the inputs and outputs and choose a refresh cadence. Note what that cadence implies for the build.
4. Sketch the workbook map from Source to Notes.
5. Add one assumption to an assumptions register, with a name, reason and owner.
6. Name a maintenance owner and a backup.
7. Write three acceptance criteria that someone else could test, and turn one of them into a QA check.
8. Show the brief to the person who asked before you build.

Try the QA checks below: compare source and report figures against a tolerance, then decide whether the workbook is ready to publish.

Try it0 of 4 done

1. 1.In E2, show PASS if Source and Report differ by no more than the tolerance in D2, otherwise FAIL.Show hint
2. 2.In E3, run the same check on the row counts. One order is missing from the report.Show hint
3. 3.In E4, check that the report shows the same number of regions as the source.Show hint
4. 4.In B6, return TRUE only if none of the checks in E2:E4 failed.Show hint

E2fxFormula for E2Enter ↵

Click a dashed cell, type a formula in the bar and press Enter.

	A	B	C	D	E
1	Check	Source	Report	Tolerance	Result
2	Total revenue	486,250	486,250.4	1
3	Order rows	1,248	1,247	0
4	Regions	4	4	0
5
6	Ready to publish?

Check your skills

• Can you name the decision a workbook supports, and who makes it, before you open Excel?
• Can you sketch a workbook map with Source, Staging, Model, Report, QA and Notes layers?
• Can you write an assumptions register entry with a value, name, reason and owner?
• Can you explain what a maintenance owner is responsible for?
• Can you rewrite "looks professional" as an acceptance criterion someone can test?