- Major sheets are listed in the Cover sheet.
- Click on the page number button on the Cover sheet to go to the
section of the model you want- To return back to the Cover sheet,
click on the Cover Page button from any sheet in the model.
- In the Cover sheet, click on the Print All Sheets button to print
all the sheets in the model.
- Click on the Modify Major Assumptions button on the Cover sheet.
This will take you to the Major Assumptions sheet where major
assumptions can be changed easily and key operating results seen on
the bottom half of this sheet- You can see the impact of these
major assumptions without having to go into different parts of the
model
- You can save the results from these major assumptions by clicking
on the Save Sheet button in the Major Assumptions sheet- A new
sheet is created showing the assumptions and results of that
scenario.
- After running a variety of scenarios, you can then decide on one
scenario and can easily make that the scenario be the basis for the
model- Go to the scenario sheet for the scenario that you have
chosen and click on the Paste Results button- The assumptions from
that scenario will then be pasted into the Major Assumptions sheet,
which drives the model.
- Names are used in many formulas throughout the model to make the
model easier to develop and easier for the reader to review- For
example, the gross margin formula can be shown as sales - cost of
sales, rather than C5- H5.
- The model includes integrated financials- profit & loss,
balance sheet and even cash flows- The numbers tie together without
having to force any numbers (“plug figures”).
- The model is mainly driven by assumptions where are explicitly
spelled out on the Assumptions sheet- This sheet has additional
assumptions beyond the major assumptions mentioned above, in
addition to the major assumptions, which are fed in from the Major
Assumptions sheet- This not only makes the assumptions clearer it
also makes changes to the model easier by allowing the user to make
changes in just two sheets (Major Assumptions and Assumptions).
- Major financial results are highlighted on one sheet (Summary)
for a quick review of the overall results.
- Key metrics are put together on the Summary sheet to provide more
insight into the financial model- These include many operating
metrics that go beyond traditional financial reporting metrics and
serve as a good reality check on the business case.
- This can vary by the client- Usually the model goes out 5 years,
but in some cases 3 years is sufficient, depending upon how soon the
company can become profitable- In some cases, it makes sense to
also do a 1st year model by month, which is built from
the annual model to save time- Where a monthly 1st year
model is also used, it is then linked into the annual model, so the
annual model includes the 1st year model results- If
there are real uncertainties about the timing of results and
financing needed in the 1st year, then it is usually
worth it to spend the additional effort for a 1st year
monthly model.
- An equity valuation is included on the Equity sheet, which also
provides information such as the cash raised and shares issued- The
valuation section takes the expected valuation from an IPO or sale
of the company and then uses discounted cash flow
(“DCF”) analysis to estimate a value of the company at
the time of each round of financing- The DCF value is then compared
to the money to be raised for each round and show any discount in
the valuation given to the investors.
- One challenge with financial models is that cash flows need to be
recalculated many times to get the updated cash flow number- This
is because several factors are tied together- expenses impact cash
on hand, which impacts interest income, which impacts net income,
which impacts taxes, which impacts cash flow and continues the
cycle- This model automates the process by performing 50 iterations
in under a minute- To use this feature, go to the Line of Credit
sheet or the Assumption sheet and click the Line of Credit Test
button- Any time a change in assumptions or other inputs in the
model are made, you can click this button and get the correctly
updated cash flow.
- A separate file with just the Summary sheet, which is an exact
copy of the one in the 5-year file, is kept so you have just a
one-page summary for giving out when appropriate.
- Pictures can tell the story and the model includes a number of charts in a separate chart file for your viewing pleasure- These charts focus on key areas to emphasize the strength of the business- Several charts include results plotted on different axes so you can better see how key results correlate.
Contact Us:
Jon Paul
President, Value Added Finance Inc.
jon@valueaddedfinance.com
jpaul@mba1980.hbs.edu
847 372-1963