Question

Excel Online Structured Activity: New project analysis You must evaluate the purchase of a proposed spectrometer for the R&D department. The base price is $170,000, and it would cost another $34,000 to modify the equipment for special use by the firm. The equipment falls into the MACRS 3-year class and would be sold after 3 years for $42,500. The applicable depreciation rates are 33%, 45%, 15%, and 7%. The equipment would require an $13,000 increase in net operating working capital (spare parts inventory). The project would have no effect on revenues, but it should save the firm $76,000 per year in before-tax labor costs. The firm's marginal federal-plus-state tax rate is 40%. The data has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions below. Open spreadsheet a. What is the initial investment outlay for the spectrometer, that is, what is the Year O project cash flow? Round your answer to the nearest cent. Negative amount should be indicated by a minus sign. b. What are the project's annual cash flows in Years 1, 2, and 3? Round your answers to the nearest cent. In Year 1 $ In Year 2 $ In Year 3 $ c. If the WACC is 11%, should the spectrometer be purchased? CA

Answer 1

SEE THE IMAGE. ANY DOUBTS, FEEL FREE TO ASK. THUMBS UP PLEASE

206 208 projects (Autosaved) (Autosaved) (Autosaved) - Microsoft Excel (Product Activation Failed) File Home Insert Page Layout Formulas Data Review View Add-Ins - 2x % Cut Σ AutoSum : A Calibri 11 A A = = = V Wrap Text General Ea Copy Fill Paste BI U D- DA E E E s FMerce Merge & Center - Center $ % % , 4.0 .00 :.: Conditional Format Format Painter Cell Insert Delete Format Sort & Find & Formatting as Table Styles 2 Clear Filter Select Clipboard Font Alignment Number Styles Cells Editing RX223 - RR RS RT RU RV RW RX RY RZ 204 205 SPECTROMETER YEAR 207 COST OF ASSET (170000 + 34000] -204000 WORKING CAPITAL - 13000 209 SAVINGS IN COSTS 76000.00 76000.00 76000.00 210 211 DEPRECIATION 67320.00 91800.00 30600.00 212 INCOME BEFORE TAX 8680.00 -15800.00 45400.00 TAX 40% 3472.00 -6320.00 18160.00 214 NET INCOME 5208.00 -9480.00 27240.00 215 DEPRECIATION 67320.00 91800.00 30600.00 216 OPERATING CASH FLOW -217000 72528.00 82320.00 57840.00 217 RECOVERY OF WORKING CAPITAL 13000.00 218 AFTER TAX SALVAGE VALUE OF ASSET 31212.00 (SEE NOTE) (A-B) 219 NET CASH FLOW -217000 72528.00 82320.00 102052.00 PV FACTOR AT 11% 1 0.9009 0.8116 0.7312 221 PV OF CFAT -217000 65340.54 66812.76 74619.54 -10227.16 NPV -10227.16 NPV 223 224 E AC MACRS NPV RELEVANT BETA LEVERED REPLACEMENT BOND OPTION TO WAIT STEIN indiffEPS EBIT LEASE STRAIGHT LINE GOLF IRR MACRS BASE CASE SENSITIVITY I I Rea ferences: ED12 BOU 120% 1 + D R 9 * * OENG 25-11-2019 213 220 222 R

- 2x Σ AutoSum : A Fill 2 Clear Sort & Filter Editing Find & Select SG projects (Autosaved) (Autosaved) (Autosaved) - Microsoft Excel (Product Activation Failed) File Home Insert Page Layout Formulas Data Review View Add-Ins * Cut Calibri 11 A A = = = V Wrap Text General E Copy Paste B I U Format Painter B- A Merge & Center - $ -% Conditional Format . Cell Insert Delete Format Formatting as Table Styles Clipboard Font Alignment Number Styles Cells SA223 2 SA SB SC SD SE 204 205 NOTE 206 COST OF ASSET 204000 207 DEPRECIATION 208 YEAR RATE DEPRECIATION BOOK VALUE 209 33.00% 67320 136680 210 45.00% 91800 44880 211 15.00% 30600 14280 212 7.00% 14280 213 214 NOTE 215 SALVAGE VALUE 42500 A 216 BOOK VALUE 14280 217 PROFIT ON SALE 28220 218 TAX ON PROFIT 11288.00 B 219 AFTER TAX SALVAGE VALUE 31212.00 A-B 220 221 222 Nm + 2236 224 E BETA LEVERED REPLACEMENT BOND OPTION TO WAIT STEIN indiffEPS EBIT LEASE STRAIGHT LINE GOLF IRR MACRS AC MACRS NPV RELEVANT ferences: ED12 BASE CASE SENSITIVITY I« BOU 120% 1 Rea + DOER 9 * ENG 25-11-2019)

projects (Autosaved) (Autosaved) (Autosaved) - Microsoft Excel (Product Activation Failed) Data Review View Add-Ins File Home Insert Page Layout Formulas - 2x % Cut Σ AutoSum : A Calibri A = = = V General 11 A D- DA Fill Wrap Text Merge Merge & Center - Center Z E Copy Paste Format Painter Clipboard BI U SF $ 4:0 .00 :.: Insert Delete Format % % , Number Conditional Format Formatting as Table Styles Cell Styles 2 Clear Sort & Find & Filter Select Editing Font Alignment Cells RW240 RR RS RT R U RV RW RXRY RZ 224 225 YEAR O CASH FLOW YEAR 1 CASH FLOW YEAR 2 CASH FLOW YEAR 3 CASH FLOW -217000 72528.00 82320.00 102052.00 [INCLUDING AFTER TAX SALVAGE AND WORKING CAPITAL RECOVERY] NPV 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 -10227.16 NO, AS NPV IS NEGATIVE, MACHINE SHOULD NOT BE PURCHASED ОЛИ I N EAC MACRS NPV RELEVANT ferences: ED12 BETA LEVERED REPLACEMENT BOND OPTION TO WAIT STEIN indiffEPS EBIT LEASE STRAIGHT LINE GOLF IRR MACRS BASE CASE SENSITIVITY I BOJ 120% - I O Rea D F B * * OENG 25-11-2019