This project will require clear inputs, proper referencing
and more details than given in the example above
Will also need to make a NPV Profile chart/graph
File Home Thisen Page Layout Formulas Data Preview View Developer 11 A A & Cut En Copy Format Painter Clipboard Calibri BIU Wrap Text Merge & Center Paste Font Alignment A10 X B D E F Year 1 1 Key 2 Inputs 3 Equipment Cost 4 NWC 5 Before Tax Salvage 6 Revenue 7 Cost 8 Tax Rate 9 MACRS Year Schedule 10 2 MACRS Depreciation 3-year 5-year 33.33% 20.00% 44.45% 32.00% 14.81% 19.20% 7.41% 11.52% 0.00% 11.52% 0.00% 5.76% 3 4 5 Background: Functions and If Statements Excel has hundreds of functions which make life easier for us as the user as we saw with some of the TVM functions in earlier projects. The IF function has the following syntax: Eif(condition, entry if true, entry if false). Condition is a mathematical expression that returns true or false. When the condition is true, the second entry is placed in the cell, otherwise the third entry is placed in the cell. Careful specification of conditions such as this is one way of helping to make sure that when in the future you use the model again, it will work correctly. What to Do: The CEO of your company asked you to evaluate the proposed implementation of a new network system. After an extensive analysis (for which the company paid a consultant $7,000) the company decided on the MT Blue system. The MT Blue has a price of $2,900,000 and will be usable for 5 years. The fixed assets will be depreciated with the MACRS approach (see depreciation schedule at bottom of next page). Using if Statements, create your model so that the user may enter in one input cell the number 3 or 5. Highlight this cell light yellow. If the user enters the number 3, your model should update using the 3-year MACRS deprecation. If the user enters in the number 5, the model should update using the 5-year MACRS deprecation values. The fixed assets will have a before tax salvage value of $510,000 at the end of year 5. The purchase of MT Blue will require an increase in NWC of $60,000 at time 0 (which will be recaptured at the end of the project). Last year the company paid $10,000 for the training of their staff for the possibility of using the MT Blue system. Because of MT Blue, the company will be able to generate additional before tax revenue of $2,080,000 per year, but the MT Blue will also increase annual operating expenses by $775,000 per year. The firm's tax rate is 40%. (Assume the company has no interest expenses.) 1)Compute the NPV of the project's relevant cash flows for costs of capital (discount rates) between 0% and 100%. Use at least 20 different discount rates. Please be sure that you choose estimates for cost of capital that cover both positive and negative NPV values. This will help with the appearance of your chart in step 2. Use the NPV() function to obtain the NPV of the project at the different cost of capital levels. Based on the cost of capital and NPV, have column that states clearly to the user whether they should "Accept" or "Reject." Use an If statement to do this, so that if any input is updated, these cells also automatically update. Use cell references and absolute references in your table to be efficient. Note: I would suggest making three columns for this part of the project. One for the various costs of capital, one for the NPV values, and one for if at that cost of capital and NPV you would accept or reject the project. MACRS Depreciation: Property Class Year Three-Year Five-Year 1 33.33% 20.00% 2 44.45 32.00 3 14.81 19.20 4 7.41 11.52 5 11.52 6 5.76 Seven-Year 14.29% 24.49 17.49 12.49 8.93 8.92 8.93 4.46 7 8 File Home Thisen Page Layout Formulas Data Preview View Developer 11 A A & Cut En Copy Format Painter Clipboard Calibri BIU Wrap Text Merge & Center Paste Font Alignment A10 X B D E F Year 1 1 Key 2 Inputs 3 Equipment Cost 4 NWC 5 Before Tax Salvage 6 Revenue 7 Cost 8 Tax Rate 9 MACRS Year Schedule 10 2 MACRS Depreciation 3-year 5-year 33.33% 20.00% 44.45% 32.00% 14.81% 19.20% 7.41% 11.52% 0.00% 11.52% 0.00% 5.76% 3 4 5 Background: Functions and If Statements Excel has hundreds of functions which make life easier for us as the user as we saw with some of the TVM functions in earlier projects. The IF function has the following syntax: Eif(condition, entry if true, entry if false). Condition is a mathematical expression that returns true or false. When the condition is true, the second entry is placed in the cell, otherwise the third entry is placed in the cell. Careful specification of conditions such as this is one way of helping to make sure that when in the future you use the model again, it will work correctly. What to Do: The CEO of your company asked you to evaluate the proposed implementation of a new network system. After an extensive analysis (for which the company paid a consultant $7,000) the company decided on the MT Blue system. The MT Blue has a price of $2,900,000 and will be usable for 5 years. The fixed assets will be depreciated with the MACRS approach (see depreciation schedule at bottom of next page). Using if Statements, create your model so that the user may enter in one input cell the number 3 or 5. Highlight this cell light yellow. If the user enters the number 3, your model should update using the 3-year MACRS deprecation. If the user enters in the number 5, the model should update using the 5-year MACRS deprecation values. The fixed assets will have a before tax salvage value of $510,000 at the end of year 5. The purchase of MT Blue will require an increase in NWC of $60,000 at time 0 (which will be recaptured at the end of the project). Last year the company paid $10,000 for the training of their staff for the possibility of using the MT Blue system. Because of MT Blue, the company will be able to generate additional before tax revenue of $2,080,000 per year, but the MT Blue will also increase annual operating expenses by $775,000 per year. The firm's tax rate is 40%. (Assume the company has no interest expenses.) 1)Compute the NPV of the project's relevant cash flows for costs of capital (discount rates) between 0% and 100%. Use at least 20 different discount rates. Please be sure that you choose estimates for cost of capital that cover both positive and negative NPV values. This will help with the appearance of your chart in step 2. Use the NPV() function to obtain the NPV of the project at the different cost of capital levels. Based on the cost of capital and NPV, have column that states clearly to the user whether they should "Accept" or "Reject." Use an If statement to do this, so that if any input is updated, these cells also automatically update. Use cell references and absolute references in your table to be efficient. Note: I would suggest making three columns for this part of the project. One for the various costs of capital, one for the NPV values, and one for if at that cost of capital and NPV you would accept or reject the project. MACRS Depreciation: Property Class Year Three-Year Five-Year 1 33.33% 20.00% 2 44.45 32.00 3 14.81 19.20 4 7.41 11.52 5 11.52 6 5.76 Seven-Year 14.29% 24.49 17.49 12.49 8.93 8.92 8.93 4.46 7 8
