PART 1: Combined Model

Create a corporate cash flow/capital budgeting model for an all-equity firm. Use the page break preview view that we used for the first corporate model, where we added print area for extra pages. Your model should contain the following sections: NOTE: READ ALL THE DIRECTIONS CAREFULLY BEFORE YOU BEGIN THE MODEL.

Data section the following inputs and initial values should be included

Price per unit - $250

Annual price increase 2%

Price only increases in ODD number years. You will need an IF statement with MOD function to calculate sales in these years. (Hint: dividing an even number by two leaves no fraction.)

Number of units (ie, quantity) 10,000

Annual increase in number of units 1%

Variable cost per unit - $130

Annual cost increase 2%

Cash Fixed costs - $500,000 per year

Depreciation existing assets 100,000/year

NWC 10% of sales

Capital spending 3% of revenue

New depreciation 2% of revenue

Cost of capital 12%

Shares outstanding 100,000

Terminal Value calculation year (TV YR) initially set to 10 (max 15)

P/E multiple in TV YR: = 12

Income statement and cash flow (IS & CF) linking data from the data input section, create pro-forma income statements for a variable 15-yr window, the length of which depends on the terminal value calculation year. (TV YR)In the first 5 rows:

Counter

Price

Quantity

Variable Cost

NWC Level

Next section, 8 rows:

Revenue

Variable Cost

Fixed Cost

Depreciation existing

Depreciation new

EBIT

Tax 15% on first $500,000; 25% > $500,000 (IF statement required)

Net Income

Next Section, 6 rows:

EBIT

Tax

Change in NWC

Depreciation

Capital Spending

FCF

Next Section, 5 rows:

Terminal Value: calculated using P/E multiple and Terminal Value year plus one Net Income

PV of Free cash flows

PV of Terminal Value

Value of Equity (PV of all FCFs and TV)

Price per share

Next section, 8 rows and 8 columns

Create a Table with cost of capital across the top with 1% increments, and P/E multiple down the side with increments of 1. (ie, starting value will be in middle of each and there will be 3 higher and 3 lower)

Formatting MATTERS! Use Accounting and 0 decimals, except for prices, which should be accounting and 2, use 2 decimals for %s, and our color coding per class.

CAN YOU PLEASE SHOW THE STEPS....