Assignment 3.1
Assignment 3.1 is a difficult assignment that will really test your understanding of a lot of concepts not only in Week 3 of the course but also in Week 1 (advanced data management). The best way to learn is repetition of concepts, so this assignment will force you to recall, remember, and reuse some of the concepts jiggling around in your head from earlier in the course!
For Assignment 3.1 you will create a worksheet that allows the user to input the Purchase Cost (in $), Year purchased, Useful Life (in years), and Salvage Value (in $), all in separate cells of the worksheet. Then, the user will select the Depreciation method from a drop-down list (data validation) in cell C8. The depreciation methods available in the list are the 3 methods in cells B12:B14 (Depreciation methods), and it is important that the methods that show up in cell C8 are spelled exactly as they are in cells B12:B14 (the grader file will not work if you have misspelled these methods!).
The user can then select a Year from the right side of the worksheet (cell F3) and the Yearly depreciation and End-of-year value for that asset for the corresponding Year will be displayed in cells F4 and F5, respectively. IMPORTANTLY, the options that show up for Year in cell F3 will start with 1 year after the Year purchased in cell C4 and will go up to the number of years of Useful Life in cell C5. For example, in the example shown below, the options available in the drop-down list in cell F3 would be 2016, 2017, 2018, …, 2024, 2025 (only those 10 years, no more, no less). You can assume that the Useful Life is limited to 20 years (i.e., the grader file will not put in anything greater than 20 in cell C5).
It is also important that you don’t move the location of any of the boxed in cells on the starter file!
HINTS:
I had to create several (8) “helper” columns over to the right side of the worksheet. Afterwards, it is easy to hide these columns, if you wish.I would recommend using the OFFSET function in combination with the Useful Life value in your data validation List (you will want to use a formula) for cell F3. You will also somehow (several ways to do this) account for the Year purchased (cell C4). If you have Office 365, you might look into using the SEQUENCE function.My formulas in cells F4 and F5 involved the following functions: MATCH, OFFSET, INDEX. These formulas referenced my “helper” columns.
For a demonstration of Assignment 3.1 “in action”, please watch the “Assignment 3 preview and instructions” screencast.
Assignment 3.2
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 costThe expected salvage valueOverhaul 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”.