Excel question for Accouting question (Solutions have been provided)

ExcelforAccounting14-28.docx

Home >Business & Finance homework help >Accounting homework help >Excel question for Accouting question (Solutions have been provided)

Requirement: Please based on the solutions and ideas (Check the attachment, named “Idea for 14-28”), using Excel skills to solve the questions. Please don’t copy sentence of conclusion from the examples, therefore you have to use your own words to answer it. Just reminder: For this question, please don’t forget Comment on the results.

Solution here:

In some editions of the text, the last sentence before “Required” reads “However, actual total sales volume in the western region was 1.5 million cartons.” The word “western” should be replaced by “Midwest.”

Actual Budgeted

Midwest region 1.5 million 1.25 million

Emcee Bakery 240,000 250,000

Market share 16% 20%

Average budgeted contribution margin per unit = $5.736 ($1,434,000 ÷ 250,000).

Solution Exhibit 14-28 presents the sales-quantity variance, market-size variance, and market-share variance for 2017.

= –

= 1,500,000 × (0.16 – 0.20) × $5.736

= 1,500,000 × 0.04 × $5.736

= $344,160 U

= –

= (1,500,000 – 1,250,000) × 0.2 × $5.736

= 250,000 × 0.2 × $5.736

= 286,800 F

The market share variance is unfavorable because the actual 16% market share was lower than the budgeted 20% market share. The market size variance is favorable because the market size increased 20% [(1,500,000 – 1,250,000) ÷ 1,250,000].

The unfavorable market-share variance was greater than the increase in market size variance resulting in an unfavorable sales-quantity variance.

Sales-Quantity Variance

$57,360 U

Market-share variance Market-size variance

$344,160 U $286,800 F

SOLUTION EXHIBIT 14-28

Market-Share and Market-Size Variance Analysis of Emcee Inc. for 2017

Static Budget: Actual Market Size Actual Market Size Budgeted Market Size Actual Market Share Budgeted Market Share Budgeted Market Share Budgeted Average Budgeted Average Budgeted Average Contribution Margin Contribution Margin Contribution Margin Per Unit Per Unit Per Unit

1,500,000 0.16a $5.736b 1,500,000 0.2c $5.736b 1,250,000 0.2c $5.736b

$1,376,640 $1,720,800 $1,434,000

$344,160 U $286,800 F

Market-share variance Market-size variance

$57,360 U

Sales-quantity variance

F = favorable effect on operating income; U = unfavorable effect on operating income

aActual market share: 240,000 units ÷ 1,500,000 units = 0.16, or 16%

bBudgeted average contribution margin per unit $1,434,000 ÷ 250,000 units = $5.736 per unit

cBudgeted market share: 250,000 units ÷ 1,250,000 units = 0.2, or 20%

Budgeted

market

Budgeted contribution

margin per composite

unit for budgeted mix

Market-size

variance

Actual

market size

in units

Budgeted

market size

in units

Budgeted

market

Market share

variance

Actual market

size in units

Actual

market