Your company has been ordered by OSHA to install new safety equipment in the main R&D laboratory. You have been asked by your boss to set up an Excel worksheet that allows you to investigate the effect that the following parameters have on the net present value (NPV) of the safety system:
- The initial system cost
- The expected salvage value
- Overhaul period (i.e., every 2 years, 3 years, 4 years, 5 years, etc.) (Note that the overhaul cost *is* required in the 12th year, if it lies within the overhaul period schedule.)
- Operating and Maintenance (O&M) costs
Your boss will then change/edit the various system parameters to evaluate the system economics, etc. The system is expected to last 12 years.
IMPORTANT: For this analysis, please neglect depreciation!
The Assignment 3.2 file that is available has a “skeleton” for your analysis. It is very important that you set it up so that it updates in a “live” fashion. In other words, if the inputs in green are changed, the table should automatically update.
What you need to do is to fill out the table with Overhaul Cost, O & M, and PV columns. In addition, the net present value (cell E23) needs to be calculated correctly.
HINTS:
- Remember, the present value (PV) column just represents the present value of the sum of Overhaul Cost (if applicable for that year) and O & M columns.
- You should use a formula in the Overhaul column that applies the Overhaul cost, but only if the corresponding year in the Year column is divisible by the “every x years” cell (cell C6). The MOD function is a good way to determine if a number is divisible by another number. If the MOD is 0, that means that it is divisible (see Part 1 of the course).
- See the screencast “Assignment 3 preview and instructions” to see the assignment solution “in action”.
