Course Project 2 Instructions (Read-Only)- Microsoft Word non-commercial use References Mailings Review View -|||| Emphasis Subtitle Styles Title x, x' Aa,A- 1:3 Heading 1 Strong Normal Paragraph Hampton Company is a producer of house paints. The company'sproduction department has been investigatingpossible ways to trim total production costs. One possibility currently being examined is to make the paintcans instead of purchasing them. The equipmentneeded would cost$1,000,000, with a disposal value of $200,000, and would be able to produce 27,500,000 cans overthe life of the machinery. The production department estimates that approximately 5,500,000 cans would be needed foreach of the next 5 years. The company would hire six new employees to produce the paintcans. These six individuals would be full-time employees working 2,000 hours per yearand eatning $15.00 perhour. They would also receive the same benefitsas other production employees, 15% of wages in addition to $2,000 of health benefits It is estimated thatthe raw materials will cost 30p per can and that other variable costs would be 10 percan. Because there is currently unused space in the factory, no additional fixed costs would be incurred if this proposal is accepted. It is expected thatcans would cost 50p each if purchased from the current supplier. The company's minimum rate of return (hurdle rate) has been determined to be 1 1 % for all new projec company's products as well as numberof units sold will not be affected by this decision. The unit- of-production depreciation method would be used if the new equipment is purchased. cts, and the current tax rate of 35% is anticipated to remain unchanged. The pricing for the Required 1.Based on the above information and usina Excel.calculate the followina items for this Required: 1. Based on the above information and using Excel, calculate the following items for this proposed equipmentpurchase o Annualcash flows over the expected life of the equipment o Payback period o Simple rate of return o Netpresent value o Internal rate of return The check figure for the total annual after-taxcash flows is $271,150. 7 Data: Cost of new equipment Expected life of equipment in years Disposal value in 5 years Life production-number of cans Annual production or purchase needs Initial training costs Number of workers needed Annual hours to be worked per employee Earnings per hour for employees Annual health benefits per employee 16 17 18 Other annual benefits per employee-% of wages Cost of raw materials per can Other variable production costs per can Costs to purchase cans- per can Required rate of return 21 Tax rate Make Purchase 6 Cost to Produce 8 29 30 31 32 Annual cost of direct material: Need of 1 million cans per year Annual cost of direct labor for new employees: Wages Health benefits Other benefits 34 35 36 37 38 39 Total wages and benefits Other variable production costs Total annual production costs Annual cost to purchase cans 40 2 Part 1 Cash Flows Over the Life of the Project 13 Before Tax Amount Tax Effect After Tax Amount Item Annual cash savings Tax savings due to depreciation 45 46 47 48 49 50 otal after-tax annual cash flow 51 Part 2 Payback Period 52 53 54 years 56 Part 3 Simple Rate of Return 58 59 60 61 62 63 Accounting income as result of decreased costs Annual cash savings Less depreciation Before tax income Tax at 35% rate After tax income 6 Part 4 Net Present Value After Tax 10% PV Present Before Tax Amount Yeal Tax % Amount Factor Value Cost of machine Cost of traning Annual cash savings Tax savings due to depreciation Disposal value Net Present Value