please show all working and formulas in excel so that i may see how you arrived at answers

ACT 5140 - Accounting For Decision Makers Week #4 - HW Assignment You MUST show all work in order to receive credit for the problems. Problem 1 Lion Company has identified the following overhead costs and cost drivers for next year: Overhead Item Expected Costs Setup costs Ordering costs Maintenance Power $1,166,400 540,000 1,856,000 180,000 Cost Driver Number of setups Number of orders Machine-hours Kilowatt-hours Maximum Quantity 4,800 40,000 64,000 400,000 The following are two of the jobs completed during the year: Direct materials Direct labor Units completed Direct labor-hours Number of setups Number of orders Machine-hours Kilowatt-hours Job 201 $18,000 $24,000 750 180 12 16 360 180 Job 202 $20,000 15,000 600 220 15 30 300 240 Required: Determine the unit cost for each job using the four cost drivers. (Round amounts to 2 decimal places.) Problem 2 Bailey Corporation has four categories of overhead. The expected overhead costs for each category for next year are as follows: Maintenance Materials handling Setups Inspection Total: $420,000 180,000 150,000 300,000 $1,050,000 Expected activity for the four activity-based cost drivers that would be used is: Machine-hours Material moves Setups Quality inspections 20,000 4,000 200 8,000 The company has been asked to submit a bid for a proposed job. The plant manager believes that obtaining this job would result in new business in future years. Bids are usually based upon full manufacturing cost plus 30 percent. Estimates for the proposed job are as follows: Direct materials Direct labor (750 hours) Number of material moves Number of inspections Number of setups Number of machine-hours $10,000 $15,000 8 5 3 300 Required: 1. Determine the amount of overhead that would be allocated to the proposed job if the Traditional Approach is used and 40,000 direct labor-hours are used as the volumebased cost driver. Determine the total costs of the proposed job. Determine the company's bid if the bid is based upon full manufacturing cost plus 30 percent. (Round amounts to 2 decimal places.) 2. Determine the amount of overhead that would be applied to the proposed project if activity-based costing (ABC) is used. Determine the total costs of the proposed job if activity-based costing is used. Determine the company's bid if activity-based costing is used and the bid is based upon full manufacturing cost plus 30 percent. (Round amounts to 2 decimal places.) 3. Which product costing method produces the more competitive bid? Problem 3 Midtown Inc. uses an activity based costing system contains the following six activity cost pools: Estimated Overhead Costs $ 1,100,000 120,000 675,000 1,350,000 1,470,000 405,000 Activity Cost Pool Indirect Labor Machine processing Machine setups Production orders Shipments Product Testing Total: Estimated Activity 100,000 Direct Labor Hours 40,000 Machine Hours 15,000 Setups 9,000 Orders 12,250 Shipments 450 Products $ 5,120,000 Activity data have been supplied for the following two jobs: Number of units produced Direct Labor Hours Machine Hours Setups Orders Shipments Products JOB 485 400 850 1,475 5 8 14 2 JOB 486 950 700 1300 2 3 1 2 Required: 1. Calculate the activity rates for each of the pools. 2. Determine the amount of overhead to be allocated to each job. 3. Calculate the amount of overhead per unit produced