Transcript Pro-Forma Generator

An Excel Spreadsheet
to Streamline the Preparation of
Pro-Forma Financial Statements

Generates Pro-Forma Financial Statements from
data entered into a setup page
Sales,



Income, Cash Flow, Balance Sheet
Prof/Loss
Cash Flow
Generates graphs of key variables
Generates Common Size (%) and compares to
norms for your industry
Pro-Forma Generator +
2

Model only allows for one sales value


Model only allows for two categories of COGS


For multiple products or services this is the sum of total
sales over all products
Build composite (see Introduction tab)
Five-year projections are generated, month-bymonth
Pro-Forma Generator +
3




Be sure to read the contents of the Introduction
tab before going on to Setup
In Setup, turquoise cells are to be filled in by
the user. Numbers in other cells are generated
by the spreadsheet
Some Setup values may be difficult to estimate.
Instructions and suggestions accompany these.
A breakeven sales value is calculated
Pro-Forma Generator +
4
Pro-Forma Generator +
5
Pro-Forma Generator +
6

Descriptions may be changed


Expenses are propagated throughout unless
changes are made manually


Tailor to your specific expense items
Revised values are propagated from there on
Model allows for inflation of expenses

Step changes may be entered manually, e.g., adding
a new employee would increase salaries
Pro-Forma Generator +
7
Table of Inflated
Values
Inflation Factor
Pro-Forma Generator +
8
Pro-Forma Generator +
9
Model allows for one short term and one long-term loan
Model comes with sample data. Filling in the setup sheet overlays
all values but those manually entered. To reset these for the new
case, simply press the reset button.
Pro-Forma Generator +
10
Functions of the spreadsheet are organized by eight
tabs along the bottom:
Tab 1, Introduction, explains the limits and
assumptions of the model
 Tab 2, Setup, is where you enter the data used in
generating the Financial Statements
 Tab 3, Income, generates the Income Statements
 Tab 4, Graphs are generated automatically
More …

Pro-Forma Generator +
11
Note the
Propagation
Pro-Forma Generator +
12
Pro-Forma Generator +
13
Pro-Forma Generator +
14
Note smoothing of month to month sales
For all graphs, scales may need to be adjusted to fit your data
Pro-Forma Generator +
15
Pro-Forma Generator +
16
Note “hills” in years 1 and 3 – cash infusion
Cash amount can get high enough that it should be
better applied to debt, equipment, dividends, etc.
Pro-Forma Generator +
17




Tab 5, Intermediate, is a
Watch for
series of calculations to be
cash going
negative
used in later statements
Tab 6, Cash Flow, generates the Cash Flow
Statement
Tab 7, Balance Sheet, generates the Balance
Sheet
Tab 8, Compares Common Size % to norms for
your industry
Pro-Forma Generator +
18
Manually entered, as needed
Manually entered, as needed
Pro-Forma Generator +
19
It
Balances!
Pro-Forma Generator +
20
Pro-Forma Generator +
21
End of Year 1
Pro-Forma Generator +
22
Return to jbv.com
Pro-Forma Generator +
23