(see template attached)
Using the information about Western Manufacturing Company provided below, complete the following two tabs in this MS Excel Workbook:
- Cost-volume-profit (CVP) Analysis prepared in February 20X1 regarding planned operations for month of March 20X1
- Contribution Margin (Full-Absorption vs. Variable Costing Method) Analysis prepared in April 20X1 for completed month of March 20X1
The background paper, Management Accounting Concepts, provides useful guidance for completing this assignment.
Based on the results of your CVP Analysis (the first tab following this one), summarize your recommendations to management regarding its contemplated supplemental March 20X1 advertising campaign described below. Limit the length of your response to 100 words.
Replace the text in this cell with your response.
Based on the results of your Contribution Margin Analysis (the second tab following this one), explain why the reported results using the full-absorption method and variable costing method differ from each other. Limit the length of your response to 100 words.
Replace the text in this cell with your response.
Number of units of the company's only product: Budgeted Mar. 20X1 Actual
Included in inventory on hand at March 1, 20X1 NA 8,000 units
Manufactured during month ended March 31, 20X1 NA 102,000 units
Sales for month of March 20X1 ? 105,000 units
Budgeted total production and sales for fiscal year ended (FYE) Dec. 31, 20X1 1,200,000 NA units
Per-unit information: Budgeted Mar. 20X1 Actual
Average selling price $140.00 $140.00
Variable manufacturing costs
Direct materials (DM) $31.50 $32.00
Direct labor (DL) 26.50 25.50
Manufacturing overhead (MOH) (Var. MOH) 37.00 38.50
Variable selling and administrative (S&A) cost (Var. S&A) $15.00 $14.50
Fixed costs: Budget 20X1
(see Note 1) Mar. 20X1 Actual
Manufacturing overhead (MOH) costs (Fxd MOH) $8,400,000 $714,000
Selling and administrative (S&A) costs (Fxd S&A) $9,000,000 $770,000
Research and development (R&D) costs $6,000,000 $510,000
Budgeted net income for month ended March 31, 20X1 $630,000
Company's projected combined effective tax rate for FY 20X1 40.0% (i.e., 0.40)
Cost of contemplated supplemental advertising campaign for March 20X1 $120,000 (see Note 2)
Note 1 - Management's monthly budget for each category of fixed costs listed is 1/12 of the annual budget amount
Note 2 - Management estimates that the supplemental advertising, if conducted, would generate a 7.5% increase in sales (units and
dollars), compared to budget, for the month of March 20X1.
a. Compute Western Manufacturing Company's budgeted break-even sales - number of units and dollars - for the month of March 20X1
b. Compute the company's required sales - number of units and dollars - necessary to achieve its budgeted net income for the month of March 20X1
c. Compute the company's operating leverage ratio using budgeted operating results for the month of March 20X1
d. Management is contemplating the expanded advertising expenditures for the month of March 20X1 (see Company Information in previous tab). Assuming management does not change the product's selling price, compute the additional amount of sales - units and dollars - necessary to achieve the company's budgeted net income for the month, if it proceeds with the additional advertising campaign.
Full-Absorption (or, Absorption Costing) Method (U.S. GAAP) Variable Costing Method (Contribution Margin Format)
Units Per unit Total Units Per unit Total
Sales (or, Revenue) SP Sales (or, Revenue) SP
Cost of goods sold (COGS) Variable cost of goods sold
Inventory, March 1 (1) Inventory, March 1 (1)
Cost of goods manufactured (1) Variable manufacturing costs (1)
Cost of goods available for sale Cost of goods available for sale
Less: Inventory, March 31 (1) Less: Inventory, March 31 (1)
Cost of goods sold Variable cost of goods sold
Variable selling and admin. expenses (2)
Total variable expenses
[Provide proper label for this subtotal] - [Provide proper label for this subtotal] -
Fixed manufacturing overhead
Selling and admin. expenses (2) Selling and admin. expenses
Research and development expenses Research and development expenses
Income before taxes (4) Income before taxes (4)
Income taxes (5) Income taxes (5)
Net income Net income
(1) Unit costs of production: (3) Variable S&A costs include sales commissions and customer shipping
Direct materials (4) Reconciliation of difference in income before taxes between the two methods:
Direct labor Units Per unit
Manufacturing overhead (MOH) costs Income before taxes - full absorption
Total variable cost per unit Increase (decrease) in inventory (units)
Fixed MOH costs Fixed MOH cost per unit (1)
Total (fully absorbed) cost per unit Fixed MOH cost expensed (deferred)
under full absorption method
(2) Selling and administrative expenses Income before taxes - variable costing
Variable (3) -
Fixed (5) Company's combined (U.S. federal and state) effective income tax rate is
Total 40 percent
Your tutorial is attached in Excel. All the computations are in the cells. The ...
Your tutorial is attached in Excel. All the computations are in the cells. The formulas are given and also you can click in the cells and see the ingredients in each amount.
A short discussion is given for whether to recommend the ad campaign and for why absorption costing and variable costing do not give you the same profits.