I have filled out most of the excel but I am stuck on the part with the arrows only. The other blanks I do not need help on I only need help on the arrowed sections to solve that. Thank you!!

TRANSREG CORP. - CAPITAL PROJECT ANALYSIS ACCEPT OR REJECT THE PROJECT AND WHY? (Your answer here) It is best to reject the project because NPV is negative and profitablitly index is below 1, indicating a deficit of outflows greater than discounted inflows Transreg Corp. is a manufacturer of automotive brake pads for original equipment manufacturers (OEMs). General Motors has approached them to manufacture brake pads for a new model. GM is currently not a customer and their brake pad is unique and requires a specialized machine. The contract will be for 7 years and will require the purchase of the specialized machine that will be needed to manufacture the brake pads as well as hire additional employees. In addition an initial working capital investment of $250,000 will be needed as well as an annual working capital investments of $250,000 at the beginning of the current year (i.e. end of the prior year). In the final year the $250,000 will be returned as inventory and accounts receivable liquidate. You are a financial analyst in the Financial Planning & Analysis Department. The production department supplied you with the following: --Cost of the machine: $22,000,000 - Working capital requirements as outlined above --Transreg Corp. will sell the number of brake pads per year as scheduled below. --Sales price per brake pad is $225.00 --Manufacturing cost per brake pad is $110.00 -- Operating expenses will be 10.0% of sales -- The equipment will be depreciated based on the following MCARS tax rates which are: Year 1- 20.0%, Year 2-32.0%, Year 3 - 19.0%, Year 4 - 12.0%, Year 5 - 12.0% and Year 6-5.0% -- The equipment will have a 5% residual value -- The average tax rate is 23% and the marginal rate is 25% -- Management requires a rate of return on this project of 14% % BEOUIRED You are to prepare IRR, NPV, payback and profitability index analyses and recommend whether to ACCEPT or REJECT the project and WHY. $ Cost of machine Residual value as a % of cost Working Capital Investment Sales price per Brake Pad Manufacturing cost per pad Operating Expenses as a % of Sales Tax Rate Required Rate of Return $ $ $ 22,000,000 5.0% 250,000 225.00 110.00 10.0% 25% 14% Year g 1 2 3 4 5 6 Z $ $ # of Brake Pads Sold Sale Price per Brake Pad Sales Total Manufacturing Cost COGS as a % of Revenue Operating Expenses Operating Expenses as a % of Sales Depreciation Expense Tax Depreciation MACRS % Add: Residual value Pre-Tax Income from Project Less: Taxes After-Tax Income Add: Depreciation Less: Working Capital Investment Cash Flow Cumulative Cash Flow 50,000 225.00 $ 11,250,000 $ 5,500,000 48.9% 1,125,000 10.0% 4,400,000 20.00% 60,000 75,000 90,000 225.00 $ 225.00 $ 225.00 $ 13,500,000 $ 16,875,000 $ 20,250,000 $ $ 6,600,000 8,250,000 9,900,000 48.9% 48.9% 48.9% 1,350,000 1,687,500 2,025,000 10.0% 10.0% 10.0% 7,040,000 4,180,000 2,640,000 32.00% 19.00% 12.00% 70,000 55,000 225.00 $ 225.00 $ 15,750,000 $ 12,375,000 $ 7,700,000 6,050,000 48.9% 48.9% 1,575,000 1,237,500 10.0% 10.0% 2,640,000 1,100,000 12.00% 5.00% 30,000 225.00 6,750,000 3,300,000 48.9% 675,000 10.0% 225,000 56,250 168,750 4.400,000 (1,490,000) (372,500) (1,117,500) 7,040,000 2,757,500 689,375 2,068,125 4,180,000 5,685,000 1,421,250 4,263,750 2,640,000 3,835,000 958,750 2,876,250 2,640,000 3,987,500 996,875 2,990,625 1,100,000 1,100,000 3,875,000 968,750 2,906,250 $ 250,000.0 (22,250,000) $ $ 4,568,750 $ 4,568,750 $ 5,922,500 $ 6,248,125 $ 10,491,250 $ 16,739,375 $ Cost 5,516,250 $ 4,090,625 $ 29,159,375 $ 33,250,000 $ 2,906,250 36,156,250 6,903,750 $ 23,643,125 $ (22,250,000) 1,393,125 Cash Flow Not Required 22,759,775 (22,250,000) losing money Present Value Cash Out for this Investment Net Present Value Internal Rate of Return Payback Period Profitability Index % Not Required % Not Required 6.3% 93.7% Recommendation: Reject Ideas to make this a profitable investment ? HW + Ready TRANSREG CORP. - CAPITAL PROJECT ANALYSIS ACCEPT OR REJECT THE PROJECT AND WHY? (Your answer here) It is best to reject the project because NPV is negative and profitablitly index is below 1, indicating a deficit of outflows greater than discounted inflows Transreg Corp. is a manufacturer of automotive brake pads for original equipment manufacturers (OEMs). General Motors has approached them to manufacture brake pads for a new model. GM is currently not a customer and their brake pad is unique and requires a specialized machine. The contract will be for 7 years and will require the purchase of the specialized machine that will be needed to manufacture the brake pads as well as hire additional employees. In addition an initial working capital investment of $250,000 will be needed as well as an annual working capital investments of $250,000 at the beginning of the current year (i.e. end of the prior year). In the final year the $250,000 will be returned as inventory and accounts receivable liquidate. You are a financial analyst in the Financial Planning & Analysis Department. The production department supplied you with the following: --Cost of the machine: $22,000,000 - Working capital requirements as outlined above --Transreg Corp. will sell the number of brake pads per year as scheduled below. --Sales price per brake pad is $225.00 --Manufacturing cost per brake pad is $110.00 -- Operating expenses will be 10.0% of sales -- The equipment will be depreciated based on the following MCARS tax rates which are: Year 1- 20.0%, Year 2-32.0%, Year 3 - 19.0%, Year 4 - 12.0%, Year 5 - 12.0% and Year 6-5.0% -- The equipment will have a 5% residual value -- The average tax rate is 23% and the marginal rate is 25% -- Management requires a rate of return on this project of 14% % BEOUIRED You are to prepare IRR, NPV, payback and profitability index analyses and recommend whether to ACCEPT or REJECT the project and WHY. $ Cost of machine Residual value as a % of cost Working Capital Investment Sales price per Brake Pad Manufacturing cost per pad Operating Expenses as a % of Sales Tax Rate Required Rate of Return $ $ $ 22,000,000 5.0% 250,000 225.00 110.00 10.0% 25% 14% Year g 1 2 3 4 5 6 Z $ $ # of Brake Pads Sold Sale Price per Brake Pad Sales Total Manufacturing Cost COGS as a % of Revenue Operating Expenses Operating Expenses as a % of Sales Depreciation Expense Tax Depreciation MACRS % Add: Residual value Pre-Tax Income from Project Less: Taxes After-Tax Income Add: Depreciation Less: Working Capital Investment Cash Flow Cumulative Cash Flow 50,000 225.00 $ 11,250,000 $ 5,500,000 48.9% 1,125,000 10.0% 4,400,000 20.00% 60,000 75,000 90,000 225.00 $ 225.00 $ 225.00 $ 13,500,000 $ 16,875,000 $ 20,250,000 $ $ 6,600,000 8,250,000 9,900,000 48.9% 48.9% 48.9% 1,350,000 1,687,500 2,025,000 10.0% 10.0% 10.0% 7,040,000 4,180,000 2,640,000 32.00% 19.00% 12.00% 70,000 55,000 225.00 $ 225.00 $ 15,750,000 $ 12,375,000 $ 7,700,000 6,050,000 48.9% 48.9% 1,575,000 1,237,500 10.0% 10.0% 2,640,000 1,100,000 12.00% 5.00% 30,000 225.00 6,750,000 3,300,000 48.9% 675,000 10.0% 225,000 56,250 168,750 4.400,000 (1,490,000) (372,500) (1,117,500) 7,040,000 2,757,500 689,375 2,068,125 4,180,000 5,685,000 1,421,250 4,263,750 2,640,000 3,835,000 958,750 2,876,250 2,640,000 3,987,500 996,875 2,990,625 1,100,000 1,100,000 3,875,000 968,750 2,906,250 $ 250,000.0 (22,250,000) $ $ 4,568,750 $ 4,568,750 $ 5,922,500 $ 6,248,125 $ 10,491,250 $ 16,739,375 $ Cost 5,516,250 $ 4,090,625 $ 29,159,375 $ 33,250,000 $ 2,906,250 36,156,250 6,903,750 $ 23,643,125 $ (22,250,000) 1,393,125 Cash Flow Not Required 22,759,775 (22,250,000) losing money Present Value Cash Out for this Investment Net Present Value Internal Rate of Return Payback Period Profitability Index % Not Required % Not Required 6.3% 93.7% Recommendation: Reject Ideas to make this a profitable investment ? HW + Ready