Excel question for Accouting question (Solutions have been provided)
choupizhu
ExcelforAccounting14-27.docx
Requirement: Please based on the solutions and ideas (Check the attachment named “Idea 14-27”), using Excel skills to solve the questions. Please don’t copy sentence of conclusion from the examples, therefore you need to use your own words to answer it. Just reminder: For this question, please don’t forget the Question 2.
Solution here:
1. Budget for 2017
Variable Contrib.
Selling Cost Margin Units Sales Contribution
Price per Unit per Unit Sold Mix Margin
(1) (2) (3) = (1) – (2) (4) (5) (6) = (3) × (4)
Kostor $12.00 $7.20 $4.80 130,000 52% $ 624,000
Limba 15.00 8.25 6.75 120,000 48 810,000
Total 250,000 100% $1,434,000
Actuals for 2017 Variable Contrib.
Selling Cost Margin Units Sales Contribution
Price per Unit per Unit Sold Mix Margin
(1) (2) (3) = (1) – (2) (4) (5) (6) = (3) × (4)
Kostor $12.50 $8.00 $4.50 132,000 55% $ 594,000
Limba 16.00 7.75 8.25 108,000 45 891,000
Total 240,000 100% $1,485,000
Solution Exhibit 14-27 presents the sales-volume, sales-quantity, and sales-mix variances for each product and in total for 2017.
Kostor = (132,000 – 130,000) × $4.80 = $ 9,600 F
Limba = (108,000 – 120,000) × $6.75 = 81,000 U
Total $71,400 U
Kostor = (240,000 – 250,000) × 0.52 × $4.80 = $24,960 U
Limba = (240,000 – 250,000) × 0.48 × $6.75 = 32,400 U
Total $57,360 U
Kostor = 240,000 × (0.55 – 0.52) × $4.80 = $34,560 F
Limba = 240,000 × (0.45 – 0.48) × $6.75 = 48,600 U
Total $14,040 U
2. The breakdown of the unfavorable sales-volume variance of $71,400 shows that the biggest contributor is the 10,000 unit overall decrease in sales resulting in an unfavorable sales-quantity variance of $57,360. There is a further unfavorable sales-mix variance of $14,040 in contribution margin as a result of the sales mix shifting away from the more profitable Limba (contribution margin of $6.75 versus contribution margin of $4.80 for Kostor). (For Question 2, It’s only an example was given by professor, please finish it by your own words.)
SOLUTION EXHIBIT 14-27
Sales-Mix and Sales-Quantity Variance Analysis of Emcee Inc. for 2017
Flexible Budget: Static Budget:
Actual Units of Actual Units of Budgeted Units of
All Products Sold All Products Sold All Products Sold
Actual Sales Mix Budgeted Sales Mix Budgeted Sales Mix
Budgeted Contribution Budgeted Contribution Budgeted Contribution
Margin Per Unit Margin Per Unit Margin Per Unit
Kostor 240,000 0.55 $4.80 = $ 633,600 240,000 0.52 $4.80 = $ 599,040 250,000 0.52 $4.80 = $ 624,000
Limba 240,000 0.45 $6.75 = 729,000 240,000 0.48 $6.75 = 777,600 250,000 0.48 $6.75 = 810,000
$1,362,600 $1,376,640 $1,434,000
$14,040 U $57,360 U
Sales-mix variance Sales-quantity variance
$71,400 U
Sales-volume variance
F = favorable effect on operating income; U= unfavorable effect on operating income
Sales-quantity
variance
Actual unitsBudgeted units
of all of all
products soldproducts sold
æö
ç÷
=-´
ç÷
èø
Budgeted
sales-mix
percentage
´
Budgeted
contribution margin
per unit
Sales-mix
variance
Actual units of
all products
sold
Actual
sales-mix
percentage
æ
ç
ç
è
Budgeted
sales-mix
percentage
ö
÷
÷
ø
Budgeted
contribution margin
per unit
Sales-volume
variance
ActualBudgeted
quantity of quantity of
units soldunits sold
æö
ç÷
-´
ç÷
èø
Budgeted
contribution margin
per unit
