Basic CVP Analysis – Using Goal Seek Determine the breakeven point for the Drill/Driver product line. Pittsburgh Tools is considering modifications to the 18v Light production line. Analysis suggests that the modifications could result in $0.65 per unit variable cost reduction and $1,300 per quarter fixed cost increase. Assuming these changes have no impact on volume, determine the price that would be necessary to achieve $0 product line margin?

FINANCIAL ACCOUNTING
10th Edition
ISBN:9781259964947
Author:Libby
Publisher:Libby
Chapter1: Financial Statements And Business Decisions
Section: Chapter Questions
Problem 1Q
icon
Related questions
icon
Concept explainers
Question
100%

Each of the question below is to be treated independently.

 

Basic CVP Analysis – Using Goal Seek

  1. Determine the breakeven point for the Drill/Driver product line.
  2. Pittsburgh Tools is considering modifications to the 18v Light production line. Analysis suggests that the modifications could result in $0.65 per unit variable cost reduction and $1,300 per quarter fixed cost increase. Assuming these changes have no impact on volume, determine the price that would be necessary to achieve $0 product line margin?

Multiproduct CVP Analysis – Using Solver

  1. Assuming the current sales mix reaches the targeted sales mix, determine the breakeven point in sales revenue necessary for the division to achieve $0 divisional operating income (breakeven).
  2. Assume the current sales mix reaches the targeted sales mix, determine the sales revenue necessary for the division to achieve $100,000 divisional operating income.

 

See the Excel instructions on the next page

  1. Making Copies of a Worksheet Tab
  • Your completed Excel file will have 5 worksheet tabs (the original Quarter Budget and one additional worksheet tab for each of the 4 questions above)
    • Right-click on the worksheet tab name > Move or Copy… > (move to end) > Create a copy > OK
  1. Finding and Using Goal Seek
  • Find the Data tab: Data > Forecast Group > What-if Analysis > Goal Seek
    • Select the target cell, specify the target value, select the cell allowed to change, select OK
  1. Activating Solver
    • File > Options > Add-ins > Manage: Excel Add-ins > Go
    • Select Solver Add-in > OK
      • The Solver tool will appear on the Data ribbon in the Analyze group

 

 

  1. Using Solver
    • Data ribbon > Analyze group > Solver
      • Set objective: select the target cell (divisional operating income is I12)
      • To Value of: specify the target value
      • By Changing Variable Cells: select individual cells allowed to change separated by a comma “,” (D5,F5,H5)
      • Subject to the Constraints:
        • Add > Cell Reference and Constraint (D14 = D15 and F14 = F15, and I14 = I15)
          • Note that this specifies the sales mix for two of the three product lines and the total sales mix. This will allow the Solver tool a small amount of flexibility to achieve a valid mathematical result.
        • Solve
Below is the budgeted P&L Statement for Pittsburgh Tools, Inc - Cordless Division.
Sales volume (units)
Revenue
Variable costs
Contribution margin
Direct fixed costs
Product line margin
Common fixed SG&A expenses
Divisional operating income
Current sales mix (revenue)
Target sales mix (revenue)
Pittsburgh Tools - Cordless Division
Budgeted Quarterly Product Line Contribution Margin Analysis
18v Drill/Driver
18v Circular Saw
4,300
$537,500 $125.00
223,600
52.00
$313,900
$73.00 $254,200
137,958
142,083
$175,942
$112,117
3,100
$465,000 $150.00
210,800
68.00
$82.00
42.6%
42.6%
36.8%
36.8%
18v Light
$260,000
145,600
$114,400
122,980
($8,580)
5,200
12,600
$50.00 $1,262,500
28.00
$22.00 $
$
Total
20.6%
20.6%
580,000
682,500
403,021
279,479
200,000
79,479
100.0%
Transcribed Image Text:Below is the budgeted P&L Statement for Pittsburgh Tools, Inc - Cordless Division. Sales volume (units) Revenue Variable costs Contribution margin Direct fixed costs Product line margin Common fixed SG&A expenses Divisional operating income Current sales mix (revenue) Target sales mix (revenue) Pittsburgh Tools - Cordless Division Budgeted Quarterly Product Line Contribution Margin Analysis 18v Drill/Driver 18v Circular Saw 4,300 $537,500 $125.00 223,600 52.00 $313,900 $73.00 $254,200 137,958 142,083 $175,942 $112,117 3,100 $465,000 $150.00 210,800 68.00 $82.00 42.6% 42.6% 36.8% 36.8% 18v Light $260,000 145,600 $114,400 122,980 ($8,580) 5,200 12,600 $50.00 $1,262,500 28.00 $22.00 $ $ Total 20.6% 20.6% 580,000 682,500 403,021 279,479 200,000 79,479 100.0%
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 3 steps with 4 images

Blurred answer
Follow-up Questions
Read through expert solutions to related follow-up questions below.
Follow-up Question

When i do solver for Q3,  I am not getting same answer as you. I am getting Divisional operating income -0.08. 18vDrill - $3802, 18 ircular $2737, 18v light - $4596

Why is that? What am I doing wrong? 

Solution
Bartleby Expert
SEE SOLUTION
Follow-up Question

can you do these two too please ? 

 

Solution
Bartleby Expert
SEE SOLUTION
Knowledge Booster
Cost volume profit (CVP) analysis
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, accounting and related others by exploring similar questions and additional content below.
Similar questions
  • SEE MORE QUESTIONS
Recommended textbooks for you
FINANCIAL ACCOUNTING
FINANCIAL ACCOUNTING
Accounting
ISBN:
9781259964947
Author:
Libby
Publisher:
MCG
Accounting
Accounting
Accounting
ISBN:
9781337272094
Author:
WARREN, Carl S., Reeve, James M., Duchac, Jonathan E.
Publisher:
Cengage Learning,
Accounting Information Systems
Accounting Information Systems
Accounting
ISBN:
9781337619202
Author:
Hall, James A.
Publisher:
Cengage Learning,
Horngren's Cost Accounting: A Managerial Emphasis…
Horngren's Cost Accounting: A Managerial Emphasis…
Accounting
ISBN:
9780134475585
Author:
Srikant M. Datar, Madhav V. Rajan
Publisher:
PEARSON
Intermediate Accounting
Intermediate Accounting
Accounting
ISBN:
9781259722660
Author:
J. David Spiceland, Mark W. Nelson, Wayne M Thomas
Publisher:
McGraw-Hill Education
Financial and Managerial Accounting
Financial and Managerial Accounting
Accounting
ISBN:
9781259726705
Author:
John J Wild, Ken W. Shaw, Barbara Chiappetta Fundamental Accounting Principles
Publisher:
McGraw-Hill Education