Complete Problem 5-40, parts 1 and 2. Your response should be in the form of a M

Complete Problem 5-40, parts 1 and 2.
Your response should be in the form of a Microsoft Excel file. There is no particular form you must follow, the purpose of this assignment is to practice making your own Excel spreadsheet to analyze manufacturing costs. There are good examples in the text and it is acceptable to adopt the format the authors use or to create your own approach. I’m looking for the correct answers as well as good spreadsheet practice, consisting of:
1. Enter all data provided in the problem in the top section of your spreadsheet.
2. Do all computations within the file. No number that can be calculated using other cells in the worksheet should be entered as a number, but instead as a formula.
3. Use formatting (numbers, shading, borders, underlining, etc.) to make your spreadsheet readable and easy to follow.
Franklin Furniture, Inc. (FFI) manufactures bedroom furniture in sets (a set includes a dresser, two queen-size beds, and one bedside table) for use in motels and hotels. FFI has three customer groups, which it calls the value, quality, and luxury groups. The value products are targeted to low-price motels that are looking for simple furniture, while the luxury furniture is targeted to the very best hotels. The value line is attractive to a variety of hotels and motels that appreciate the combination of quality and value. Currently there has been a small increase in the low-cost and value lines, and an appreciable increase in demand in the luxury line, reflecting cyclical changes in the marketplace. Luxury hotels are now in more demand for business travel, while a few years ago, the value segment was the most popular for business travelers. FFI wants to be able to respond to the increased demand with increased production but worries about the increased production cost and about price setting as its mix of customers and production change. FFI has used a volume-based rate based on direct labor hours for some time. Direct labor cost is $15 per hour.
Budgeted Cost Cost Driver
Materials Handling $349,600 # of Parts
Product Scheduling 160,000 # of Production Orders
Setup Labor 216,000 # of Setups
Automated Machinery 1,750,000 Machine Hours
Finishing 619,500 Direct Labor Hours
Pack and Ship 290,400 # of Orders Shipped
$3,385,500
General, Selling, & Admin Costs $5,000,000
The budgeted production data for the three product lines follow.
Product Lines Value Quality Luxury
Units Produced 15000 5000 600
Price $650 $900 $1,200
Direct Materials cost per unit $80 $50 $110
# of Parts per unit 30 50 120
Direct labor hours per unit 4 5 7
Machine hours per unit 3 7 15
Production orders 50 70 200
Production Setups 20 50 50
Orders shipped 1,000 2,000 300
Number of Inspections 2 6 14
Required (Round all rates to two decimal places)
1. Determine the cost per set and the total production cost of each of the three customer groups using activity-based costing.
2. Determine the production cost for each of the three customer groups using FFI’s current volume-based approach.
3.Compare the two approaches and discuss the strategic and competitive issues of using each of the two methods.
4. The activity usage data given in the problem reflects current usage of the various cost drivers to manufacture the firm’s product lines. Suppose you are given the following information regarding the firm’s practical capacity for each of these activities, as follows:
Cost Driver Practical Capacity
# of Parts 990,000
# of Production Orders 800
# of Setups 200
Machine Hours 100,000
Direct Labor Hours 123,900
Number of orders shipped 5,000
compute the new activity rates using practical capacity and Comment on how you would use this additional information for costing the firm’s products and assisting in strategic planning.