It is in the assignment.
Microsoft Word - BA 2505 PROJECT PART 2 SPRING 2020 FINANCE 3 Spring 2020 PROJECT PART 1 The president and owner of Old School Corporation (OSC), has just returned from a seminar on automation. At the seminar, the speakers indicated that robotic manufacturing was the wave of the future and that companies could gain a competitive advantage by adopting the technology. OSC had looked at robotic manufacturing a few years ago but based on the company’s sales volume and the cost of the robots the company decided to stick with its old manufacturing methods. However, the cost of the robots has come down over the last few years and the company is expecting its sales volume to increase over the next five years. The company president has therefore asked that a new analysis be prepared looking at the financial implications of automating production through robotic manufacturing vs. continuing with the existing manual system. A committee, consisting of the controller, the marketing manager, the information systems manger, and the production manager was given the responsibility to prepare the analysis. As a starting point, the controller provided the following information on expected revenues and expenses for the existing manual system. Description Total Dollars Percent of Sales Sales $600,000 100% Less: Expenses Direct Materials 180,000 30 Direct Labor 120,000 20 Variable Manufacturing Overhead 60,000 10 Fixed Overhead: Miscellaneous manufacturing cost 70,000 Depreciation 20,000 Variable Selling 30,000 5 Total Expenses 480,000 Operating Income 120,000 After investigating the robotic equipment available, the committee settled on an R2D2C3PO system. The production manager was excited about the robotic system because it would eliminate the need to hire welders, which was attractive because the market for welders seemed perpetually tight. Moreover, the robot’s maximum production rate is up to ten times that of a person. The marketing manager provided the following projections for the next 5 years, the useful life of the robotic equipment. Note: these are the projected sales regardless of whether the robotic equipment is purchased, or it the company continues to use the manual system. Year Sales Dollars 1 $600,000 2 700,000 3 700,000 4 800,000 5 800,000 Currently, the company employs six welders, who work forty hours per week and fifty weeks per year at an average wage of $10 per hour. If the robot is acquired, OSC will need one computer skilled operator, who will be paid $25 per hour. The operator will be required to work 40 hours per week and 50 weeks per year. The robotic system requires the following immediate cash investment, and will be immediately operational, so it will be used for year 1 production if it is purchased. The robotic equipment will be depreciated using 3-year MACRS (Note: GAAP state that all costs required to make equipment operational should be included in the depreciable amount, so the Full $500,000 should be depreciated using the 3-year MACRS values) Purchase price $400,000 Installation 40,000 Testing 60,000 In addition to the labor savings (Labor will become a Fixed cost), the robotic system will: a. reduce the cost of direct materials by 40%, b. reduce the cost of variable overhead by10%, c. reduce the variable selling expense by 20%. All these reductions will take place beginning the year the robotic system starts production. (These costs will still be variable, but at a lower percentage of sales) Fixed costs will be increase by the depreciation associated with the robotic. The robot will be depreciated using 3-year MACRS with depreciating starting in the year the robot becomes operational (year 1). The existing equipment has a current book value of $100,000 and is being depreciated $20,000 per year. If the robot is acquired the existing equipment will be sold at the end of year 1 for $30,000 and will have a book value of $ 80,000 at the time of sale. The company’s tax rate is 30%. Part 1: (Note: Use excel to solve this problem) 1. Prepare a 5-year schedule (plus year 0) of after-tax cash flows for both the manual and robot systems. Assume OSC has a perfect JIT inventory system and will sell for cash every unit of product they manufacture, so there is no upfront working capital investment required for inventory or receivables. YOU DO NOT NEED TO DO ANY PRESENT VALUE CALULATIONS (Note: to continue under the existing manual system, for each $100,000 increase in sales, the company would have to rent an additional piece of equipment at a cost of $10,000 per year and would have to hire an additional welder. That is why direct labor cost and variable manufacturing overhead cost are variable costs under the existing system at the percentages show on page one. Material cost and variable selling expense are also variable under the existing system ant the percentages shown on page one. Under the robotic system, direct labor will become a fixed cost, and the other three will remain variable, but at reduced percentages of sales. Also, under the robotic system, depreciation will change, but miscellaneous manufacturing cost will stay the same) 2. Prepare a schedule of the incremental cash flow from investing in the robotic equipment. That is, show the extra investment that would have to be made immediately, and the extra cash flow that would be generated in years 0 - 5 from the robotic system vs. the cash flows you would achieve anyway if you continued with the manual system. (NOTE: for this incremental schedule YOU DO NOT HAVE TO SHOW THE LINE BY LINE DIFFERENCES, just show the year by year bottom line cash flow under the robotic system, then show the year by year bottom line cash flow under the old system, and finally show the year by year cash flow difference. YOU DO NOT NEED TO DO ANY PRESENT VALUE OR IRR CALULATIONS) BA-2505 – SPRING 2019 Part 2: Answer the following questions by attaching your spreadsheets, and completing the answer sheet below (Use copies of the spreadsheet you developed for part 1.) 1. Using the schedules of cash flows you prepared in part 1: 1a. Calculate the NPV for the robotic equipment using total investment and the total cash flows and using the company’s cost of capital of 9% as the discount rate. 1b. Compute the IRR for the robotic system based on the total investment and the total cash flows. 1c. Calculate the NPV for the existing equipment using the company’s 9% cost of capital as the discount rate. (Note: it is impossible to compute an IRR for the existing system since there is no new investment.) 1d. What is the difference between you answer to part 1a and part 1c? 2. The NPV and IRR for the robot you calculated in parts 1a and 1b are overstated since you could continue to get the cash flow from the existing system without any current investment. 2a. Calculate the NPV for the robotic equipment using incremental investment and the incremental cash flows at company’s cost of capital of 9%. Note this is the same answer as you should have obtained for part 1d. 2b. Compute the IRR for the robotic system based on the incremental investment and the incremental cash flow. 2c. The Comptroller’s division has indicated that there is more risk in pursuing robotic manufacturing vs. expanding the existing manual system, so they feel the incremental cash flows obtained using the robotic system should be discounted at a higher rate than the company’s normal cost of capital to reflect this additional risk. What is the NPV of the incremental investment and incremental cash flow from the robotic system if a discount rate of 20% is used. 3. Assume OSC has decided to go ahead and install the robotic equipment. However, instead of purchasing it with some of the company’s existing cash, the company has decided to investigate borrowing the $500,000 purchase price from a bank or leasing the equipment from the manufacturer. The loan would be at an interest rate of 10.25%, with interest payable at the end of each year based on the principle outstanding during that year. Principle would be paid back at $100,000 per year at the end of each year for 5 years, with the first payments at the end on year 1. The lease would have payments of $133,891 due at the end of each year for 5 years, after which the company would own the robotic equipment. (Note for tax purposes assume this would be considered an operating lease, so the company would not take any depreciation, but the lease payments would be fully deductible for tax purposes.) 3a. Re-calculate the cash flows if the company borrows $500,000 at 12% to finance the purchase. (Note for tax purposes both interest and depreciation are deductible, but principle payments of $100,000 per year are not. However, the principle payments do affect cash flow. The amount of interest each year is based on the 10.25% interest rate times the outstanding balance on the loan for that year. Note: the outstanding balance will go down each year based on the $100,000 principal payment made at the end of the previous years. Depreciation is still based on 3-year MACRS beginning in year 1.) 3b. What is the revised NPV on the re-calculated total cash flow from part a above where the company finances the equipment with a $500,000