finance

please solve the problem set 1  in the attachments 

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.

Assumptions

Hist

Growth

5% 5%

61% 61%

as % of Beginning PPE

27% 27%

10% 10%

%

30.0% 30.0%

%

3.0% 3.0%

8% 8%

% of COGS

36% 36%

% of COGS

7% 7% 7%

% of Sales

15% 15%

																																																																																																																								ASSUMPTIONS
				Clean Co										Hist													Proj
				2014					2015					2016					2017					2018
	Sales			5%
Cost of Goods Sold (COGS) as % of Sales			61%
		Depreciation			2			7%
Selling General & Administrative % of Sales			10%
Tax Rate						30.0
Dividend Growth				3.0
Accounts Receivable % of Sales			8%
	Inventories			36%
	Accounts Payable
	Capital Expenditures			15%
Existing Debt – assume unchanged

Calcs

Clean Co Hist Hist Proj Proj Proj

2014 2015 2016 2017 2018

)

CALCS
Beginning Net PPE		110.0
Plus Capex	1		50.0
Minus Depreciation		(30.0)
Ending Net PPE		230.0
Beginning	Retained Earnings		1	90.0
Plus		Net Income		1	8	5.0
Minus	Dividends	(	28.0
Ending Retained Earnings		347.0

IncStat

Clean Co Hist Hist Proj Proj Proj
2014 2015 2016 2017 2018
Income Statement

Sales

)

Depreciation

)

(30.0)

Net Income

28.0

	Income Statement
1,000.0	1,050.0
COGS ex-Dep	(		600.0	(650.0)
(		25.0
Gross Margin	375.0		370.0
SG&A	(100.0)	(105.0)
Pretax Profit	275.0	265.0
Taxes	(85.0)	(80.0)
	190.0	185.0
Total Dividends	27.0

BalSheet

YES YES YES YES

Clean Co Hist Hist Proj Proj Proj
2014 2015 2016 2017 2018

30.0 50.0

90.0 85.0

Inventories

370.0

)

110.0 230.0

600.0

Accounts Payable

45.0 48.0

190.0 180.0

235.0

Equity

25.0 25.0

Retained Earnings 190.0 347.0

450.0 600.0

BALANCE SHEET
Balance?					YES
Assets
PROJECTED EXCESS CASH
Cash
A/R
2		20.0		235.0
Total Current Assets	340.0
Gross PPE	290.0	440.0
Accum Depreciation	(	180.0	(210.0)
Net PPE
Total Assets		450.0
Liabs &	Equity
Current Liabs
PROJECTED BORROWING
	45.0		48.0
Total Current Liabs
Long-Term Liabs
Long-Term Debt
Total Liabs	228.0
Common Stock
Total Equity	215.0	372.0
Total Liabs & Equity

Cashflow

Clean Co Hist Hist Proj Proj Proj
2014 2015 2016 2017 2018

Net Income 185.0
Depreciation 30.0

5.0

3.0

Capital Expenditures

(150.0)

Dividends

20.0

30.0

20.0

50.0

CASHFLOW
Cashflow From Operations
Change in A/R
Change in Inv	(15.0)
Change in A/P
Total Cash from Operations	208.0
Cashflow from Investing
	(150.0)
Total Cash from Investing
Cashflow from Financing
Increase (Decrease) in LTD	(10.0)
(28.0)
Total Cash from Financing	(38.0)
Net Cashflow
Beginning Cash
Additions to Cash
Ending Cash