Please do all your work using Excel. Before you print your results from Excel, c
ID: 2783222 • Letter: P
Question
Please do all your work using Excel. Before you print your results from Excel, click “view” and make sure that the page break (“page break view”) is set correctly (simply drag your mouse) so that the entire width of the document is printed. Be sure to write down your name. If you have multiple pages, please staple them.
Chapman Machine Shop is considering a four-year project to improve its production efficiency. Buying and installing a new machine press for $620,000 will result in annual revenue increase of $421000. However, the cost of operation (COGS, SG&A, etc.) will increase by $210000 per year. Last month, Chapman also spent $15000 to analyze the best location to place this new machine press. The press falls in the MACRS 5-year class, and it will have a salvage value at the end of the project of $98,000. The MACRS rates are .2, .32, .192, .1152, .1152, and .0576 for Years 1 to 6, respectively.
Also, the press requires an initial investment in spare parts inventory of $20,000, along with an additional $3,600 in inventory for each succeeding year of the project. In addition, account receivables and account payables are projected to increase initially by $6000 and $2000 respectively, with no further increases in subsequent years. All other working capital accounts will stay the same as before. The inventory, account receivables and account payables will return to its original level when the project ends. The shop's tax rate is 35 percent and its project discount rate is 11 percent.
Should the firm buy and install the machine press? Display the relevant cash flows and compute NPV, IRR, and PI in Excel to inform your decision.
Explanation / Answer
Calculation of the IRR, NPV, PI are given below -
as NPV shows negative cashflow IRR is less than 11% and PI is less than 1 all collectively indicate that chapman machine does not consider that project.
tax will not be deducted on salvage value as machine is sold on loss and at the end of 4th year when its book value was 107136.
Please note all values are in $.
In case of any clarification required please comment.
Year 0 1 2 3 4 Initial Investment -620000 Incremental revenue 421000 421000 421000 421000 Cost of operation & SGA 210000 210000 210000 210000 Prior expense -15000 purchase Initial spare part inventory -20000 -23600 -27200 -30800 Accounts receivable -6000 Accounts payable 2000 less Depriciation 124000 198400 119040 71424 EBIT 63400 -14600 61160 139576 less Tax @35% 22190 0 21406 48851.6 EAT 41210 -14600 39754 90724.4 add Depriciation 124000 198400 119040 71424 Salvage value 98000 Accounts receivable refund 6000 Accounts payable refund -2000 Cash Flow -659000 165210 183800 158794 264148.4 Discounting @11% 1 0.900901 0.811622 0.731191 0.658731 Present value -659000 148837.8 149176.2 116108.8 174002.7 NPV (sum of present value) -70874.4 IRR (Using Excel function) 6.19% PI = P.V Cash inflow / P.Vcash outflow 0.89