Chapter 4 Form Chapter 4: Applying Excel This is a worksheet for Luggage Company Inc. You will calculate 2 methods of applying Manufacturing Overhead: 1) Using a single plant-wide overhead rate and 2)...

Accounting excel spreedsheet



Chapter 4 Form Chapter 4: Applying Excel This is a worksheet for Luggage Company Inc. You will calculate 2 methods of applying Manufacturing Overhead: 1) Using a single plant-wide overhead rate and 2) using an ABC method with 4 activities/cost pools. This exercise will illustrate the differences that occur when indirect costs do not correlate to a single cost driver and there is a need to use multiple drivers in order to have product cost data that is relevant and accurate for pricing and decision-making. Enter a formula into each of the cells marked with a ? below Review Problem: Activity-Based Costing Data to be used in your analysisProducts DeluxeTourist Annual sales in units2,00010,000Note: Production units = sales units for this assignment Direct materials per unit$25$17 Direct labor-hours per unit54 Direct labor rate$12per DLH Estimated OverheadExpected Activity for each Cost Pool Activities and Activity MeasuresCostDeluxeTouristTotal Labor related (direct labor-hours)$ 80,000??? Machine setups (setups)150,0003,0002,000? Production orders (orders)70,000100300? General factory (machine-hours)250,00012,00028,000? ? Compute the predetermined overhead rate Estimated total manufacturing overhead (a)? Estimated total amount of the allocation base (b)?DLHs Predetermined overhead rate (a) ÷ (b)?per DLH USING A PLANT-WIDE OVERHEAD RATE Compute the manufacturing overhead appliedDeluxeTouristTO COST THE 2 DIFFERENT PRODUCTS Direct labor-hours per unit (a)?DLHs?DLHs Predetermined overhead rate (b)?per DLH?per DLH Manufacturing overhead applied per unit (a) × (b)?? Compute traditional unit product costsDeluxeTourist Direct materials?? Direct labor?? Manufacturing overhead applied?? Traditonal unit product cost?? Compute activity ratesEstimated Overhead ActivitiesCostTotal Expected ActivityActivity Rate Labor related??DLHs?per DLH Machine setups??setups?per setup Production orders??orders?per orderUSING AN ABC METHODOLOGY General factory??MHs?per MHTO COST THE 2 PRODUCTS Compute the ABC overhead cost per unitDeluxeTourist ActivityExpectedExpected ActivitiesRateActivityAmountActivityAmount Labor related????? Machine setups????? Production orders????? General factory????? Total overhead cost assigned (a)?? Number of units produced (b)?? ABC overhead cost per unit (a) ÷ (b)?? Compute the ABC unit product costsDeluxeTourist Direct materials?? Direct labor?? ABC overhead cost per unit (see above)?? ABC unit product cost?? &F Page &P