As an additional cost savings measure, Music Makers is considering the purchase of a computerized office management system. The system would cost \(\$ 98,000\). It would have an expected life of 48 months and would be depreciated for tax purposes using the sum-of-the-years-digits' method. The company's tax rate is \(30 \%\). The company expects to save \(\$ 2,500\) per month from the system in addition to the tax savings. It requires at least a \(12 \%\) return on investment (annual interest rate) for the project.

Required Use a spreadsheet to prepare a net present value and internal rate of return analysis for Music Makers. Use the format provided below as an example. Enter the data or use formulas to calculate data in column B, rows 1 through 7. Use references to these data to prepare the analysis beginning in row 10 . Computations for month 0 (the date of the investment) and month 1 (the date the first cost savings are incurred) are provided as examples.

In the Month column, enter each month for the computation, from 0 to 48 . If you enter data in A10 and A11, you can select these two cells and drag them downward to automatically enter the remaining months ( 12 to 48 ). To calculate depreciation in column \(\mathrm{B}\), use the function button \(f_{x}\) and select Financial, SYD for sum-of-the-years-digits. In the pop-up box, enter references to the Cost (\$B\$3), Salvage (0), Life (\$B\$4), and Period (Per) (A11). Use absolute references for those cells that will not change in the calculation. Tax savings are the amount of depreciation each month times the tax rate. Cost savings are \(\$ 2,500\) per month. Net cash flow is the sum of tax savings and cost savings. Present value is calculated from the formula \(\mathrm{PV}=C /(1+R)^{t}\), where \(C\) is the net cash flow for the month from column \(\mathrm{E}, R\) is the monthly interest rate ( \(\$ 3 \$ 2\) ), and \(t\) is the month from column A. The net cash flow in month 0 is the amount of the investment and is negative because it is a cash outflow. Once data are entered in row 11 , you can select the cells in the row and drag them down to copy the formulas for the remaining months.
To calculate the net present value of the investment, sum the data in column F. Evaluate whether Music Makers should make the investment based on its net present value.
To calculate the internal rate of return in cell \(\mathrm{A} 7\), select the cell, then click on the function button and select Financial, IRR. In the pop-up box, enter the Values by selecting the net cash flow cells (E10:E58). You can leave the Guess box empty or enter an estimate of the IRR (0.12, for example). The function uses this guess as a starting point to estimate the internal rate of return. Evaluate whether Music Makers should make the investment based on its internal rate of return.
Suppose Music Makers' annual required return is \(25 \%\). Would the investment still be justified? Recompute the net present value.

Transcribed Image Text:

A B E F 1 Annual interest rate 2 Monthly interest rate 3 Investment Life (months) 45 5 Tax rate 6 Cost savings 7 868 10 11 Depreciation Tax Savings Cost Savings Net Cash Flow 1,200.00 2,500.00 (98,000.00) 3,700.00 Present Value (98,000.00) 3,663.37 Internal rate of return Month 0 1 4,000.00