Excel Question For Accouting Question (Solutions Have Been Provided)

The solutions and ideas have been given, please using Excel skills to solve the accounting questions. Please check the attachment for details.

Due on Oct-17 at 3PM

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

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

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

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

0 replies