Download the sample workbook (.xlsx)
What is in the sample workbook
The sample workbook is a small financial model in a single .xlsx file. It is built to be read from start to finish: inputs, raw data, calculations and outputs are kept on separate sheets, and every calculated number is a formula you can inspect. It opens in Microsoft Excel, Excel for the web, and other spreadsheet programs that read .xlsx files.
README sheet
An orientation sheet. It explains the inputs, calculations and outputs structure, the colour key used throughout the file (blue for inputs, black for formulas, green for links to other sheets, yellow for cells that need attention), and short experiments you can run to see the model respond.
Assumptions sheet
All editable inputs in one place, each stored as a named range: BaseRevenue, GrowthRate, GrossMargin, VarOpex, FixedOpex, TargetRevenue and TaxRate. A notes column explains what each input means and which calculations depend on it. Because formulas refer to names, they read as =BaseRevenue*(1+GrowthRate) rather than cell addresses.
Data sheet
Twelve sales records stored in an Excel Table named tblSales. Data validation restricts Region to North, South, East or West and Units to positive whole numbers. Formulas refer to the table by name, so new rows are included without editing any range.
Forecast sheet
A twelve-month projection. Forecast revenue grows from BaseRevenue by GrowthRate each month, actual revenue is pulled from tblSales with SUMIFS, and the rows below work down from revenue through cost of goods, gross profit, operating costs, operating profit and tax to net income. Conditional formatting colours the variance between actual and forecast.
Dashboard sheet
A summary of six figures, including totals, averages, variance and progress against TargetRevenue. Every figure links to the Forecast or Data sheet, and the best-performing region is found with INDEX and MATCH over per-region totals. A column beside each figure explains how it is calculated.
How to use it
- Download the file and open the README sheet first.
- On the Assumptions sheet, change GrowthRate and watch the Forecast and Dashboard update.
- Add a row to the bottom of tblSales on the Data sheet and confirm that the totals include it.
- Click a Forecast cell and paste its formula into the Formula Explainer to read it step by step.
- Use Formulas > Trace Precedents on a Dashboard figure to follow it back to its source.