Index
Sheet Index:
Financial Statements The page has your financial statements and common sized analysis
Balance Sheet Lead This sheet uses ratios and the IS to drive working capital accounts
Fixed Assets Lead Sheet Lead sheet for your fixed assets (instructions on that page)
Operating Expense Lead Sheet Lead sheet for your operating expenses (instructions on that page)
Revenue Lead Sheet Lead sheet for your revenues (instructions on that page)
Financial Statements
PROJECTED INCOME STATEMENTS
Common Sized
2020 2021 2022 2020 2021 2022
Revenue
Cost of Goods Sold
Gross Margin
Operating expneses
Selling Expenses
General & Administrative Expenses
Depreciation Expense
Total Operating Expenses
Income from Operations
Other revenue (expense)
Interest
Pretax Income
Income Tax Expense
Net Income
PROJECTED STATEMENT OF RETAINED EARNINGS
2020 2021 2022
Beginning Retained Earnings
+Net Income
-Dividends
Ending Retained Earnings
PROJECTED BALANCE SHEETS
Opening 2020 2021 2022 2020 2021 2022
ASSETS
Cu
ent Assets
Cash $ - 0
Accounts Recievable
Inventory
Prepaid and Other Assets
Total Cu
ent Assets
Long Lived Assets
Property, Plant and Equipment
Accumulated Depreciation
Net P,P&E
TOTAL ASSETS
LIABILITIES & EQUITY
Cu
ent Liabilities
Accounts Payable
Accrued Other Cu
ent Liabilities
Total Cu
ent Liabilities
Long Term Liabilities
LT-Debt $ - 0
TOTAL LIABILITIES
OWNERS' EQUITY
Common Stock $ - 0
Retained Earnings
TOTAL EQUITY
TOTAL LIABILITIES & EQUITY
PROJECTED STATEMENT OF CASH-FLOWS
2020 2021 2022
Cash Flow from Operating Activities
Net Income
Adjustments to reconcile Net Income to
cash used in operating activities:
Depreciation
Change in Operating Assets & Liabilities
Accounts Receivable
Inventory
Prepaid and Other Cu
ent Assets
Accounts Payable
Accrued & Other Cu
ent Liabilities
Net Cash generated (used) in Operating Activities
Cash Flow from Investing Activities
Purchases of Property, Plant & Equipment
Net Cash generated (used) in Investing Activities
Cash Flow from Financing Activities
LT-Debt
Additional Equity Financing
Distributions to Stockholders
Net Cash generated (used) in Financing Activities
Net Change in Cash
Beginning Cash Balance
Ending Cash Balance
Revenue Lead Sheet
Revenue Lead Sheet 2020 2021 2022
REVENUE SOURCE 1
$ per unit
Units sold
Total source 1 $ - 0 $ - 0 $ - 0
REVENUE SOURCE 2
$ per unit $ - 0 $ - 0 $ - 0
Units sold
Total source 2 $ - 0 $ - 0 $ - 0
REVENUE SOURCE 3
$ per unit $ - 0 $ - 0 $ - 0
Units sold
Total source 3 $ - 0 $ - 0 $ - 0
REVENUE SOURCE 4
$ per unit $ - 0 $ - 0 $ - 0
Units sold
Total source 4 $ - 0 $ - 0 $ - 0
REVENUE SOURCE 5
$ per unit
Units sold
Total source 5 $ - 0 $ - 0 $ - 0
Total Revenue to inc stmt $ - 0 $ - 0 $ - 0
COGS %
COGS $ $ - 0 $ - 0 $ - 0
Instructions: This lead sheet has 5 sources of revenue built into the model. The product of $per unit and units gives you revenue by source. The sources total up on Row 32. If you need to add more just insert some rows and add the toal by source to cell by year to line 32.
Operating Expense Lead Sheet
Expense Lead Sheet 2020 2021 2022
Selling Expenses
Advertising $ - 0 $ - 0 $ - 0
Other $ - 0 $ - 0 $ - 0
Other $ - 0 $ - 0 $ - 0
Other $ - 0 $ - 0 $ - 0
Other $ - 0 $ - 0 $ - 0
Other $ - 0 $ - 0 $ - 0
Other $ - 0 $ - 0 $ - 0
XXXXXXXXXXTotal Selling Expenses $ - 0 $ - 0 $ - 0
General & Administrative Expenses
Rent $ - 0 $ - 0 $ - 0
Other $ - 0 $ - 0 $ - 0
Other $ - 0 $ - 0 $ - 0
Other $ - 0 $ - 0 $ - 0
Other $ - 0 $ - 0 $ - 0
Other $ - 0 $ - 0 $ - 0
Other $ - 0 $ - 0 $ - 0
Other $ - 0 $ - 0 $ - 0
Other $ - 0 $ - 0 $ - 0
Other $ - 0 $ - 0 $ - 0
Other $ - 0 $ - 0 $ - 0
XXXXXXXXXXTotal General & Administrative $ - 0 $ - 0 $ - 0
Instructions: This lead sheet has two primary components Selling expenses and General and Administrative expenses. Simply add in your list of expenses in Column B and the dollars in the respective years and it will total and link to the Income Statement.
Fixed Assets Lead Sheet
Depreciation 2020 2021 2022 2023 2024
ADDITIONS Life Cost
2020 Type 1 0.00 $0
2021 Type 1 0.00 $0
2022 Type 1 0.00 $0
2023 Type 1 0.00 $0
2024 Type 1 0.00 $0
- 0 - 0 - 0 - 0 - 0
Life Cost
2020 Type 2 0.00 $0
2021 Type 2 0.00 $0
2022 Type 2 0.00 $0
2023 Type 2 0.00 $0
2024 Type 2 0.00 $0
- 0 - 0 - 0 - 0 - 0
Life Cost
2020 Type 3 0.00 $0
2021 Type 3 0.00 $0
2022 Type 3 0.00 $0
2023 Type 3 0.00 $0
2024 Type 3 0.00 $0
- 0 - 0 - 0 - 0 - 0
Life Cost
2020 Type 4 0.00 $0
2021 Type 4 0.00 $0
2022 Type 4 0.00 $0
2023 Type 4 0.00 $0
2024 Type 4 0.00 $0
- 0 - 0 - 0 - 0 - 0
Table to drive Financial Statements
Depreciation Expense
PP&E Cost
Accum. Depreciation
Net PP&E
Instructions: This lead sheet has 5 different buckets of fixed assets. Simply put the useful life into column C and the Cost in column D for the year you plan to purchase the asset and it will depreciate the asset and link it over to the Income Statement, Balance Sheet, and Statement of Cash-Flows. So you could group Type 1 = all assets with a useful life of 5 years. Type 2 all assets with 10 year depreciable life. It uses straight line and zero residual
Balance Sheet Lead
WORKING CAPITAL ASSUMPTIONS 2020 2021 2022
Accounts Recievable -
Inventory -
Prepaids & Other -
Accounts Payable -
Accrued & Other Cu
ent Liabilities-
Information for Initial LT-Debt
Term (enter term in years)
Rate (enter interest rate)
Bo
owing (link to opening BS)
Pmt
Interest
Principal
Balance
Key Financial Ratios
2020 2021 2022
Return on Assets
Return on Equity
Total Asset Turnove
Fixed Asset or PPE Turnove
Cu
ent Ratio
Quick Ratio
Debt-Equity Ratio
ACCT (FINA) 470
Forecasting project
Introduction:
This project has two primary objectives. First, you will develop a basic understanding of Excel. Second and more important, the project is designed to help you understand how transactions and events captured in an accounting system impact financial statements and consequently the key metrics used to analyze a firm.
I encourage you to make your forecast of a simple company you may have thought about starting. When the accounts relate to something tangible in your mind it is easier to understand how the financial statements work. I will not grade the forecast based on whether it is a viable business model, but instead on how well the model works (e.g., can I change your revenue assumptions and see the impact on each of the financial statements and does the balance sheet still balance). This project should cause you to struggle at some points. It is well-known that what we struggle with is what we learn.
Task:
It is an individual project, with each individual developing a three year financial forecast (I am fine with you working together to understand how things flow, but you cannot simply copy another project). To help you be successful, I have posted a number of videos in the Project Module. These videos will walk you through key portions of the project.
Process:
Step 1: Open up the forecasting spreadsheet and have a quick look around. You will find the spreadsheet with the following tabs in the Project Module:
1. Financial Statements
2. Revenue Lead Sheet
3. Expense Lead Sheet
4. Fixed Asset Lead Sheet
5. Balance Lead Sheet and Analysis
Step 2: On the financial statements tab, I provide you with a skeleton of the financial statements. You will end up having formulas for nearly all the accounts on your financial statements:
1. Income Statement
2. Statement of Shareholders Equity (or simply Retained Earnings)
3. Balance Sheet
4. Cash Flows
Step 3: Develop your revenue lead sheet. You need to make up a price per unit and number of units sold for some of the revenue sources (I put five in the model, use as many as you like) this will result in the revenue produced by each revenue source. I have put a total revenue line on row 32 of the Revenue Lead Sheet tab. You should make the total revenue line on the Revenue Lead Sheet tab link over to the Income Statement on the Financial Statements Tab for total revenue.
This is also a good place to estimate COGS. Normally it would be specific to each revenue source, but in this example we are going to apply a single Cost of Goods Sold percentage (COGS%) to all revenue sources. At the bottom of the Revenue Lead Sheet you will see a COGS % row (row 34) you should input a percentage for COGS in these cells. Then, just like total revenue, you will link COGS $ to the Income Statement on the Financial Statements tab.
Step 4: Develop your expense lead sheet. This should be the build-up of the assumptions you will use to forecast expenses. I left a large number of “other” expenses which you can change to be the specific expenses you think are relevant to the category (selling or general and administrative). You do not need to use all of these rows and can leave some blank, but things like rent, insurance, salaries, wages and benefits could go here. When you are done, link Total Selling Expenses and Total General and Administrative to the Income Statement on the Financial Statements Tab.
Step 5: Prepare an opening balance sheet on the Financial Statements tab (there is a video for this “Opening Balance Sheet and Debt Amortization”). This will reflect your capital structure and will drive the amortization table you will create for debt (so you have to have some level of debt). The simplest opening balance sheet is to have some combination of debt and common stock on the liabilities and equity side, and an equal amount in cash on the