Computer Assignment 4: Go to Spreadsheet tab to do all work. Remember to use cell references for ALL calculations nformation in Inputs Shrieves Casting Company is considering a new project They expect to sell 1,800 units each year for three years at a prioe of $200 per unit. Variable costs are $80 per unit and fixed costs are $50,000 per year The equipment costs $200,000, plus $10.000 of shipping and $30,000 of installation. It will be depreciated according to MACRS 3 year class. When the project ends in three years, Shrieves expects to sell the equipment for $100,000. This project requires an initial investment in net working capital, specifically $40,000 of inventories with $4,000 of Accounts Payable to help finanoe these inventories In the inputs, numbers you need to calculate are shown in yellow Make sure you use cell references Then fill in all outputs, beginning with the Depreciation schedule, then the rest of the spreadsheet. You will first assume inflation is zero Make sure your cash inflow and cash outflows are notated differently. Use the functions on Excel to find NPV. IRR and MIRR Use the If function to put "Accept" or "Rejecr in 875 FINC 3630 Computer Assignment 4 Submit on Canvas Due: Monday, June 18th (submitted on Canvas by 9:30 AM) All work must be YOUR OWN. You may not share spreadsheetsl* Put your name in the header! ncellA6 Fill in the spreadsheet using calculations to find cash flows. Use cell references. Yellow indicates your work e the following: I have neither given nor received help on this assignment. All work is my own. INPUTS Machinery Shipping Installation Depreciable Cost $200,000 $10,000 $30,000 Increase in Net WC 40,000 4,000 Increase in Inventories Increase in Accounts Payable Increase in Net WC Depreciation 3 year MACRS 1,800 $200 S80 Units Sold Price per Unit Variable Cost per Unit Fixed Costs Revenue per Year Variable Costs per Year $50,000 per year in today's dollars in today's dollars Tax rate WACC Inflation Selling Price of Machine-Year 3 40% 10% 0% $100,000 Create a 3yr MACRS Depreciation Schedule Year Depreciation Rate Dollar Depreciation End of Year Book Value tion schedule. Fill in years and iation rates, then use cell references. Makre sure cells are formatted appropriately Cash Flow Schedule Fill in cash flow pieces in the correct years, using cell references. Add up to find Free Cash Flow. Then find NPV, IRR and MIRR Cash Flows per year NOPAT Variable Costs Fixed Costs Depreciation EBIT Taxes NOPAT + Depreciation Operating Cash Flows Total Cost of Machine Selling Price Taxes Change in Net Working Capital Free Cash Flows Net Present Value IRR Net Present Value IRR MIRR Accept/Reject Decision After filling in spreadsheet and calculating NPV, IRR and MIRR, check answers. Create an If function to analyze then write "Accept" or "Reject" in B75. Sensitivity Analysis 1. If unit sales are 1200, what is NPV? 2. If unit sales are 2400, what is NPV? Answer these by typing in the numbers, not cell references (do these with zero inflation.) Don't forget to reset your spreadsheet to the original inputs (1,800 units)! Submit on Canvas