A financial model forecasts a company’s performance and, when built correctly, becomes a narrative tool that guides optimal decisions. By integrating detailed schedules, six key attributes, logical sections, and print-ready formatting, you’ll create a dynamic, transparent, and user-friendly model that tells the full story behind every recommendation.
Creating a Comprehensive Financial Model
A financial model is used to forecast a company's financial performance. Creating a comprehensive financial model helps users make informed decisions about the future of the company.
To ensure accuracy, the model should include several schedules. By including the schedules listed in this resource, the financial model will properly reflect the operations of the company.
Revenue Schedule
Calculates revenue based on units sold and selling price. Considers production capacity.
Operating Costs Schedule
Calculates revenue based on units sold and selling price. Considers production capacity.
CAPEX/Depreciation Schedule
Tracks assets, purchase date, capital costs, useful life, and depreciation method. Helps determine future cash flow needs.
Income Tax Schedule
Shows differences between accounting and taxable income. Reflects tax issues and timing differences.
Working Capital Schedule
Calculates working capital based on income statement items and number of days or turns. Determines liquidity and cash flow needs.
Debts Schedule
Tracks all company debts, including loans and bonds, with details on interest rates, maturity, and repayment plans to forecast interest expenses and principal repayments.
Equity Schedule
Outlines the company’s equity structure including common and preferred shares, dividends, and retained earnings to asses changes in shareholders equity over time.
6 Key Attributes of A Financial Model
In order to clearly and effectively tell the story of a company to decision makers, a financial model needs to serve as a powerful communication tool.
These goals can be achieved when a model is developed with 6 Key Attributes:
1. Flexible
The model contains scenarios with multiple cases (i.e. base case, best case, worst case) for the most critical variables.
2. Intuitive
The reader can easily follow and understand the model based on the flow of the pages.
3. Transparent
Every formula can be understood whether the reader clicks into the cell, or looks at a printout on paper.
4. Dynamic
Any changes to the assumptions should properly flow through the model.
5. Tells a story
The model is a powerful communication tool that allows for optimal decision making.
6. Transferable
When a model is well designed and well built, colleagues and clients will be able to use the file easily.
Key Sections of A Financial Model
1. Cover Page
A well-designed cover page, complete with clear headings, sets the stage by explaining the purpose of the model and inspires confidence in your decision makers. Your financial model is a financial presentation.
2. Executive Summary
The first thing a reader of a model wants to know is "What's the answer?" Don't make your readers flip through the model to get to the answer. Put your summary up front and they're guaranteed to keep reading.
3. Assumptions
As soon as your readers understand the answer, the next thing they want to know is "How did you get there?" Your assumptions tell the story of how you arrived at your answer.
4. Scenarios Page
These are the key driver assumptions that are hard to forecast and hard for management to control, but can have a big impact on the success or failure of the company.
5. The Engine
The final section of an optimal model is the engine; this includes the financial statements and the schedules that were used to calculate the results. Most models require schedules to calculate each of the primary line items on the company's financial statements.
Finally, because many decision makers review models on paper or as PDFs, the model applies best practices for formatting and printing to maintain clarity off-screen.
Tips for Printing Financial Models
A good financial model must also work on paper or as a PDF. The vast majority of decision makers today are still making decisions while looking at a document on paper or reviewing the model as a PDF on their screen.
Most senior executives won’t be clicking their way through a spreadsheet file and playing with the formulas. They want to see something that looks and feels like a presentation. That means all financial models need to be powerful communication tools.
In other words, it is not enough to just present facts and figures. The model needs to tell a story that will grab the attention of those looking at it and then give them the information they need to make an optimal business decision.
To optimize the printing of a model:
1. Set up the model in print-size pages
- Approximately 8 to 10 columns of numbers with row labels
- Approximately 35 to 50 rows with page titles
2. Page Layout Tab (Alt P S P) - Expand Page Setup Menu - Page Tab
- Set to Landscape
- Scaling should be 85% to 95%
3. Page Layout Tab - Expand Page Setup Menu - Margins Tab
- Minimize the margins: .25” to .4”
- Click the option to centre on page horizontally
4. Page Layout Tab - Expand Page Setup Menu - Header/Footer Tab
- Click Custom Footer and click on the [Page] and [Pages] buttons
- Can also use the date, time, and file path buttons
5. Page Layout Tab - Expand Page Setup Menu - Sheet Tab
- This is the key technique to maintain consistent print ranges that do not have to constantly be reset.
- In the Sheet box, enter each page range, separated by commas
- Each page on the model should be separated by at least one blank row
