USING EXCEL FORMULAS, FUNCTIONS, AND PROCESSES TO SOLVE THE PROBLEMS, NOT HAND WRITTEN MATH

Managerial Accounting

USF Bookstore wants to buy a new coding machine to help control book inventories. The machine sells

for $$30,000$ and requires working capital of $$4,000.$ Its estimated useful life is $4$ years and it has an

estimated salvage value of $$5,000.$ The working capital will also be recovered at the end of the

machine$$s useful life. The Company expects to save $$11,000$ in operating costs per year by purchasing

this machine. The Company uses straight$-$line depreciation. Note: all computations are pretax.

Required:

$1.$ Compute the net present value of the machine at a $14\%$ rate of return.

$2.$ Compute the internal rate of return of the machine.

$3.$ Compute the payback period of the investment.

$4.$ Run two scenarios that vary inputs that you think the Company should consider. You should only

change one item per scenario, and they need to be different items. Compute the NPV and IRR

for these scenarios. Note: You cannot change the rate of return.

$5.$ Should the company buy this machine? Why or why not? What other business considerations

would you want to point out to the company?

Set up your Excel spreadsheet with $4$ tabs

$$ Input and conclusion $($Inputs and $5$ above$)$

$$ Net present value $(1)$ above and internal rate of return $(2)$ above

Payback $(3)$ above

$$ Scenarios $(4$ above$)$

Notes:

$1.$ Link your inputs to your computations. There should be no numbers input in sheets $2$ through

$4$; these are OUTPUT sheets. Numbers on the output sheets must be either links to the input

sheets, computations, or functions.

$2.$ Make your spreadsheets easy to follow. For example, if you are computing the payback period,

show the numbers going into both the numerator and denominator. SHOW YOUR WORK. Your

reviewer does not want to look at your formulas.

$3.$ You must use the Excel functions for net present value $($NPV$)$ and internal rate of return $($IRR$).$

Don$$t use the book present value tables $($you can use the tables to check your work, but you

must use the excel functions for NPV and IRR$).$

$4.$ For the scenarios, consider what is reasonable. For example, it would not be reasonable for

sales to double.

$5.$ You may work with one partner; one person should submit the case noting both partners on the

submission notes.

Grading:

Linking, easy to follow worksheets $5$

NPV $3$

Internal rate of return $3$

Payback $3$

Scenarios $3$

Well written conclusion $3$