10.10 Chapter 10 HW - Spreadsheet Mastery (problem 2 & 3) please fill the table and answer questions with the blank boxes. thank you!
B D E F G H J K 85 86 Problem 2: Your firm recently purchased an industrial machine costing $525,000. It is classified as a seven-year property under MACRS. 87 What are the annual depreciation allowances and end-of-the-year book values for this machine? 88 89 Finance Concept: For tax purposes, the depreciation expense is computed under MACRS, which was 90 enacted as part of the Tax Reform Act of 1986. The depreciation is larger at the beginning. Beginning Book Value Depreciation Allowance Ending Book Value Year 1 2 3 7 Year MACRS 14.29% 24.49% 17.49% 12.49% 8.93% 8.93% 8.93% 4.45% 4 5 6 7 8 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 Solution: 1 Enter the Beginning Book Value (cost of the machine) in cell E93. 2 Calculate the remaining Beginning Book Values: enter =193 in cell E94 and copy down the column. 3 Calculate the Depreciation Allowance by multiplying $E$93 (Beg Bk V) * F93 (MACRS %). 4 Find the Ending Book Value by subtracting the depreciation from the Beginning Book Value. 5 Copy all your formulas down the columns. 6 Year 8 Ending Book Value should equal zero, and beg bk val = depreciation in year 8.. A B C D E H K 112 Problem 3: Peggy's Peaches has developed a new product, the Bruiseless Peach, which always stays peachy fresh. Peggy's paid $85,000 to a marketing firm to survey the bruiseless peach market. The potential sales were estimated at $250,000 per year. New equipment will be necessary to carefully handle the peaches. It cost $200,000 and will have fixed costs of $70,000 per year, and variable costs will be 25% of sales. The new anti-bruise machine will be depreciated straight-line for the four years of it's life and is the only initial cost for the new "Peggy's Peaches, the UnBruised Ones". Peggy's pays 34% tax and has a required return of 8%. Calculate the NPV 113 and IRR. 114 115 Solution: 116 Enter numbers and formulas to solve this problem. Use the Excel NPV and IRR functions to solve NPV and IRR.. 117 Step 1. Find the Net Income for years 1-4. 118 Depreciation = (Initial cost - Salvage Value) years ==> Assume Salvage Value = 0. 119 120 Net Income Year 1-4 121 Sales from problem 122 Variable Costs 25% of Sales 123 Fixed Costs from problem 124 Depreciation from H18 125 EBIT EBIT = Sales-costs-dep 126 Taxes Taxes = EBIT * tax rate 127 Net Income NI = EBIT - taxes 128 129 Operating Cash Flows 130 EBIT 131 + Depreciation 132 - Taxes 133 After-Tax Cash Flow 134 CE Assume Salvage Value = 0. 119 120 Net Income Year 1-4 121 Sales from problem 122 Variable Costs 25% of Sales 123 Fixed Costs from problem 124 Depreciation from H18 125 EBIT EBIT = Sales-costs-dep 126 Taxes Taxes = EBIT * tax rate 127 Net Income NI = EBIT - taxes 128 129 Operating Cash Flows 130 EBIT 131 + Depreciation 132 - Taxes 133 After-Tax Cash Flow 134 CE
