Students will create $an$ Excel worksheet $to$ forecast the cash flows $of$ a proposed project leading

$to$ calculating its $NPV$ result, following the method introduced $in$ Chapter $6.$ The proposed

project $is$ Example $2(The$ Baldwin Company$)in$ Chapter $6-$ Cash Flow $(part1).$ The goal $isto$

have a systematic approach $to$ forecast cash flows and also provide users the flexibility $to$ modify

key inputs$*to$ quickly get the $NPV$ results under different scenarios. A good reference $is$ the

Excel Template provided $in$ the Canvas course.$*For$ grading purposes, these key inputs include:

$1.$ Units sold for each year $of$ the project

$2.$ Price per unit for Year $1$

$3.$ Price increase percentage per year $(assume$ the same for all years$)$

$4.$ Unit production cost for Year $1$

$5.$ Unit production cost increase percentage per year $(assume$ the same for all years$)$

$6.NWCto$ start project

$7.NWC$ for subsequent years $as$ a percentage $of$ Sales $(assume$ the same for all years$)$

$8.$ Cost $of$ warehouse

$9.$ Cost $of$ machine

$10.$ Depreciation rate for each year $of$ the project

$11.$ Salvage value $of$ machine $(pre-$tax$)$

$12.$ Tax rate $(assume$ the same for all years$)$

$13.$ Discount rate $(assume$ the same for all years$)$

Example $2$: The Baldwin Company

$$ Costs $of$ test marketing $(already$ spent$)$: $$250,000$

Current market value $of$ proposed factory site $(whichwe$ own$)$:

$$150,000$

The proposed factory site $is$ expected $tobe$ worth $$150,000at$ the

end $of$ the project

$C$ Cost $of$ bowling ball machine: $$100,000(depreciated$ according $to$

MACRS $5-$year$)$

Bowling ball machine's salvage value: $$30,000$

Increase $in$ net working capital $in$ year $0$: $$10,000$

Net working capital $at$ end $of$ each year $is10\%of$ sales

Production $(in$ units$)by$ year during $5-$year life $of$ the machine:

$5,000,8,000,12,000,10,000,6,000$

$b$ Price during first year $is$ $$20$; price increases $2\%$ per year

thereafter.

Production costs during first year are $$10$ per unit and increase

$10\%$ per year thereafter.

Annual inflation rate: $5\%$

Discount rate: $10\%$

Tax rate $is34\%$ Example $2$: The Baldwin Company

$$ Costs $of$ test marketing $(already$ spent$)$: $$250,000$

Current market value $of$ proposed factory site $(whichwe$ own$)$:

$$150,000$

The proposed factory site $is$ expected $tobe$ worth $$150,000at$ the

end $of$ the project

$C$ Cost $of$ bowling ball machine: $$100,000(depreciated$ according $to$

MACRS $5-$year$)$

Bowling ball machine's salvage value: $$30,000$

Increase $in$ net working capital $in$ year $0$: $$10,000$

Net working capital $at$ end $of$ each year $is10\%of$ sales

Production $(in$ units$)by$ year during $5-$year life $of$ the machine:

$5,000,8,000,12,000,10,000,6,000$

$b$ Price during first year $is$ $$20$; price increases $2\%$ per year

thereafter.

Production costs during first year are $$10$ per unit and increase

$10\%$ per year thereafter.

Annual inflation rate: $5\%$

Discount rate: $10\%$

Tax rate $is34\%$ Purpose

The purpose $of$ this project $isto$ provide students $an$ incentive $to$ practice their Excel modelling

skills learned $in$ class $on$ cash flow forecasting for the capital budgeting purpose.

Instruction

Students will create $an$ Excel worksheet $to$ forecast the cash flows $of$ a proposed project leading

$to$ calculating its $NPV$ result, following the method introduced $in$ Chapter $6.$ The proposed

project $is$ Example $2(The$ Baldwin Company$)in$ Chapter $6-$ Cash Flow $(part1).$ The goal $isto$

have a systematic approach $to$ forecast cash flows and also provide users the flexibility $to$ modify

key inputs$*to$ quickly get the $NPV$ results under different scenarios. A good reference $is$ the

Excel Template provided $in$ the Canvas course.

$*For$ grading purposes, these key inputs include:

Units sold for each year $of$ the project

Price per unit for Year $1$

Price increase percentage per year $(assume$ the same for all years$)$

Unit production cost for Year $1$

Unit production cost increase percentage per year $(assume$ the same for all years$)$

$NWCto$ start project

$NWC$ for subsequent years $as$ a percentage $of$ Sales $(assume$ the same for all years$)$

Cost $of$ warehouse

Cost $of$ machine

Depreciation rate for each year $of$ the project

Salvage value $of$ machine $(pre-$tax$)$

Tax rate $(assume$ the same for all years$)$

Discount rate $(assume$ the same for all years$)$