Show me the steps to solve Sunrise Bakery in Excel

Sunrise Bakery

Assumptions

Expected Investment Cost $$300,000$

Risk Free Rate

Market Beta

Equity Risk Premium

Cost of Debt

Cost of Equity

Tax rate

WACC

Income statement $($all figures are incremental$)0123456$

Revenue $($Sales$)$ $$135,000$ $$145,000$ $$155,000$ $$165,000$ $$165,000$ $$165,000$

Production Costs $($$$20,000)($$$22,000)($$$24,000)($$$26,000)($$$26,000)($$$26,000)$

Depreciation $/$ Amortization $($$$30,000)($$$30,000)($$$30,000)($$$30,000)($$$30,000)($$$30,000)$

Profit Before tax $$85,000$ $$93,000$ $$101,000$ $$109,000$ $$109,000$ $$109,000$

Taxes $$0$ $$0$ $$0$ $$0$ $$0$ $$0$

Profit after tax $($Net Income$)$ $$85,000$ $$93,000$ $$101,000$ $$109,000$ $$109,000$ $$109,000$

Projected Balance Sheet $0123456$

Cash and Marketable Sec. $$0$ $$115,675$ $$238,725$ $$369,775$ $$508,825$ $$647,825$ $$786,825$

Other Current Assets $($Inventory and Receivables$)$ $$15,000$ $$17,025$ $$17,175$ $$17,325$ $$17,475$ $$17,475$ $$17,475$

Fixed Assets

At cost $$300,000$ $$300,000$ $$300,000$ $$300,000$ $$300,000$ $$300,000$ $$300,000$

Accumulated Depreciation $/$ Amortization $$0$ $$30,000$ $$60,000$ $$90,000$ $$120,000$ $$150,000$ $$180,000$

Net Fixed Assets $$300,000$ $$270,000$ $$240,000$ $$210,000$ $$180,000$ $$150,000$ $$120,000$

Total Assets $$315,000$ $$402,700$ $$495,900$ $$597,100$ $$706,300$ $$815,300$ $$924,300$

Current Liabilities $($Payables$)$ $$0$ $$2,700$ $$2,900$ $$3,100$ $$3,300$ $$3,300$ $$3,300$

LT Debt $$0$ $$0$ $$0$ $$0$ $$0$ $$0$ $$0$

Total Liabilities $$0$ $$2,700$ $$2,900$ $$3,100$ $$3,300$ $$3,300$ $$3,300$

Stock and accumulated retained earnings $$315,000$ $$400,000$ $$493,000$ $$594,000$ $$703,000$ $$812,000$ $$921,000$

Total Liabilities and Equity $$315,000$ $$402,700$ $$495,900$ $$597,100$ $$706,300$ $$815,300$ $$924,300$

Working Capital $($Non$-$Cash Current Assets less Current Liabilities$)$ $$15,000$ $$14,325$ $$14,275$ $$14,225$ $$14,175$ $$14,175$ $$14,175$

Change in working capital

Projected Free Cash Flows to the Project

Net Income

Depreciation $/$ Amortization

Change in working capital

Capital Expenditures $($fixed assets at cost$)$

After tax salvage value $($Terminal Value$)$

Free Cash Flows

NPV

Should the company undertake the project? Number of years

Payback Analysis $($when do cash flows sum positive?$)$

Discounted Payback Analysis