| Requirements
1) Create the Net Present Value model in MS Excel
as shown on
Page 12 of the text "Project Management in Practice".
2) Based on the following parameters,
calculate the Net Present Value, the Internal Rate of Return, and the
approximate year in which Payback is achieved (i.e. between years x and
y)
3) The parameters are as follows:
- Increased Gross Revenues of 5,000 units per year selling at $60 with a
Net Profit Margin of 10%. Revenues growing at an annual rate of 7% per year
from years 2 to 10.
- Decreased Cost of Sales on each of the new sales-in
a) above-of 8% of Gross Revenue. Growth rate is same as growth in revenues.
- Additional Decreased Cost of Sales on 60,000 units of 8% of Gross Revenues. Growth rate of 4% per year for
years 2-10.
- Software development labor is 3,900 hours at $100 per hour.
- Annual maintenance labor is 20% of original cost for years 2 thru 10.
- Hardware Cost is 20 servers/network equip at
$25,000 each, depreciated over 5 years - straight line.
- Purchase software is 5 licenses @ $75,000 each, depreciated over 3 years - straight line.
- Ongoing purchased software maintenance costs are 15% of the original
purchase from years 2 thru 10.
- The company's discount rate is 20%.
Hint: It is easier to construct the total
inflows and outflows of the model if you create a separate column for
each of the above costs, and then total them as necessary (i.e. Total
Inflow = a + b + c). Also, the Internal Rate of Return is not
shown in the text example but is standard financial function
in the Excel. Finally, to determine in which year achieves
payback, you must create a column that calculates cumulative cash flows
(inflow - outflows). The payback year is where the cumulative cash
flow crosses from negative to positive.
You must eMail me the completed spreadsheet before
midnight on Thursday, April 17th.
|
