finance

Proforma Modeling Problem Set

In this problem set your task is to set up an integrated excel model of Clean Co’s income statement,

balance sheet, and cashflow statement with 2 historical years and 3 projected years. See excel file for

Clean Co included with assignment.

General Principles for modelling in Excel:

Matrix Integrity. You should be able to copy formulas across sheets and maintain correct

relationships among cells.

Flexible. Do not use hard numbers in formulas. Make sure all assumptions are grouped

together and clearly identified.

User-Friendly. Break calculations down into simple steps.

Steps

1. Re-create the Clean Co excel file from scratch with Assumptions, Calcs, IncStat, BalSheet, and

Cashflow tabs so you understand clearly the relationships among the various tabs. Make sure

the historical balance sheets balance before taking the next step.

2. Calculate projected Sales on the Income Statement based on sales growth assumptions.

Calculate Net PP&E in Calcs section. Start with beginning PPE from the most recent historical

year, add Capital Expenditures, and subtract Depreciation to get ending PPE for the first

projected year. This will be the Net PPE line for the first projected year on the balance sheet.

3. Project the remaining income statement. Use the assumptions for Sales (% growth), COGS (% of

Sales), SG&A (% of Sales), Research & Development (% of Sales), Taxes (% of Pretax Income)

noted under Assumptions. Calculate pretax income, taxes, and net income for projected years.

4. Project the balance sheet. Make assumptions for accounts receivable (% of Sales), Inventories

(% of Cost of Goods Sold), and Accounts Payable (% of Cost of Goods Sold). Assume Long-Term

Debt is unchanged from prior historical year. Assume projected Cash is zero.

5. Calculate Retained Earnings in the Calcs section. Start with beginning RE from most recent

historical year, add Net Income, and subtract dividends to get retained earnings for first

projected year. Link projected retained earnings from Calcs tab to each projected year’s balance

sheet. Project cash flow statement based on changes in projected balance sheets. You should

have Cash from Operations, Cash from Investing, and Cash from Financing sections. Add these

sections to calculate Change in Cash.

6. If Change in Cash is positive, link this to the Projected Excess Cash line on the projected balance

sheet. The balance sheet should now balance. If the Change in Cash is negative, link this to

Projected Borrowing on the projected balance sheet. The balance sheet should now balance.

7. On the balance sheet add a line at the top calculating the difference between assets and

liabilities+equity. If this line is zero throughout each historical and projected year you have

constructed the model correctly.