Chapter 10 Spreadsheet Problem Solutions (C10)
1. There are a number of instructions with which you should be familiar
2. The model is set up to deal with a situation where the entire
investment outlay occurs at t=0 and the inflows occur over the
subsequent five to 10 years. Modification of the model would be
required to deal with a shorter or longer time frame.
INPUT DATA: KEY OUTPUT:
Base price ($260,000) NPV
Modifications ($15,000) -6,216
Increase in NWC ($22,500)
Increase in sales revenue 220,000
MODEL-GENERATED DATA:
Initial investment at t=0:
Base price ($260,000)
Modification ($15,000)
Increase in NWC ($22,500)
Initial investment outlay ($297,500)
Depreciation schedule: Terminal cash flow:
Depr. basis = $275,000 Salvage value 8,500
Ending Tax on sale of asset (3,400)
Year MACRS Depreciation Book Reverse of NWC 22,500
Rate Allowance Value Terminal CF 27,600
10.20 55,000 220,000
20.32 88,000 132,000
Annual cash flows:
0 1 2 3 4 5 6 7 8 9 10
Initial invest. (297,500)
Sales increase 220,000 220,000 220,000 220,000 220,000 220,000 220,000 220,000 0 0
Operating costs (150,000) (150,000) (150,000) (150,000) (150,000) (150,000) (150,000) (150,000) 0 0
Net cash flow (297,500) 64,000 77,200 62,900 55,200 54,100 48,600 42,000 69,600 0 0
NPV (6,216)
Expansion Project
We have already entered the base case data for each model in this
file, and the models have performed the analysis for preceding parts
of the problem. You will need to enter the data for each of the
remaining parts of the problem–we indicate in each problem the parts
that should be done using the spreadsheet. However, there are several
points worth noting before you go into a model:
1. The input data are entered in specified cells in the INPUT DATA
section. When you change an input item, the model automatically
2. The key output data are displayed to the right of the INPUT DATA
3. Input data items that you can change are distinguished from the
ones you should not change. The items that you can change generally
appear in color (blue) whereas the other items are printed in black.
4. All percentages must be entered as decimals. Dollars and other
numbers must be entered without dollar signs or commas.
5. Instructions and comments concerning specific models accompany
GENERAL INSTRUCTIONS FOR COMPUTERIZED PROBLEM SOLUTIONS