ACC 311 Module 2 Problem Set 1-5
Solution
A B C D E F G H I |
1 lla: Subtract; Cell Referencing
2
EJl BE4.2 - Using Excel to Calculate Contribution Margin
4 PROBLEM Required: Provide input into cells shaded in yellow in this template. Input
5 ;The local ice cream shop sells single-scoop cones. The store the required mathematical formulas or functions with cell references to the
6 |employs a manager plus one full-time and two part-time Problem area or work area as indicated.
7 |employees, and also incurs costs for labor, rent, and insurance.
8 |Information concerning operations follows.
9 | What is the contribution margin per unit of a single-scoop cone?
10 Selling price of single-scoop cones S 2.00
11 Unit cost of ice cream per scoop 0.25 Contribution margin per unit S 1.70"
12 | Unit cost of cone 0.05
13 | Montlhy labor costs 3,500
14 | Monthly rent and insurance cost 500
15|
Cell Correct Results Feedback
Input In
H11 =C10-C11- $1.70 The contribution margin per unit is calculated by subtracting the total variable cost per unit of
C12 A the ice cream and cone totaling 30 cents from selling price of $2.00, resulting in $1.70 per unit.
Betty is helping her company consider a change in its CVP relationship to increase profitability. Currently, the company is selling
14,100 units, generating $76,600 in operating income. The contribution margin is $22 per unit, while total variable costs are
$253,800.
What amount of fixed costs does the company currently incur?
Fixed costs $ 233600
If it increases its selling price by 10% while expecting volume to drop byjust 5%, will the company achieve its goal?
New operating income $ l 114670
The company | achieved j its goal.
, Contribution margin per unit $22
x Total units 14,100
Total contribution margin $310,200
Operatingincome = CM - Fixed costs
$76600 = $310,200 - Fixed costs
Fixed costs = $233,600
Contributionmargin = Sales - Variable costs
$310,200 = Sales - $253,800
Sales = $564,000
Presented in contribution margin income statement format:
Contribution Margin Income Statement
Sales $564,000
Less: Variable costs 253,800
Contribution margin $310,200
Less: Fixed costs 233,600
Operatingincome $76,600
With an increase is selling price of 10% and a decrease in sales volume of 5%, we first determine the new sales volume, new Sales,
and new Variable costs:
Sales volume 14,100 Sales $564,000 Variable costs $253,800
x95% 095 +Units 14,100 + original volume 14,100
Newsalesvolume 13,395 Selling price per unit $40 Variable cost
per unit $18
xIncrease 110 xNew volume 13,395
New selling price per unit $44 New variable costs $241,110
xNew volume 13,395
New sales $589,380
Solution
A B C D E F G H I |
1 lla: Subtract; Cell Referencing
2
EJl BE4.2 - Using Excel to Calculate Contribution Margin
4 PROBLEM Required: Provide input into cells shaded in yellow in this template. Input
5 ;The local ice cream shop sells single-scoop cones. The store the required mathematical formulas or functions with cell references to the
6 |employs a manager plus one full-time and two part-time Problem area or work area as indicated.
7 |employees, and also incurs costs for labor, rent, and insurance.
8 |Information concerning operations follows.
9 | What is the contribution margin per unit of a single-scoop cone?
10 Selling price of single-scoop cones S 2.00
11 Unit cost of ice cream per scoop 0.25 Contribution margin per unit S 1.70"
12 | Unit cost of cone 0.05
13 | Montlhy labor costs 3,500
14 | Monthly rent and insurance cost 500
15|
Cell Correct Results Feedback
Input In
H11 =C10-C11- $1.70 The contribution margin per unit is calculated by subtracting the total variable cost per unit of
C12 A the ice cream and cone totaling 30 cents from selling price of $2.00, resulting in $1.70 per unit.
Betty is helping her company consider a change in its CVP relationship to increase profitability. Currently, the company is selling
14,100 units, generating $76,600 in operating income. The contribution margin is $22 per unit, while total variable costs are
$253,800.
What amount of fixed costs does the company currently incur?
Fixed costs $ 233600
If it increases its selling price by 10% while expecting volume to drop byjust 5%, will the company achieve its goal?
New operating income $ l 114670
The company | achieved j its goal.
, Contribution margin per unit $22
x Total units 14,100
Total contribution margin $310,200
Operatingincome = CM - Fixed costs
$76600 = $310,200 - Fixed costs
Fixed costs = $233,600
Contributionmargin = Sales - Variable costs
$310,200 = Sales - $253,800
Sales = $564,000
Presented in contribution margin income statement format:
Contribution Margin Income Statement
Sales $564,000
Less: Variable costs 253,800
Contribution margin $310,200
Less: Fixed costs 233,600
Operatingincome $76,600
With an increase is selling price of 10% and a decrease in sales volume of 5%, we first determine the new sales volume, new Sales,
and new Variable costs:
Sales volume 14,100 Sales $564,000 Variable costs $253,800
x95% 095 +Units 14,100 + original volume 14,100
Newsalesvolume 13,395 Selling price per unit $40 Variable cost
per unit $18
xIncrease 110 xNew volume 13,395
New selling price per unit $44 New variable costs $241,110
xNew volume 13,395
New sales $589,380
