cost accounting
amnah96
Eldenburg_Wolcott_Wk09_ch04.ppt© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 1
Cost Management
Measuring, Monitoring, and Motivating Performance
Chapter 4
Relevant Information for Decision Making
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 2
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Learning objectives
Q1: What is the process for identifying and using relevant information in decision making?
Q2: How is relevant quantitative and qualitative information used in special order decisions?
Q3: How is relevant quantitative and qualitative information used in keep or drop decisions?
Q4: How is relevant quantitative and qualitative information used in outsourcing (make or buy) decisions?
Q5: How is relevant quantitative and qualitative information used in product emphasis and constrained resource decisions?
Q6: What factors affect the quality of operating decisions?
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 3
Q1: Nonroutine Operating Decisions
annual budgets and resource allocation decisions
Routine operating decisions are those made on a regular schedule. Examples include:
monthly production planning
weekly work scheduling issues
accept or reject a customer’s special order
Nonroutine operating decisions are not made on a regular schedule. Examples include:
keep or drop business segments
insource or outsource a business activity
constrained (scarce) resource allocation issues
The first primary bullet & its 3 secondary bullets are automated on 1.5 second delays.
The first click brings in the second primary bullet – its 4 secondary bullets are automated on 1.5 second delays.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 4
Q1: Nonroutine Operating Decisions
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 5
Q1: Process for Making Nonroutine Operating Decisions
1. Identify the type of decision to be made.
2. Identify the relevant quantitative analysis technique(s).
3. Identify and analyze the qualitative factors.
4. Perform quantitative and/or qualitative analyses
5. Prioritize issues and arrive at a decision.
One click for each element after the first one, which is automated.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 6
Q1: Identify the Type of Decision
Special order decisions
determine the pricing
accept or reject a customer’s proposal for order quantity and pricing
identify if there is sufficient available capacity
Keep or drop business segment decisions
examples of business segments include product lines, divisions, services, geographic regions, or other distinct segments of the business
eliminating segments with operating losses will not always improve profits
The first primary bullet & its 3 secondary bullets are automated on 1.5 second delays.
The first click brings in the second primary bullet – its 2 secondary bullets are automated on 1.5 second delays.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 7
Q1: Identify the Type of Decision
Outsourcing decisions
make or buy production components
perform business activities “in-house” or pay another business to perform the activity
Constrained resource allocation decisions
determine which products (or business segments) should receive allocations of scarce resources
examples include allocating scarce machine hours or limited supplies of materials to products
Other decisions may use similar analyses
The first primary bullet & its 3 secondary bullets are automated on 1.5 second delays.
The first click brings in the second primary bullet – its 2 secondary bullets are automated on 1.5 second delays.
3. The second click brings in the third primary bullet.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 8
Q1: Identify and Apply the Relevant Quantitative Analysis Technique(s)
Regression, CVP, and linear programming are examples of quantitative analysis techniques.
Analysis techniques require input data.
Data for some input variables will be known and for other input variables estimates will be required.
Many nonroutine decisions have a general decision rule to apply to the data.
The results of the general rule need to be interpreted.
The quality of the information used must be considered when interpreting the results of the general rule.
One mouse click is required for each of the 4 primary bullets, except the first one, which is automated– the secondary bullets are automated on a 1 second delay.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 9
Q2-Q5 : Identify and Analyze Qualitative Factors
Qualitative information cannot easily be valued in dollars.
can be difficult to identify
Examples of qualitative information that may be relevant in some nonroutine decisions include:
quality of inputs available from a supplier
can be every bit as important as the quantitative information
effects of decision on regular customers
effects of production on the environment or the community
effects of decision on employee morale
The first primary bullet & its 2 secondary bullets are automated on 1.5 second delays.
The first click brings in the second primary bullet – its 4 secondary bullets are automated on 1.5 second delays.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 10
Q1: Consider All Information and Make a Decision
Before making a decision:
Consider all quantitative and qualitative information.
Consider the quality of the information.
Judgment is required when interpreting the effects of qualitative information.
Judgment is also required when user lower-quality information.
The “before making a decision” element is automated.
1. The first mouse click brings in the first secondary bullet – its sole tertiary bullet is automated on a 1.5 second delay.
2. The second mouse click brings in the second secondary bullet – its sole tertiary bullet is automated on a 1.5 second delay.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 11
Q2: Special Order Decisions
A new customer (or an existing customer) may sometimes request a special order with a lower selling price per unit.
The general rule for special order decisions is:
accept the order if incremental revenues exceed incremental costs,
If the special order replaces a portion of normal operations, then the opportunity cost of accepting the order must be included in incremental costs.
subject to qualitative considerations.
Price >= Relevant Relevant Opportunity
Variable Costs + Fixed Costs + Cost
The first primary bullet is automated.
The first mouse click brings in the second primary bullet – its 2 secondary bullets are automated on 1.5 second delays.
The second mouse click brings in the third primary bullet.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 12
RobotBits, Inc. makes sensory input devices for robot manufacturers. The normal selling price is $38.00 per unit. RobotBits was approached by a large robot manufacturer, U.S. Robots, Inc. USR wants to buy 8,000 units at $24, and USR will pay the shipping costs. The per-unit costs traceable to the product (based on normal capacity of 94,000 units) are listed below. Which costs are relevant to this decision?
Q2: Special Order Decisions
Relevant?
yes
$20.00
Relevant?
yes
Relevant?
yes
Relevant?
no
Relevant?
yes
Relevant?
no
Relevant?
no
no
The given cost information is automated.
For each cost element, one click begins the “relevant?” and “yes/no” sequences; the “yes/no” responses are on a 1.5 second delay.
Note for shipping/handling, first the answer comes in as “yes” because this is normally an incremental relevant cost, but then “no” comes in to highlight that this is not relevant in this specific instance.
A final click brings in the circle, arrow, and $20 total for the relevant costs.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 13
Suppose that the capacity of RobotBits is 107,000 units and projected sales to regular customers this year total 94,000 units. Does the quantitative analysis suggest that the company should accept the special order?
Q2: Special Order Decisions
First determine if there is sufficient idle capacity to accept this order without disrupting normal operations:
Projected sales to regular customers 94,000 units
Special order 8,000 units
102,000 units
RobotBits still has 5,000 units of idle capacity if the order is accepted. Compare incremental revenue to incremental cost:
Incremental profit if accept special order =
($24 selling price - $20 relevant costs) x 8,000 units = $32,000
1. The first click brings in the “First determine..” element.
2. The second click begins the sequence to determine if there is idle capacity, and the conclusion that there is sufficient capacity to accept.
3. The third click brings in “incremental profit if accept . . .” and the computation of incremental profit.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 14
What qualitative issues, in general, might RobotBits consider before finalizing its decision?
Will USR expect the same selling price per unit on future orders?
Will other regular customers be upset if they discover the lower selling price to one of their competitors?
Will employee productivity change with the increase in production?
Given the increase in production, will the incremental costs remain as predicted for this special order?
Are materials available from its supplier to meet the increase in production?
Q2: Qualitative Factors in Special Order Decisions
One mouse click is required for each bullet.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 15
Suppose instead that the capacity of RobotBits is 100,000 units and projected sales to regular customers this year totals 94,000 units. Should the company accept the special order?
Q2: Special Order Decisions and Capacity Issues
Here the company does not have enough idle capacity to accept the order:
Projected sales to regular customers 94,000 units
Special order 8,000 units
102,000 units
If USR will not agree to a reduction of the order to 6,000 units, then the offer can only be accepted by denying sales of 2,000 units to regular customers.
1. The first click brings in the “Here the company...” element.
2. The second click begins the sequence to determine if there is idle capacity, and the conclusion that there is sufficient capacity to accept.
The rest of the elements are automated.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 16
Suppose instead that the capacity of RobotBits is 100,000 units and projected sales to regular customers this year total 94,000 units. Does the quantitative analysis suggest that the company should accept the special order?
Q2: Special Order Decisions and Capacity Issues
CM/unit on regular sales
= $38.00 - $22.50 = $15.50.
The opportunity cost of accepting this order is the lost contribution margin on 2,000 units of regular sales.
Incremental profit if accept special order =
$32,000 incremental profit under idle capacity – opportunity cost =
Variable cost/unit for regular sales = $22.50.
$32,000 - $15.50 x 2,000 = $1,000
The given information is automated.
1. The first click brings in the computation of variable cost per unit for regular sales
2. The second click reveals the computation of the CM/unit on regular sales.
The shadowed box is automated.
3. The third click brings in the sequence that computes the incremental profit if accept.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 17
What additional qualitative issues, in this case of a capacity constraint, might RobotBits consider before finalizing its decision?
What will be the effect on the regular customer(s) that do not receive their order(s) of 2,000 units?
What is the effect on the company’s reputation of leaving orders from regular customers of 2,000 units unfilled?
Will any of the projected costs change if the company operates at 100% capacity?
Are there any methods to increase capacity? What effects do these methods have on employees and on the community?
Notice that the small incremental profit of $1,000 will probably be outweighed by the qualitative considerations.
Q2: Qualitative Factors in Special Order Decisions
One mouse click is required for each bullet.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 18
Q3: Keep or Drop Decisions
Managers must determine whether to keep or eliminate business segments that appear to be unprofitable.
The general rule for keep or drop decisions is:
keep the business segment if its contribution margin covers its avoidable fixed costs,
If the business segment’s elimination will affect continuing operations, the opportunity costs of its discontinuation must be included in the analysis.
subject to qualitative considerations.
Drop if: Contribution < Relevant Opportunity
Margin Fixed Costs + Cost
The first primary bullet is automated.
The first mouse click brings in the second primary bullet – its 2 secondary bullets are automated on 1.5 second delays.
The second mouse click brings in the third primary bullet.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 19
Starz, Inc. has 3 divisions. The Gibson and Quaid Divisions have recently been operating at a loss. Management is considering the elimination of these divisions. Divisional income statements (in 1000s of dollars) are given below. According to the quantitative analysis, should Starz eliminate Gibson or Quaid or both?
Q3: Keep or Drop Decisions
This slide is entirely automated.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 20
Q3: Keep or Drop Decisions
Use the general rule to determine if Gibson and/or Quaid should be eliminated.
The general rule shows that we should keep Quaid and drop Gibson.
The shadowed text box is automated.
The first click brings in the computation for CM – avoidable fixed costs.
The second click brings in the conclusion to keep Quaid and drop Gibson.
The right arrow is automated.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 21
Q3: Keep or Drop Decisions
Using the general rule is easier than recasting the income statements:
Quaid & Russell only
Profits increase by $11 when Gibson is eliminated.
The shadowed text box is automated, as is the recast income statements and the “Quaid & Russell only” notation.
The first click brings the arrow and ovals that highlight Gibson’s unavoidable fixed costs.
The second click brings in the comment that profits increase by $11.
The arrows and ovals to highlight the increase in profits from $70 to $81 are automated.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 22
Suppose that the Gibson & Quaid Divisions use the same supplier for a particular production input. If the Gibson Division is dropped, the decrease in purchases from this supplier means that Quaid will no longer receive volume discounts on this input. This will increase the costs of production for Quaid by $14,000 per year. In this scenario, should Starz still eliminate the Gibson Division?
Q3: Keep or Drop Decisions
Profits decrease by $3 when Gibson is eliminated.
One click brings in the computation for the effect on profit and the comment “profits decrease by 3” is automated on a 1 second delay.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 23
What qualitative issues should Starz consider before finalizing its decision?
What will be the effect on the customers of Gibson if it is eliminated? What is the effect on the company’s reputation?
What will be the effect on the employees of Gibson? Can any of them be reassigned to other divisions?
What will be the effect on the community where Gibson is located if the decision is made to drop Gibson?
What will be the effect on the morale of the employees of the remaining divisions?
Q3: Qualitative Factors in Keep or Drop Decisions
One mouse click is required for each bullet.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 24
Q4: Insource or Outsource (Make or Buy) Decisions
Managers often must determine whether to
make or buy a production input
keep a business activity in house or outsource the activity
The general rule for make or buy decisions is:
choose the alternative with the lowest relevant (incremental cost), subject to qualitative considerations
If the decision will affect other aspects of operations, these costs (or lost revenues) must be included in the analysis.
Outsource if: Cost to Outsource < Cost to Insource
Where: Cost to Relevant Relevant Opportunity
Insource = FC + VC + Cost
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 25
Graham Co. currently of our main product manufactures a part called a gasker used in the manufacture of its main product. Graham makes and uses 60,000 gaskers per year. The production costs are detailed below. An outside supplier has offered to supply Graham 60,000 gaskers per year at $1.55 each. Fixed production costs of $30,000 associated with the gaskers are unavoidable. Should Graham make or buy the gaskers?
Advantage of “make” over “buy” = [$1.55 - $1.50] x 60,000 = $3,000
The production costs per unit for manufacturing a gasker are:
Direct materials $0.65
Direct labor 0.45
Variable manufacturing overhead 0.40
Fixed manufacturing overhead* 0.50
$2.00
*$30,000/60,000 units = $0.50/unit
Relevant?
yes
$1.50
Relevant?
yes
Relevant?
yes
Relevant?
no
Q4: Make or Buy Decisions
The given cost information is automated.
For each cost element, one click begins the “relevant?” and “yes/no” sequences; the “yes/no” responses are on a 1.5 second delay.
A final click brings in the circle, arrow, $1.50 total for the relevant costs and the “advantage of make over buy” calculation.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 26
The quantitative analysis indicates that Graham should continue to make the component. What qualitative issues should Graham consider before finalizing its decision?
Is the quality of the manufactured component superior to the quality of the purchased component?
Will purchasing the component result in more timely availability of the component?
Would a relationship with the potential supplier benefit the company in any way?
Are there any worker productivity issues that affect this decision?
Q4: Qualitative Factors in Make or Buy Decisions
One mouse click is required for each bullet.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 27
Suppose the potential supplier of the gasker offers Graham a discount for a different sub-unit required to manufacture Graham’s main product if Graham purchases 60,000 gaskers annually. This discount is expected to save Graham $15,000 per year. Should Graham consider purchasing the gaskers?
Q3: Make or Buy Decisions
Profits increase by $12,000 when the gasker is purchased instead of manufactured.
Advantage of “make” over “buy”
before considering discount (slide 23) $3,000
Discount 15,000
Advantage of “buy” over “make” $12,000
One click brings in the computation for the effect on profit and the comment “profits increase by $12,000” is automated on a 1 second delay.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 28
Q5: Constrained Resource (Product Emphasis) Decisions
Managers often face constraints such as
production capacity constraints such as machine hours or limits on availability of material inputs
limits on the quantities of outputs that customers demand
The general rule for constrained resource allocation decisions with only one constraint is:
allocate scarce resources to products with the highest contribution margin per unit of the constrained resource,
subject to qualitative considerations.
Managers need to determine which products should first be allocated the scarce resources.
The first primary bullet and its two secondary bullets are automated.
The first mouse click brings in the second primary bullet.
The second mouse click brings in the third primary bullet - its 2 secondary bullets are automated on 1.5 second delays
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 29
Regular Deluxe
Selling price per unit $40 $110
Variable cost per unit 20 44
Contribution margin per unit $20 $ 66
Contribution margin ratio 50% 60%
Required machine hours/unit 0.4 2.0
Urban has only 160,000 machine hours available per year.
0.4R + 2D 160,000 machine hours
Q5: Constrained Resource Decisions (Two Products; One Scarce Resource)
Urban’s Umbrellas makes two types of patio umbrellas, regular and deluxe. Suppose there is unlimited customer demand for each product. The selling prices and variable costs of each product are listed below.
Write Urban’s machine hour constraint as an inequality.
The given info is automated, as is the instruction to write the machine hour constraint as an inequality.
1. The first click brings in the inequality for the machine hour constraint.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 30
If D=0, this constraint becomes 0.4R 160,000 machine hours, or R 400,000 units
Suppose that Urban decides to make all Regular umbrellas. What is the total contribution margin? Recall that the CM/unit for R is $20.
The machine hour constraint is: 0.4R + 2D 160,000 machine hours
Total contribution margin = $20*400,000 = $8 million
Suppose that Urban decides to make all Deluxe umbrellas. What is the total contribution margin? Recall that the CM/unit for D is $66.
If R=0, this constraint becomes 2D 160,000 machine hours, or D 80,000 units
Total contribution margin = $66*80,000 = $5.28 million
Q5: Constrained Resource Decisions (Two Products; One Scarce Resource)
The machine hour constraint is automated.
The first click brings in the calculation of the number of units of R that can be made and of the total contribution margin.
The second click brings in the instruction to calculate total CM if Urban makes all Ds.
The third click bring in the calculation of the number of units of D that can be made and of the total contribution margin.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 31
In a one constraint problem, a combination of Rs and Ds will yield a contribution margin between $5.28 and $8 million. Therefore, Urban will only make one product, and clearly R is the best choice.
make all Ds; get $5.28 million
make all Rs; get $8 million
If the choice is between all Ds or all Rs, then clearly making all Rs is better. But how do we know that some combination of Rs and Ds won’t yield an even higher contribution margin?
Q5: Constrained Resource Decisions (Two Products; One Scarce Resource)
The shadowed text box is automated.
The first click brings in the rest of the elements in an automated sequence.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 32
In Urban’s case, the sole scarce resource was machine hours, so Urban should make only the product with the highest contribution margin per machine hour.
The general rule for constrained resource decisions with one scarce resource is to first make only the product with the highest contribution margin per unit of the constrained resource.
R: CM/mach hr = $20/0.4mach hrs = $50/mach hr
D: CM/mach hr = $66/2mach hrs = $33/mach hr
Notice that the total contribution margin from making all Rs is $50/mach hr x 160,000 machine hours to be used producing Rs = $8 million.
Q5: Constrained Resource Decisions (Two Products; One Scarce Resource)
The first shadowed text box is automated.
The first mouse click brings in the “In Urban’s case. . .” element.
The second mouse click brings in the computations for the CM per machine hour for each product.
The last shadowed text box is automated.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 33
Usually managers face more than one constraint.
an algebraic expression of the company’s goal, known as the objective function
a list of the constraints written as inequalities
Multiple constraints are easiest to analyze using a quantitative analysis technique known as linear programming.
Q5: Constrained Resource Decisions (Multiple Scarce Resources)
A problem formulated as a linear programming problem contains
for example “maximize total contribution margin” or “minimize total costs”
The first primary bullet is automated.
The first mouse click brings in the second primary bullet.
The second mouse click brings in the third primary bullet - its 2 secondary bullets are automated on 2.5 second delays
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 34
Suppose Urban also need 2 and 6 hours of direct labor per unit of R and D, respectively. There are only 120,000 direct labor hours available per year. Formulate this as a linear programming problem.
Max 20R + 66D
R,D
0.4R+2D 160,000
2R+6D 120,000
R 0
D 0
subject to:
objective function
R, D are the choice variables
constraints
mach hr constraint
DL hr constraint
nonnegativity constraints
(can’t make a negative
amount of R or D)
Q5: Constrained Resource Decisions (Two Products; Two Scarce Resources)
The first click starts a sequence to display the formulation of the linear program.
The second click begins a sequence that circles & labels the objective function, then the choice variables, and finally the constraints.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 35
20,000
Draw a graph showing the possible production plans for Urban.
80,000
400,000
60,000
R
D
Every R, D ordered pair is a production plan.
But which ones are feasible, given the constraints?
To determine this, graph the constraints as inequalities.
0.4R+2D 160,000
mach hr constraint
When D=0, R=400,000
When R=0, D=80,000
2R+6D 120,000
DL hr constraint
When D=0, R=60,000
When R=0, D=20,000
Q5: Constrained Resource Decisions (Two Products; Two Scarce Resources)
The first click begins a sequence about R,D ordered pairs.
The second click begins a sequence to graph the machine hour constraint.
The third click begins a sequence to graph the DL hour constraint.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 36
20,000
80,000
400,000
60,000
R
D
There are not enough machine hours or enough direct labor hours to produce this production plan.
There are enough machine hours, but not enough direct labor hours, to produce this production plan.
This production plan is feasible; there are enough machine hours and enough direct labor hours for this plan.
The feasible set is the area where all the production constraints are satisfied.
Q5: Constrained Resource Decisions (Two Products; Two Scarce Resources)
The first click brings in the comment and arrow about the production plan without sufficient mach hrs or DL hrs.
The second click brings in the comment and arrow about the production plan with enough machine hours, but not enough DL hrs.
The third click brings in the comment and the arrow about the feasible production plan.
The fourth click brings in the definition of the feasible set.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 37
20,000
80,000
400,000
60,000
R
D
The graph helped us realize an important aspect of this problem – we thought there were 2 constrained resources but in fact there is only one.
For every feasible production plan, Urban will never run out of machine hours.
The machine hour constraint is non-binding, or slack, but the direct labor hour constraint is binding.
We are back to a one-scarce-resource problem.
Q5: Constrained Resource Decisions (Two Products; Two Scarce Resources)
This slide is entirely automated.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 38
20,000
80,000
400,000
60,000
R
D
Here direct labor hours is the sole scarce resource.
Urban should make all deluxe umbrellas.
We can use the general rule for one-constraint problems.
R: CM/DL hr = $20/2DL hrs = $10/DL hr
D: CM/DL hr = $66/6DL hrs = $11/DL hr
Optimal plan is R=0, D=20,000. Total contribution margin = $66 x 20,000 = $1,320,000
Q5: Constrained Resource Decisions (Two Products; One Scarce Resource)
The first 2 text elements are automated.
The first click brings in the computation of the CM per DL hour for each product.
The rest of the slide is automated.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 39
Max 20R + 66D
R,D
0.4R+2D 160,000
2R+6D 600,000
R 0
D 0
subject to:
mach hr constraint
DL hr constraint
Q5: Constrained Resource Decisions (Two Products; Two Scarce Resources)
Suppose Urban has been able to train a new workforce and now there are 600,000 direct labor hours available per year. Formulate this as a linear programming problem, graph it, and find the feasible set.
The formulation of the problem is the same as before; the only change is that the right hand side (RHS) of the DL hour constraint is larger.
The first click starts a sequence to display the formulation of the linear program and all the rest of the elements in the slide.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 40
100,000
80,000
400,000
300,000
R
D
The machine hour constraint is the same as before.
0.4R+2D 160,000
mach hr constraint
2R+6D 600,000
DL hr constraint
When D=0, R=300,000
When R=0, D=100,000
Q5: Constrained Resource Decisions (Two Products; Two Scarce Resources)
The sequence to draw the machine hour constraint is automated.
The first click starts the sequence to draw the direct labor hour constraint.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 41
100,000
80,000
400,000
300,000
R
D
Q5: Constrained Resource Decisions (Two Products; Two Scarce Resources)
There are not enough machine hours or enough direct labor hours for this production plan.
This production plan is feasible; there are enough machine hours and enough direct labor hours for this plan.
The feasible set is the area where all the production constraints are satisfied.
There are enough direct labor hours, but not enough machine hours, for this production plan.
There are enough machine hours, but not enough direct labor hours, for this production plan.
The first (green) infeasible production plan is an automated sequence.
The first click starts the sequence for the brown production plan.
The second click starts the sequence for the purple production plan.
The third click starts the sequence for the black production plan.
The rest of the slide is automated.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 42
100,000
80,000
400,000
300,000
R
D
Q5: Constrained Resource Decisions (Two Products; Two Scarce Resources)
How do we know which of the feasible plans is optimal?
We can’t use the general rule for one-constraint problems.
We can graph the total contribution margin line, because its slope will help us determine the optimal production plan.
The objective “maximize total contribution margin” means that we choose a production plan so that the contribution margin is a large as possible, without leaving the feasible set. If the slope of the total contribution margin line is lower (in absolute value terms) than the slope of the machine hour constraint, then. . .
. . . this would be the optimal production plan.
The first text element is automated.
1. The first click brings in the “we can graph…” text element and the “if the slope…” element is automated.
2. The second click starts the sequence to show what the optimal production plan would be if the total CM line had this slope.
The rest of the slide is automated.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 43
100,000
80,000
400,000
300,000
R
D
Q5: Constrained Resource Decisions (Two Products; Two Scarce Resources)
What if the slope of the total contribution margin line is higher (in absolute value terms) than the slope of the direct labor hour constraint?
If the total CM line had this steep slope, . .
. . then this would be the optimal production plan.
The first text element is automated.
The first click brings in the “if the total CM line has this steep slope…”
The second click starts the sequence to show what the optimal production plan would be if the total CM line had this slope.
The rest of the slide is automated.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 44
100,000
80,000
400,000
300,000
R
D
Q5: Constrained Resource Decisions (Two Products; Two Scarce Resources)
What if the slope of the total contribution margin line is between the slopes of the two constraints?
If the total CM line had this slope, . .
. . then this would be the optimal production plan.
The first text element is automated.
The first click brings in the “if the total CM line has this steep slope…”
The second click starts the sequence to show what the optimal production plan would be if the total CM line had this slope.
The rest of the slide is automated.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 45
100,000
80,000
400,000
300,000
R
D
Q5: Constrained Resource Decisions (Two Products; Two Scarce Resources)
The last 3 slides showed that the optimal production plan is always at a corner of the feasible set. This gives us an easy way to solve 2 product, 2 or more scarce resource problems.
R=0, D=80,000
The total contribution margin here is
0 x $20 + 80,000 x $66 = $5,280,000.
R=300,000, D=0
The total contribution margin here is
300,000 x $20 + 0 x $66 = $6,000,000.
R=?, D=?
Find the intersection of the 2 constraints.
The first text element is automated.
The first click brings in the “R=0, D=80,000” text, arrow and circle.
The second click brings in the “R=300,000 D=0” text, arrow and circle.
The third click brings in the “R=?, D=?” text, arrow and circle.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 46
100,000
80,000
400,000
300,000
R
D
Q5: Constrained Resource Decisions (Two Products; Two Scarce Resources)
To find the intersection of the 2 constraints, use substitution or subtract one constraint from the other.
Total CM = $5,280,000.
Total CM = $6,000,000.
0.4R+2D = 160,000
2R+6D = 600,000
2R+10D = 800,000
2R+6D = 600,000
multiply each side by 5
0R+4D = 200,000
D = 50,000
2R+6(50,000) = 600,000
2R = 300,000
R = 150,000
subtract
Total CM = $20 x 150,000 + $66 x 50,000 = $6,300,000.
The first text element is automated.
The first click brings in the sequence to show that D=50,000 for the intersection of the 2 constraints.
The second click brings in the sequence to show that R=150,000 for the intersection of the 2 constraints.
The third click brings in the computation for the total CM for the intersection of the 2 constraints and the arrow and the circle.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 47
100,000
80,000
400,000
300,000
R
D
Q5: Constrained Resource Decisions (Two Products; Two Scarce Resources)
By checking the total contribution margin at each corner of the feasible set (ignoring the origin), we can see that the optimal production plan is R=150,000, D=50,000.
Total CM = $5,280,000.
Total CM = $6,000,000.
Total CM = $6,300,000.
150,000
50,000
Knowing how to graph and solve 2 product, 2 scarce resource problems is good for understanding the nature of a linear programming problem (but difficult in more complex problems).
This slide is entirely automated.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 48
The quantitative analysis indicates that Urban should produce 150,000 regular umbrellas and 50,000 deluxe umbrellas. What qualitative issues should Urban consider before finalizing its decision?
The assumption that customer demand is unlimited is unlikely; can this be investigated further?
Are there any long-term strategic implications of minimizing production of the deluxe umbrellas?
What would be the effects of attempting to relax the machine hour or DL hour constraints?
Are there any worker productivity issues that affect this decision?
Q5: Qualitative Factors in Scarce Resource Allocation Decisions
One mouse click is required for each bullet.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 49
Problems with multiple products, one scarce resource, and one constraint on customer demand for each product are easy to solve.
Q5: Constrained Resource Decisions (Multiple Products; Multiple Constraints)
The general rule is to make the product with the highest contribution margin per unit of the scarce resource:
until its customer demand is satisfied
then move to the product with the next highest contribution margin per unit of the scarce resource, etc.
Problems with multiple products and multiple scarce resources are too cumbersome to solve by hand – Excel solver is a useful tool here.
The first text element is automated.
The first click brings in the second primary bullet.
The second click brings in the third primary bullet.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 50
Regular Deluxe
Selling price per unit $40 $110
Variable cost per unit 20 44
Contribution margin per unit $20 $ 66
Required machine hours/unit 0.4 2.0
CM/machine hour $50 $33
Urban should first concentrate on making Rs. He can make enough to satisfy customer demand for Rs: 300,000 Rs x 0.4 mach hr/R = 120,000 mach hrs.
Q5: Constrained Resource Decisions (Two Products; One Scarce Resource)
Urban’s Umbrellas makes two types of patio umbrellas, regular and deluxe. Suppose customer demand for regular umbrellas is 300,000 units and for deluxe umbrellas customer demand is limited to 60,000. Urban has only 160,000 machine hours available per year. What is his optimal production plan? How much would he pay (above his normal costs) for an extra machine hour?
The given info is automated, as is the instruction to write the machine hour constraint as an inequality.
The first click brings in the conclusion that Urban should first concentrate on Rs.
The right arrow is automated.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 51
Regular Deluxe
Selling price per unit $40 $110
Variable cost per unit 20 44
Contribution margin per unit $20 $ 66
Required machine hours/unit 0.4 2.0
CM/machine hour $50 $33
The 40,000 remaining hours will make 20,000 Ds.
Q5: Constrained Resource Decisions (Two Products; One Scarce Resource)
Here Urban will be producing Ds when he runs out of machine hours so he’d be willing to pay up to $33 for an additional machine hour.
If customer demand for Rs exceeded 400,000 units, Urban would be willing to pay up to an additional $50 for a machine hour.
The optimal plan is 300,000 Rs and 20,000 Ds. The CM/mach hr shows how much Urban would be willing to pay, above his normal costs, for an additional machine hour.
If customer demand for Rs and Ds could be satisfied with the 160,000 available machine hours, then Urban would not be willing to pay anything to acquire an additional machine hour.
The given info is automated, as is the text to the right of the given info.
The first click brings in the text element discussing the optimal plan, which is hidden after the next mouse click.
The second click brings in the text element that says he’d pay $33/hr for one more machine hr, which is hidden after the next mouse click.
The third click brings in the text element that describes when he’d pay $50/hr for one more machine hr, which is hidden after the next mouse click.
The fourth click brings in the text element that describes when he’d pay nothing for one more machine hr.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 52
Q5: Constrained Resource Decisions Using Excel Solver
To obtain the solver dialog box, choose “Solver” from the Tools pull-down menu.
The “target cell” will contain the maximized value for the objective (or “target”) function.
Choose “max” for the types of problems in this chapter.
Choose one cell for each choice variable (product). It’s helpful to “name” these cells.
Add constraint formulas by clicking “add”.
Click “solve” to obtain the next dialog box.
The first click brings in the black “target cell” label.
The second click brings in the red “choose max” label.
The third click brings in the purple “choose one cell” label.
The fourth click brings in the blue “add constraint formulas” label.
The shadowed text box is automated on a 1.5 second delay.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 53
Q5: Constrained Resource Decisions Using Excel Solver
=20*Regular + 66*Deluxe
Cell B2 was named “Regular” and cell C2 was named Deluxe.
=0.4*Regular+2*Deluxe
=2*Regular+6*Deluxe
=Regular (cell B2)
=Deluxe (cell C2)
Then click “solve” and choose all 3 reports.
The first click brings in the text element that describes the names of the 2 changing cells.
The second click brings in the formula for the target cell.
The third click brings in the 2 formulas for the scarce resource constraints.
The fourth click brings in the 2 formulas for the nonnegativity constraints.
The fifth click brings in the shadowed box.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 54
Q5: Excel Solver Answer Report
Refer to the problem on Slide #50.
The total contribution margin for the optimal plan was $6.3 million.
The optimal production plan was 150,000 Rs and 50,000 Ds.
The machine and DL hour constraints are binding – the plan uses all available machine and DL hours.
The nonnegativity constraints for R and D are not binding; the slack is 50,000 and 150,000 units respectively.
The first click brings in the total contribution margin label.
The second click brings in the label for the optimal plan.
The third click brings in the label for the binding resource constraints.
The fourth click brings in the label for the slack nonnegativity constraints.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 55
Q5: Excel Solver Sensitivity Report
Refer to the problem on Slide #50.
The CM per unit for Regular can drop to $13.20 or increase to $22 (all else equal) before the optimal plan will change. The CM per unit for Deluxe can drop to $60 or increase to $100 (all else equal) before the optimal plan will change.
This shows how much the slope of the total CM line can change before the optimal production plan will change.
The first click brings in the label for the allowable change in CM.
The second click brings the interpretation of the allowable change in contribution margin (at the bottom).
The next click advances to the next slide where the discussion of the sensitivity report is continued.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 56
Q5: Excel Solver Sensitivity Report
Refer to the problem on Slide #50.
The available DL hours could decrease to 480,000 or increase to 800,000 (all else equal) before the shadow price for DL would change. The available machine hours could decrease to 120,000 or increase to 200,000 (all else equal) before the shadow price for machine hours would change.
This shows how much the RHS of each constraint can change before the shadow price will change.
The first click brings in the label for the allowable change in RHS of constraints.
The second click brings the interpretation of the allowable change in RHS of constraints.
The next click advances to the next slide where the discussion of the sensitivity report is continued.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 57
Q5: Excel Solver Sensitivity Report
Refer to the problem on Slide #50.
Urban would be willing to pay up to $8.50 to obtain one more DL hour and up to $7.50 to obtain one more machine hour.
The shadow price shows how much a one unit increase in the RHS of a constraint will improve the total contribution margin.
The first click brings in the label for the shadow prices.
The second click brings the interpretation of the shadow prices.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 58
Q7: Impacts to Quality of Nonroutine Operating Decisions
The quality of the information used in nonroutine operating decisions must be assessed.
There may be more information quality issues (and more uncertainty) in nonroutine decisions because of the irregularity of the decisions.
Business risk (changes in economic condition, consumer demand, regulation, competitors, etc.)
Three aspects of the quality of information available can affect decision quality.
Information timeliness
Assumptions in the quantitative and qualitative analyses
The first primary bullet and its secondary bullet are automated.
The first click brings in the second primary bullet, and its three secondary bullets are automated.
© John Wiley & Sons, 2011
Chapter 4: Relevant Costs for Nonroutine Operating Decisions
Eldenburg & Wolcott’s Cost Management, 2e
Slide # 59
Short term decision must align to company’s overall strategic plans
Must watch for decision maker bias
Predisposition for specific outcome
Preference for one type of analysis without considering other options
Opportunity costs are often overlooked
Performing sensitivity analysis can help assess and minimize business risk
Established control system incentives (performance bonuses, etc.) can encourage sub-obtimal decision making
Q7: Impacts to Quality of Nonroutine Operating Decisions
The first primary bullet is automated.
The first click brings in the second primary bullet
The second click brings in the third primary bullet, and its three secondary bullets are automated.
Direct materials$6.20
Direct labor8.00
Variable mfg. overhead5.80
Fixed mfg. overhead3.50
Shipping/handling2.50
Fixed administrative costs0.88
Fixed selling costs0.36
$27.24
RobotBits
| Direct materials | $6.20 |
| Direct labor | 8.00 |
| Variable mfg. overhead | 5.80 |
| Fixed mfg. overhead | 3.50 |
| Shipping/handling | 2.50 |
| Fixed administrative costs | 0.88 |
| Fixed selling costs | 0.36 |
| $27.24 |
Sheet2
Sheet3
RobotBits
| Direct materials | $6.20 |
| Direct labor | 8.00 |
| Variable mfg. overhead | 5.80 |
| Fixed mfg. overhead | 3.50 |
| Shipping/handling | 2.50 |
| Fixed administrative costs | 0.88 |
| Fixed selling costs | 0.36 |
| $27.24 |
Sheet2
Sheet3
GibsonQuaidRussellTotal
Revenues$390 $433 $837 $1,660
Variable costs2473354721,054
Contribution margin14398365606
Traceable fixed costs166114175455
Division operating income($23)($16)$190 151
81
$70
Avoidable$154 $96 $139
Unavoidable121836
$166 $114 $175
Unallocated fixed costs
Operating income
Breakdown of traceable fixed costs:
RobotBits
| Direct materials | $6.20 |
| Direct labor | 8.00 |
| Variable mfg. overhead | 5.80 |
| Fixed mfg. overhead | 3.50 |
| Shipping/handling | 2.50 |
| Fixed administrative costs | 0.88 |
| Fixed selling costs | 0.36 |
| $27.24 |
keep drop
| Gibson | Quaid | Russell | Total | |
| Revenues | $390 | $433 | $837 | $1,660 |
| Variable costs | 247 | 335 | 472 | 1,054 |
| Contribution margin | 143 | 98 | 365 | 606 |
| Traceable fixed costs | 166 | 114 | 175 | 455 |
| Division operating income | ($23) | ($16) | $190 | 151 |
| Unallocated fixed costs | 81 | |||
| Operating income | $70 | |||
| Breakdown of traceable fixed costs: | ||||
| Avoidable | $154 | $96 | $139 | |
| Unavoidable | 12 | 18 | 36 | |
| $166 | $114 | $175 |
Sheet3
GibsonQuaid
Contribution margin$143 $98
Avoidable fixed costs15496
Effect on profit if keep($11)$2
RobotBits
| Direct materials | $6.20 |
| Direct labor | 8.00 |
| Variable mfg. overhead | 5.80 |
| Fixed mfg. overhead | 3.50 |
| Shipping/handling | 2.50 |
| Fixed administrative costs | 0.88 |
| Fixed selling costs | 0.36 |
| $27.24 |
keep drop
| Gibson | Quaid | Russell | Total | |
| Revenues | $390 | $433 | $837 | $1,660 |
| Variable costs | 247 | 335 | 472 | 1,054 |
| Contribution margin | 143 | 98 | 365 | 606 |
| Traceable fixed costs | 166 | 114 | 175 | 455 |
| Division operating income | ($23) | ($16) | $190 | 151 |
| Unallocated fixed costs | 81 | |||
| Operating income | $70 | |||
| Breakdown of traceable fixed costs: | ||||
| Avoidable | $154 | $96 | $139 | |
| Unavoidable | 12 | 18 | 36 | |
| $166 | $114 | $175 |
Sheet3
RobotBits
| Direct materials | $6.20 |
| Direct labor | 8.00 |
| Variable mfg. overhead | 5.80 |
| Fixed mfg. overhead | 3.50 |
| Shipping/handling | 2.50 |
| Fixed administrative costs | 0.88 |
| Fixed selling costs | 0.36 |
| $27.24 |
keep drop
| Gibson | Quaid | Russell | Total | |
| Revenues | $390 | $433 | $837 | $1,660 |
| Variable costs | 247 | 335 | 472 | 1,054 |
| Contribution margin | 143 | 98 | 365 | 606 |
| Traceable fixed costs | 166 | 114 | 175 | 455 |
| Division operating income | ($23) | ($16) | $190 | 151 |
| Unallocated fixed costs | 81 | |||
| Operating income | $70 | |||
| Breakdown of traceable fixed costs: | ||||
| Avoidable | $154 | $96 | $139 | |
| Unavoidable | 12 | 18 | 36 | |
| $166 | $114 | $175 | ||
| Gibson | Quaid | |||
| Contribution margin | $143 | $98 | ||
| Avoidable fixed costs | 154 | 96 | ||
| Effect on profit if keep | ($11) | $2 |
Sheet3
GibsonQuaidRussellTotal
Revenues$390 $433 $837 $1,270
Variable costs247335472807
Contribution margin14398365$463
Traceable fixed costs166114175289
Division operating income($23)($16)$190 $174
81
Gibson's unavoidable fixed costs12
$81
Unallocated fixed costs
Operating income
RobotBits
| Direct materials | $6.20 |
| Direct labor | 8.00 |
| Variable mfg. overhead | 5.80 |
| Fixed mfg. overhead | 3.50 |
| Shipping/handling | 2.50 |
| Fixed administrative costs | 0.88 |
| Fixed selling costs | 0.36 |
| $27.24 |
keep drop
| Gibson | Quaid | Russell | Total | |
| Revenues | $390 | $433 | $837 | $1,660 |
| Variable costs | 247 | 335 | 472 | 1,054 |
| Contribution margin | 143 | 98 | 365 | 606 |
| Traceable fixed costs | 166 | 114 | 175 | 455 |
| Division operating income | ($23) | ($16) | $190 | 151 |
| Unallocated fixed costs | 81 | |||
| Operating income | $70 | |||
| Breakdown of traceable fixed costs: | ||||
| Avoidable | $154 | $96 | $139 | |
| Unavoidable | 12 | 18 | 36 | |
| $166 | $114 | $175 |
Sheet3
RobotBits
| Direct materials | $6.20 |
| Direct labor | 8.00 |
| Variable mfg. overhead | 5.80 |
| Fixed mfg. overhead | 3.50 |
| Shipping/handling | 2.50 |
| Fixed administrative costs | 0.88 |
| Fixed selling costs | 0.36 |
| $27.24 |
keep drop
| Gibson | Quaid | Russell | Total | |
| Revenues | $390 | $433 | $837 | $1,660 |
| Variable costs | 247 | 335 | 472 | 1,054 |
| Contribution margin | 143 | 98 | 365 | 606 |
| Traceable fixed costs | 166 | 114 | 175 | 455 |
| Division operating income | ($23) | ($16) | $190 | 151 |
| Unallocated fixed costs | 81 | |||
| Operating income | $70 | |||
| Breakdown of traceable fixed costs: | ||||
| Avoidable | $154 | $96 | $139 | |
| Unavoidable | 12 | 18 | 36 | |
| $166 | $114 | $175 | ||
| Gibson | Quaid | |||
| Contribution margin | $143 | $98 | ||
| Avoidable fixed costs | 154 | 96 | ||
| Effect on profit if keep | ($11) | $2 | ||
| Gibson | Quaid | Russell | Total | |
| Revenues | $390 | $433 | $837 | $1,270 |
| Variable costs | 247 | 335 | 472 | 807 |
| Contribution margin | 143 | 98 | 365 | $463 |
| Traceable fixed costs | 166 | 114 | 175 | 289 |
| Division operating income | ($23) | ($16) | $190 | $174 |
| Unallocated fixed costs | 81 | |||
| Gibson's unavoidable fixed costs | 12 | |||
| Operating income | $81 |
Sheet3
$11
Opportunity cost of eliminating Gibson
(14)
($3)
Revised effect on profit if drop Gibson
Effect on profit if drop Gibson before considering
impact on Quaid's production costs
RobotBits
| Direct materials | $6.20 |
| Direct labor | 8.00 |
| Variable mfg. overhead | 5.80 |
| Fixed mfg. overhead | 3.50 |
| Shipping/handling | 2.50 |
| Fixed administrative costs | 0.88 |
| Fixed selling costs | 0.36 |
| $27.24 |
keep drop
| Gibson | Quaid | Russell | Total | |||
| Revenues | $390 | $433 | $837 | $1,660 | ||
| Variable costs | 247 | 335 | 472 | 1,054 | ||
| Contribution margin | 143 | 98 | 365 | 606 | ||
| Traceable fixed costs | 166 | 114 | 175 | 455 | ||
| Division operating income | ($23) | ($16) | $190 | 151 | ||
| Unallocated fixed costs | 81 | |||||
| Operating income | $70 | |||||
| Breakdown of traceable fixed costs: | ||||||
| Avoidable | $154 | $96 | $139 | |||
| Unavoidable | 12 | 18 | 36 | |||
| $166 | $114 | $175 | ||||
| Gibson | Quaid | |||||
| Contribution margin | $143 | $98 | ||||
| Avoidable fixed costs | 154 | 96 | ||||
| Effect on profit if keep | ($11) | $2 | ||||
| Gibson | Quaid | Russell | Total | |||
| Revenues | $390 | $433 | $837 | $1,270 | ||
| Variable costs | 247 | 335 | 472 | 807 | ||
| Contribution margin | 143 | 98 | 365 | $463 | ||
| Traceable fixed costs | 166 | 114 | 175 | 289 | ||
| Division operating income | ($23) | ($16) | $190 | $174 | ||
| Unallocated fixed costs | 81 | |||||
| Gibson's unavoidable fixed costs | 12 | |||||
| Operating income | $81 | |||||
| Effect on profit if drop Gibson before considering impact on Quaid's production costs | $11 | |||||
| Opportunity cost of eliminating Gibson | (14) | |||||
| Revised effect on profit if drop Gibson | ($3) |
Sheet3
Microsoft Excel 9.0 Answer Report
Target Cell (Max)
CellName
Original
Value
Final Value
$B$3Regular
06,300,000
Adjustable Cells
CellName
Original
Value
Final Value
$B$2Regular
0150,000
$C$2Deluxe
050000
Constraints
CellName
Cell
Value
FormulaStatusSlack
$B$9DL hr
600,000$B$9<=$C$9Binding0
$B$8mach hr
160,000$B$8<=$C$8Binding0
$B$11D>0
50,000$B$11>=$C$11
Not
Binding
50,000
$B$10R>0
150,000$B$10>=$C$10
Not
Binding
150,000
Answer Report 2
| Microsoft Excel 9.0 Answer Report | |||||||
| Worksheet: [Ch4 part 2 page 3.xls]input | |||||||
| Report Created: 5/22/2003 10:02:23 AM | |||||||
| Target Cell (Max) | |||||||
| Cell | Name | Original Value | Final Value | ||||
| $B$7 | A | 0 | 1320000 | This is the value of the objective function at the optimal production plan | |||
| Adjustable Cells | |||||||
| Cell | Name | Original Value | Final Value | ||||
| $B$6 | A | 0 | 0 | This is the optimal production plan | |||
| $C$6 | B | 0 | 20000 | ||||
| Constraints | |||||||
| Cell | Name | Cell Value | Formula | Status | Slack | ||
| $B$12 | LHS | 40000 | $B$12<=$C$12 | Not Binding | 120000 | This says the machine hour constraint is slack by 120,000 M Hrs* | |
| $B$13 | LHS | 120000 | $B$13<=$C$13 | Binding | 0 | This says the direct labor hour constraint is binding (i.e. all DL hrs used up) | |
| $B$14 | LHS | 0 | $B$14>=$C$14 | Binding | 0 | This says the nonnegativity constraint for A is binding (i.e. A=0) | |
| $B$15 | LHS | 20000 | $B$15>=$C$15 | Not Binding | 20000 | This says the nonnegativity constraint for B is slack by 20,000 | |
| *This makes sense, because making 20,000 Bs uses 40,000 M Hrs, leaving 120,000 machine hours unused |
Sensitivity Report 2
| Microsoft Excel 9.0 Sensitivity Report | |||||||||||||
| Worksheet: [Ch4 part 2 page 3.xls]input | |||||||||||||
| Report Created: 5/22/2003 10:02:23 AM | |||||||||||||
| Adjustable Cells | |||||||||||||
| Final | Reduced | Objective | Allowable | Allowable | |||||||||
| Cell | Name | Value | Cost | Coefficient | Increase | Decrease | (CM of A between 0 and 22 gives same sales mix) | ||||||
| $B$6 | A | 0 | 0 | 20 | 2 | 1E+30 | This is the amount the CM per unit of A can change before the optimal sales mix would change | ||||||
| $C$6 | B | 20000 | 0 | 66 | 1E+30 | 6 | This is the amount the CM per unit of B can change before the optimal sales mix would change | ||||||
| (CM of B between 60 and any positive number gives same sales mix) | |||||||||||||
| Constraints | |||||||||||||
| Final | Shadow | Constraint | Allowable | Allowable | |||||||||
| Cell | Name | Value | Price | R.H. Side | Increase | Decrease | |||||||
| $B$12 | LHS | 40000 | 0 | 160000 | 1E+30 | 120000 | This shows how much you'd be willing to pay to loosen the RHS of the Machine Hour constraint by one M Hr | ||||||
| $B$13 | LHS | 120000 | 11 | 120000 | 360000 | 120000 | This shows how much you'd be willing to pay to loosen the RHS of the DL Hour constraint by one DL Hr | ||||||
| $B$14 | LHS | 0 | -2 | 0 | 60000 | 450000 | |||||||
| $B$15 | LHS | 20000 | 0 | 0 | 20000 | 1E+30 |
Answer Report 3
| Microsoft Excel 9.0 Answer Report | ||||||
| Target Cell (Max) | ||||||
| Cell | Name | Original Value | Final Value | |||
| $B$3 | Regular | 0 | 6,300,000 | |||
| Adjustable Cells | ||||||
| Cell | Name | Original Value | Final Value | |||
| $B$2 | Regular | 0 | 150,000 | |||
| $C$2 | Deluxe | 0 | 50000 | |||
| Constraints | ||||||
| Cell | Name | Cell Value | Formula | Status | Slack | |
| $B$11 | LHS | 50,000 | $B$11>=$C$11 | Not Binding | 50,000 | |
| $B$8 | LHS | 160,000 | $B$8<=$C$8 | Binding | 0 | |
| $B$10 | LHS | 150,000 | $B$10>=$C$10 | Not Binding | 150,000 | |
| $B$9 | LHS | 600,000 | $B$9<=$C$9 | Binding | 0 |
Sensitivity Report 3
| Microsoft Excel 9.0 Sensitivity Report | |||||||
| Worksheet: [Ch4 part 2 page 50.xls]input | |||||||
| Report Created: 7/22/2004 10:18:05 AM | |||||||
| Adjustable Cells | |||||||
| Final | Reduced | Objective | Allowable | Allowable | |||
| Cell | Name | Value | Cost | Coefficient | Increase | Decrease | |
| $B$2 | Regular | 150,000 | 0 | 20 | 2 | 6.8 | |
| $C$2 | Deluxe | 50000 | 0 | 66 | 34 | 6 | |
| Constraints | |||||||
| Final | Shadow | Constraint | Allowable | Allowable | |||
| Cell | Name | Value | Price | R.H. Side | Increase | Decrease | |
| $B$11 | LHS | 50,000 | 0 | 0 | 50000 | 1E+30 | |
| $B$8 | LHS | 160,000 | 8 | 160000 | 40000 | 40000 | |
| $B$10 | LHS | 150,000 | 0 | 0 | 150000 | 1E+30 | |
| $B$9 | LHS | 600,000 | 9 | 600000 | 200000 | 120000 |
Limits Report 3
| Microsoft Excel 9.0 Limits Report | |||||||||
| Worksheet: [Ch4 part 2 page 50.xls]input | |||||||||
| Report Created: 7/22/2004 10:18:05 AM | |||||||||
| Target | |||||||||
| Cell | Name | Value | |||||||
| $B$3 | Regular | 6,300,000 | |||||||
| Adjustable | Lower | Target | Upper | Target | |||||
| Cell | Name | Value | Limit | Result | Limit | Result | |||
| $B$2 | Regular | 150,000 | 0 | 3,300,000 | 150,000 | 6,299,999 | |||
| $C$2 | Deluxe | 50000 | 0 | 3000000 | 50000.0012631063 | 6300000.08336501 |
Answer Report 1
| Microsoft Excel 9.0 Answer Report | ||||||
| Target Cell (Max) | ||||||
| Cell | Name | Original Value | Final Value | |||
| $B$3 | Regular | 0 | 6,300,000 | |||
| Adjustable Cells | ||||||
| Cell | Name | Original Value | Final Value | |||
| $B$2 | Regular | 0 | 150,000 | |||
| $C$2 | Deluxe | 0 | 50000 | |||
| Constraints | ||||||
| Cell | Name | Cell Value | Formula | Status | Slack | |
| $B$9 | DL hr | 600,000 | $B$9<=$C$9 | Binding | 0 | |
| $B$8 | mach hr | 160,000 | $B$8<=$C$8 | Binding | 0 | |
| $B$11 | D>0 | 50,000 | $B$11>=$C$11 | Not Binding | 50,000 | |
| $B$10 | R>0 | 150,000 | $B$10>=$C$10 | Not Binding | 150,000 |
Sensitivity Report 1
| Microsoft Excel 9.0 Sensitivity Report | |||||||
| Worksheet: [Ch4 part 2 page 50.xls]input | |||||||
| Report Created: 7/22/2004 10:39:43 AM | |||||||
| Adjustable Cells | |||||||
| Final | Reduced | Objective | Allowable | Allowable | |||
| Cell | Name | Value | Cost | Coefficient | Increase | Decrease | |
| $B$2 | Regular | 150,000 | 0 | 20 | 2 | 6.8 | |
| $C$2 | Deluxe | 50000 | 0 | 66 | 34 | 6 | |
| Constraints | |||||||
| Final | Shadow | Constraint | Allowable | Allowable | |||
| Cell | Name | Value | Price | R.H. Side | Increase | Decrease | |
| $B$9 | LHS | 600,000 | 9 | 600000 | 200000 | 120000 | |
| $B$8 | LHS | 160,000 | 8 | 160000 | 40000 | 40000 | |
| $B$11 | LHS | 50,000 | 0 | 0 | 50000 | 1E+30 | |
| $B$10 | LHS | 150,000 | 0 | 0 | 150000 | 1E+30 |
Limits Report 1
| Microsoft Excel 9.0 Limits Report | |||||||||
| Worksheet: [Ch4 part 2 page 50.xls]input | |||||||||
| Report Created: 7/22/2004 10:39:43 AM | |||||||||
| Target | |||||||||
| Cell | Name | Value | |||||||
| $B$3 | Regular | 6,300,000 | |||||||
| Adjustable | Lower | Target | Upper | Target | |||||
| Cell | Name | Value | Limit | Result | Limit | Result | |||
| $B$2 | Regular | 150,000 | 0 | 3,300,000 | 150,000 | 6,299,999 | |||
| $C$2 | Deluxe | 50000 | 0 | 3000000 | 50000.0012631063 | 6300000.08336501 |
input
| Regular | Deluxe | |||
| 150,000 | 50000 | |||
| 6,300,000 | ||||
| Contraints | ||||
| LHS | RHS | |||
| 160,000 | 160,000 | mach hr constraint | ||
| 600,000 | 600,000 | DL hr constraint | ||
| 150,000 | 0 | R nonnegative | ||
| 50,000 | 0 | D nonnegative |
Microsoft Excel 9.0 Sensitivity Report
Adjustable Cells
FinalReducedObjectiveAllowableAllowable
CellNameValueCostCoefficientIncreaseDecrease
$B$2Regular150,00002026.8
$C$2Deluxe50000066346
Constraints
FinalShadowConstraintAllowableAllowable
CellNameValuePriceR.H. SideIncreaseDecrease
$B$9DL hr600,0009600000200000120000
$B$8mach hr160,00081600004000040000
$B$11D>050,00000500001E+30
$B$10R>0150,000001500001E+30
Answer Report 2
| Microsoft Excel 9.0 Answer Report | |||||||
| Worksheet: [Ch4 part 2 page 3.xls]input | |||||||
| Report Created: 5/22/2003 10:02:23 AM | |||||||
| Target Cell (Max) | |||||||
| Cell | Name | Original Value | Final Value | ||||
| $B$7 | A | 0 | 1320000 | This is the value of the objective function at the optimal production plan | |||
| Adjustable Cells | |||||||
| Cell | Name | Original Value | Final Value | ||||
| $B$6 | A | 0 | 0 | This is the optimal production plan | |||
| $C$6 | B | 0 | 20000 | ||||
| Constraints | |||||||
| Cell | Name | Cell Value | Formula | Status | Slack | ||
| $B$12 | LHS | 40000 | $B$12<=$C$12 | Not Binding | 120000 | This says the machine hour constraint is slack by 120,000 M Hrs* | |
| $B$13 | LHS | 120000 | $B$13<=$C$13 | Binding | 0 | This says the direct labor hour constraint is binding (i.e. all DL hrs used up) | |
| $B$14 | LHS | 0 | $B$14>=$C$14 | Binding | 0 | This says the nonnegativity constraint for A is binding (i.e. A=0) | |
| $B$15 | LHS | 20000 | $B$15>=$C$15 | Not Binding | 20000 | This says the nonnegativity constraint for B is slack by 20,000 | |
| *This makes sense, because making 20,000 Bs uses 40,000 M Hrs, leaving 120,000 machine hours unused |
Sensitivity Report 2
| Microsoft Excel 9.0 Sensitivity Report | |||||||||||||
| Worksheet: [Ch4 part 2 page 3.xls]input | |||||||||||||
| Report Created: 5/22/2003 10:02:23 AM | |||||||||||||
| Adjustable Cells | |||||||||||||
| Final | Reduced | Objective | Allowable | Allowable | |||||||||
| Cell | Name | Value | Cost | Coefficient | Increase | Decrease | (CM of A between 0 and 22 gives same sales mix) | ||||||
| $B$6 | A | 0 | 0 | 20 | 2 | 1E+30 | This is the amount the CM per unit of A can change before the optimal sales mix would change | ||||||
| $C$6 | B | 20000 | 0 | 66 | 1E+30 | 6 | This is the amount the CM per unit of B can change before the optimal sales mix would change | ||||||
| (CM of B between 60 and any positive number gives same sales mix) | |||||||||||||
| Constraints | |||||||||||||
| Final | Shadow | Constraint | Allowable | Allowable | |||||||||
| Cell | Name | Value | Price | R.H. Side | Increase | Decrease | |||||||
| $B$12 | LHS | 40000 | 0 | 160000 | 1E+30 | 120000 | This shows how much you'd be willing to pay to loosen the RHS of the Machine Hour constraint by one M Hr | ||||||
| $B$13 | LHS | 120000 | 11 | 120000 | 360000 | 120000 | This shows how much you'd be willing to pay to loosen the RHS of the DL Hour constraint by one DL Hr | ||||||
| $B$14 | LHS | 0 | -2 | 0 | 60000 | 450000 | |||||||
| $B$15 | LHS | 20000 | 0 | 0 | 20000 | 1E+30 |
Answer Report 3
| Microsoft Excel 9.0 Answer Report | ||||||
| Target Cell (Max) | ||||||
| Cell | Name | Original Value | Final Value | |||
| $B$3 | Regular | 0 | 6,300,000 | |||
| Adjustable Cells | ||||||
| Cell | Name | Original Value | Final Value | |||
| $B$2 | Regular | 0 | 150,000 | |||
| $C$2 | Deluxe | 0 | 50000 | |||
| Constraints | ||||||
| Cell | Name | Cell Value | Formula | Status | Slack | |
| $B$11 | LHS | 50,000 | $B$11>=$C$11 | Not Binding | 50,000 | |
| $B$8 | LHS | 160,000 | $B$8<=$C$8 | Binding | 0 | |
| $B$10 | LHS | 150,000 | $B$10>=$C$10 | Not Binding | 150,000 | |
| $B$9 | LHS | 600,000 | $B$9<=$C$9 | Binding | 0 |
Sensitivity Report 3
| Microsoft Excel 9.0 Sensitivity Report | |||||||
| Worksheet: [Ch4 part 2 page 50.xls]input | |||||||
| Report Created: 7/22/2004 10:18:05 AM | |||||||
| Adjustable Cells | |||||||
| Final | Reduced | Objective | Allowable | Allowable | |||
| Cell | Name | Value | Cost | Coefficient | Increase | Decrease | |
| $B$2 | Regular | 150,000 | 0 | 20 | 2 | 6.8 | |
| $C$2 | Deluxe | 50000 | 0 | 66 | 34 | 6 | |
| Constraints | |||||||
| Final | Shadow | Constraint | Allowable | Allowable | |||
| Cell | Name | Value | Price | R.H. Side | Increase | Decrease | |
| $B$11 | LHS | 50,000 | 0 | 0 | 50000 | 1E+30 | |
| $B$8 | LHS | 160,000 | 8 | 160000 | 40000 | 40000 | |
| $B$10 | LHS | 150,000 | 0 | 0 | 150000 | 1E+30 | |
| $B$9 | LHS | 600,000 | 9 | 600000 | 200000 | 120000 |
Limits Report 3
| Microsoft Excel 9.0 Limits Report | |||||||||
| Worksheet: [Ch4 part 2 page 50.xls]input | |||||||||
| Report Created: 7/22/2004 10:18:05 AM | |||||||||
| Target | |||||||||
| Cell | Name | Value | |||||||
| $B$3 | Regular | 6,300,000 | |||||||
| Adjustable | Lower | Target | Upper | Target | |||||
| Cell | Name | Value | Limit | Result | Limit | Result | |||
| $B$2 | Regular | 150,000 | 0 | 3,300,000 | 150,000 | 6,299,999 | |||
| $C$2 | Deluxe | 50000 | 0 | 3000000 | 50000.0012631063 | 6300000.08336501 |
Answer Report 1
| Microsoft Excel 9.0 Answer Report | ||||||
| Target Cell (Max) | ||||||
| Cell | Name | Original Value | Final Value | |||
| $B$3 | Regular | 0 | 6,300,000 | |||
| Adjustable Cells | ||||||
| Cell | Name | Original Value | Final Value | |||
| $B$2 | Regular | 0 | 150,000 | |||
| $C$2 | Deluxe | 0 | 50000 | |||
| Constraints | ||||||
| Cell | Name | Cell Value | Formula | Status | Slack | |
| $B$9 | LHS | 600,000 | $B$9<=$C$9 | Binding | 0 | |
| $B$8 | LHS | 160,000 | $B$8<=$C$8 | Binding | 0 | |
| $B$11 | LHS | 50,000 | $B$11>=$C$11 | Not Binding | 50,000 | |
| $B$10 | LHS | 150,000 | $B$10>=$C$10 | Not Binding | 150,000 |
Sensitivity Report 1
| Microsoft Excel 9.0 Sensitivity Report | |||||||
| Adjustable Cells | |||||||
| Final | Reduced | Objective | Allowable | Allowable | |||
| Cell | Name | Value | Cost | Coefficient | Increase | Decrease | |
| $B$2 | Regular | 150,000 | 0 | 20 | 2 | 6.8 | |
| $C$2 | Deluxe | 50000 | 0 | 66 | 34 | 6 | |
| Constraints | |||||||
| Final | Shadow | Constraint | Allowable | Allowable | |||
| Cell | Name | Value | Price | R.H. Side | Increase | Decrease | |
| $B$9 | DL hr | 600,000 | 9 | 600000 | 200000 | 120000 | |
| $B$8 | mach hr | 160,000 | 8 | 160000 | 40000 | 40000 | |
| $B$11 | D>0 | 50,000 | 0 | 0 | 50000 | 1E+30 | |
| $B$10 | R>0 | 150,000 | 0 | 0 | 150000 | 1E+30 |
Limits Report 1
| Microsoft Excel 9.0 Limits Report | |||||||||
| Worksheet: [Ch4 part 2 page 50.xls]input | |||||||||
| Report Created: 7/22/2004 10:39:43 AM | |||||||||
| Target | |||||||||
| Cell | Name | Value | |||||||
| $B$3 | Regular | 6,300,000 | |||||||
| Adjustable | Lower | Target | Upper | Target | |||||
| Cell | Name | Value | Limit | Result | Limit | Result | |||
| $B$2 | Regular | 150,000 | 0 | 3,300,000 | 150,000 | 6,299,999 | |||
| $C$2 | Deluxe | 50000 | 0 | 3000000 | 50000.0012631063 | 6300000.08336501 |
input
| Regular | Deluxe | |||
| 150,000 | 50000 | |||
| 6,300,000 | ||||
| Contraints | ||||
| LHS | RHS | |||
| 160,000 | 160,000 | mach hr constraint | ||
| 600,000 | 600,000 | DL hr constraint | ||
| 150,000 | 0 | R nonnegative | ||
| 50,000 | 0 | D nonnegative |
Microsoft Excel 9.0 Sensitivity Report
Adjustable Cells
FinalReducedObjectiveAllowableAllowable
CellNameValueCostCoefficientIncreaseDecrease
$B$2Regular150,00002026.8
$C$2Deluxe50000066346
Constraints
FinalShadowConstraintAllowableAllowable
CellNameValuePriceR.H. SideIncreaseDecrease
$B$9DL hr600,0008.50600000200000120000
$B$8mach hr160,0007.501600004000040000
$B$11D>050,0000.000500001E+30
$B$10R>0150,0000.0001500001E+30
Answer Report 2
| Microsoft Excel 9.0 Answer Report | |||||||
| Worksheet: [Ch4 part 2 page 3.xls]input | |||||||
| Report Created: 5/22/2003 10:02:23 AM | |||||||
| Target Cell (Max) | |||||||
| Cell | Name | Original Value | Final Value | ||||
| $B$7 | A | 0 | 1320000 | This is the value of the objective function at the optimal production plan | |||
| Adjustable Cells | |||||||
| Cell | Name | Original Value | Final Value | ||||
| $B$6 | A | 0 | 0 | This is the optimal production plan | |||
| $C$6 | B | 0 | 20000 | ||||
| Constraints | |||||||
| Cell | Name | Cell Value | Formula | Status | Slack | ||
| $B$12 | LHS | 40000 | $B$12<=$C$12 | Not Binding | 120000 | This says the machine hour constraint is slack by 120,000 M Hrs* | |
| $B$13 | LHS | 120000 | $B$13<=$C$13 | Binding | 0 | This says the direct labor hour constraint is binding (i.e. all DL hrs used up) | |
| $B$14 | LHS | 0 | $B$14>=$C$14 | Binding | 0 | This says the nonnegativity constraint for A is binding (i.e. A=0) | |
| $B$15 | LHS | 20000 | $B$15>=$C$15 | Not Binding | 20000 | This says the nonnegativity constraint for B is slack by 20,000 | |
| *This makes sense, because making 20,000 Bs uses 40,000 M Hrs, leaving 120,000 machine hours unused |
Sensitivity Report 2
| Microsoft Excel 9.0 Sensitivity Report | |||||||||||||
| Worksheet: [Ch4 part 2 page 3.xls]input | |||||||||||||
| Report Created: 5/22/2003 10:02:23 AM | |||||||||||||
| Adjustable Cells | |||||||||||||
| Final | Reduced | Objective | Allowable | Allowable | |||||||||
| Cell | Name | Value | Cost | Coefficient | Increase | Decrease | (CM of A between 0 and 22 gives same sales mix) | ||||||
| $B$6 | A | 0 | 0 | 20 | 2 | 1E+30 | This is the amount the CM per unit of A can change before the optimal sales mix would change | ||||||
| $C$6 | B | 20000 | 0 | 66 | 1E+30 | 6 | This is the amount the CM per unit of B can change before the optimal sales mix would change | ||||||
| (CM of B between 60 and any positive number gives same sales mix) | |||||||||||||
| Constraints | |||||||||||||
| Final | Shadow | Constraint | Allowable | Allowable | |||||||||
| Cell | Name | Value | Price | R.H. Side | Increase | Decrease | |||||||
| $B$12 | LHS | 40000 | 0 | 160000 | 1E+30 | 120000 | This shows how much you'd be willing to pay to loosen the RHS of the Machine Hour constraint by one M Hr | ||||||
| $B$13 | LHS | 120000 | 11 | 120000 | 360000 | 120000 | This shows how much you'd be willing to pay to loosen the RHS of the DL Hour constraint by one DL Hr | ||||||
| $B$14 | LHS | 0 | -2 | 0 | 60000 | 450000 | |||||||
| $B$15 | LHS | 20000 | 0 | 0 | 20000 | 1E+30 |
Answer Report 3
| Microsoft Excel 9.0 Answer Report | ||||||
| Target Cell (Max) | ||||||
| Cell | Name | Original Value | Final Value | |||
| $B$3 | Regular | 0 | 6,300,000 | |||
| Adjustable Cells | ||||||
| Cell | Name | Original Value | Final Value | |||
| $B$2 | Regular | 0 | 150,000 | |||
| $C$2 | Deluxe | 0 | 50000 | |||
| Constraints | ||||||
| Cell | Name | Cell Value | Formula | Status | Slack | |
| $B$11 | LHS | 50,000 | $B$11>=$C$11 | Not Binding | 50,000 | |
| $B$8 | LHS | 160,000 | $B$8<=$C$8 | Binding | 0 | |
| $B$10 | LHS | 150,000 | $B$10>=$C$10 | Not Binding | 150,000 | |
| $B$9 | LHS | 600,000 | $B$9<=$C$9 | Binding | 0 |
Sensitivity Report 3
| Microsoft Excel 9.0 Sensitivity Report | |||||||
| Worksheet: [Ch4 part 2 page 50.xls]input | |||||||
| Report Created: 7/22/2004 10:18:05 AM | |||||||
| Adjustable Cells | |||||||
| Final | Reduced | Objective | Allowable | Allowable | |||
| Cell | Name | Value | Cost | Coefficient | Increase | Decrease | |
| $B$2 | Regular | 150,000 | 0 | 20 | 2 | 6.8 | |
| $C$2 | Deluxe | 50000 | 0 | 66 | 34 | 6 | |
| Constraints | |||||||
| Final | Shadow | Constraint | Allowable | Allowable | |||
| Cell | Name | Value | Price | R.H. Side | Increase | Decrease | |
| $B$11 | LHS | 50,000 | 0 | 0 | 50000 | 1E+30 | |
| $B$8 | LHS | 160,000 | 8 | 160000 | 40000 | 40000 | |
| $B$10 | LHS | 150,000 | 0 | 0 | 150000 | 1E+30 | |
| $B$9 | LHS | 600,000 | 9 | 600000 | 200000 | 120000 |
Limits Report 3
| Microsoft Excel 9.0 Limits Report | |||||||||
| Worksheet: [Ch4 part 2 page 50.xls]input | |||||||||
| Report Created: 7/22/2004 10:18:05 AM | |||||||||
| Target | |||||||||
| Cell | Name | Value | |||||||
| $B$3 | Regular | 6,300,000 | |||||||
| Adjustable | Lower | Target | Upper | Target | |||||
| Cell | Name | Value | Limit | Result | Limit | Result | |||
| $B$2 | Regular | 150,000 | 0 | 3,300,000 | 150,000 | 6,299,999 | |||
| $C$2 | Deluxe | 50000 | 0 | 3000000 | 50000.0012631063 | 6300000.08336501 |
Answer Report 1
| Microsoft Excel 9.0 Answer Report | ||||||
| Target Cell (Max) | ||||||
| Cell | Name | Original Value | Final Value | |||
| $B$3 | Regular | 0 | 6,300,000 | |||
| Adjustable Cells | ||||||
| Cell | Name | Original Value | Final Value | |||
| $B$2 | Regular | 0 | 150,000 | |||
| $C$2 | Deluxe | 0 | 50000 | |||
| Constraints | ||||||
| Cell | Name | Cell Value | Formula | Status | Slack | |
| $B$9 | LHS | 600,000 | $B$9<=$C$9 | Binding | 0 | |
| $B$8 | LHS | 160,000 | $B$8<=$C$8 | Binding | 0 | |
| $B$11 | LHS | 50,000 | $B$11>=$C$11 | Not Binding | 50,000 | |
| $B$10 | LHS | 150,000 | $B$10>=$C$10 | Not Binding | 150,000 |
Sensitivity Report 1
| Microsoft Excel 9.0 Sensitivity Report | |||||||
| Adjustable Cells | |||||||
| Final | Reduced | Objective | Allowable | Allowable | |||
| Cell | Name | Value | Cost | Coefficient | Increase | Decrease | |
| $B$2 | Regular | 150,000 | 0 | 20 | 2 | 6.8 | |
| $C$2 | Deluxe | 50000 | 0 | 66 | 34 | 6 | |
| Constraints | |||||||
| Final | Shadow | Constraint | Allowable | Allowable | |||
| Cell | Name | Value | Price | R.H. Side | Increase | Decrease | |
| $B$9 | DL hr | 600,000 | 8.50 | 600000 | 200000 | 120000 | |
| $B$8 | mach hr | 160,000 | 7.50 | 160000 | 40000 | 40000 | |
| $B$11 | D>0 | 50,000 | 0.00 | 0 | 50000 | 1E+30 | |
| $B$10 | R>0 | 150,000 | 0.00 | 0 | 150000 | 1E+30 |
Limits Report 1
| Microsoft Excel 9.0 Limits Report | |||||||||
| Worksheet: [Ch4 part 2 page 50.xls]input | |||||||||
| Report Created: 7/22/2004 10:39:43 AM | |||||||||
| Target | |||||||||
| Cell | Name | Value | |||||||
| $B$3 | Regular | 6,300,000 | |||||||
| Adjustable | Lower | Target | Upper | Target | |||||
| Cell | Name | Value | Limit | Result | Limit | Result | |||
| $B$2 | Regular | 150,000 | 0 | 3,300,000 | 150,000 | 6,299,999 | |||
| $C$2 | Deluxe | 50000 | 0 | 3000000 | 50000.0012631063 | 6300000.08336501 |
input
| Regular | Deluxe | |||
| 150,000 | 50000 | |||
| 6,300,000 | ||||
| Contraints | ||||
| LHS | RHS | |||
| 160,000 | 160,000 | mach hr constraint | ||
| 600,000 | 600,000 | DL hr constraint | ||
| 150,000 | 0 | R nonnegative | ||
| 50,000 | 0 | D nonnegative |
Answer Report 2
| Microsoft Excel 9.0 Answer Report | |||||||
| Worksheet: [Ch4 part 2 page 3.xls]input | |||||||
| Report Created: 5/22/2003 10:02:23 AM | |||||||
| Target Cell (Max) | |||||||
| Cell | Name | Original Value | Final Value | ||||
| $B$7 | A | 0 | 1320000 | This is the value of the objective function at the optimal production plan | |||
| Adjustable Cells | |||||||
| Cell | Name | Original Value | Final Value | ||||
| $B$6 | A | 0 | 0 | This is the optimal production plan | |||
| $C$6 | B | 0 | 20000 | ||||
| Constraints | |||||||
| Cell | Name | Cell Value | Formula | Status | Slack | ||
| $B$12 | LHS | 40000 | $B$12<=$C$12 | Not Binding | 120000 | This says the machine hour constraint is slack by 120,000 M Hrs* | |
| $B$13 | LHS | 120000 | $B$13<=$C$13 | Binding | 0 | This says the direct labor hour constraint is binding (i.e. all DL hrs used up) | |
| $B$14 | LHS | 0 | $B$14>=$C$14 | Binding | 0 | This says the nonnegativity constraint for A is binding (i.e. A=0) | |
| $B$15 | LHS | 20000 | $B$15>=$C$15 | Not Binding | 20000 | This says the nonnegativity constraint for B is slack by 20,000 | |
| *This makes sense, because making 20,000 Bs uses 40,000 M Hrs, leaving 120,000 machine hours unused |
Sensitivity Report 2
| Microsoft Excel 9.0 Sensitivity Report | |||||||||||||
| Worksheet: [Ch4 part 2 page 3.xls]input | |||||||||||||
| Report Created: 5/22/2003 10:02:23 AM | |||||||||||||
| Adjustable Cells | |||||||||||||
| Final | Reduced | Objective | Allowable | Allowable | |||||||||
| Cell | Name | Value | Cost | Coefficient | Increase | Decrease | (CM of A between 0 and 22 gives same sales mix) | ||||||
| $B$6 | A | 0 | 0 | 20 | 2 | 1E+30 | This is the amount the CM per unit of A can change before the optimal sales mix would change | ||||||
| $C$6 | B | 20000 | 0 | 66 | 1E+30 | 6 | This is the amount the CM per unit of B can change before the optimal sales mix would change | ||||||
| (CM of B between 60 and any positive number gives same sales mix) | |||||||||||||
| Constraints | |||||||||||||
| Final | Shadow | Constraint | Allowable | Allowable | |||||||||
| Cell | Name | Value | Price | R.H. Side | Increase | Decrease | |||||||
| $B$12 | LHS | 40000 | 0 | 160000 | 1E+30 | 120000 | This shows how much you'd be willing to pay to loosen the RHS of the Machine Hour constraint by one M Hr | ||||||
| $B$13 | LHS | 120000 | 11 | 120000 | 360000 | 120000 | This shows how much you'd be willing to pay to loosen the RHS of the DL Hour constraint by one DL Hr | ||||||
| $B$14 | LHS | 0 | -2 | 0 | 60000 | 450000 | |||||||
| $B$15 | LHS | 20000 | 0 | 0 | 20000 | 1E+30 |
Answer Report 3
| Microsoft Excel 9.0 Answer Report | ||||||
| Target Cell (Max) | ||||||
| Cell | Name | Original Value | Final Value | |||
| $B$3 | Regular | 0 | 6,300,000 | |||
| Adjustable Cells | ||||||
| Cell | Name | Original Value | Final Value | |||
| $B$2 | Regular | 0 | 150,000 | |||
| $C$2 | Deluxe | 0 | 50000 | |||
| Constraints | ||||||
| Cell | Name | Cell Value | Formula | Status | Slack | |
| $B$11 | LHS | 50,000 | $B$11>=$C$11 | Not Binding | 50,000 | |
| $B$8 | LHS | 160,000 | $B$8<=$C$8 | Binding | 0 | |
| $B$10 | LHS | 150,000 | $B$10>=$C$10 | Not Binding | 150,000 | |
| $B$9 | LHS | 600,000 | $B$9<=$C$9 | Binding | 0 |
Sensitivity Report 3
| Microsoft Excel 9.0 Sensitivity Report | |||||||
| Worksheet: [Ch4 part 2 page 50.xls]input | |||||||
| Report Created: 7/22/2004 10:18:05 AM | |||||||
| Adjustable Cells | |||||||
| Final | Reduced | Objective | Allowable | Allowable | |||
| Cell | Name | Value | Cost | Coefficient | Increase | Decrease | |
| $B$2 | Regular | 150,000 | 0 | 20 | 2 | 6.8 | |
| $C$2 | Deluxe | 50000 | 0 | 66 | 34 | 6 | |
| Constraints | |||||||
| Final | Shadow | Constraint | Allowable | Allowable | |||
| Cell | Name | Value | Price | R.H. Side | Increase | Decrease | |
| $B$11 | LHS | 50,000 | 0 | 0 | 50000 | 1E+30 | |
| $B$8 | LHS | 160,000 | 8 | 160000 | 40000 | 40000 | |
| $B$10 | LHS | 150,000 | 0 | 0 | 150000 | 1E+30 | |
| $B$9 | LHS | 600,000 | 9 | 600000 | 200000 | 120000 |
Limits Report 3
| Microsoft Excel 9.0 Limits Report | |||||||||
| Worksheet: [Ch4 part 2 page 50.xls]input | |||||||||
| Report Created: 7/22/2004 10:18:05 AM | |||||||||
| Target | |||||||||
| Cell | Name | Value | |||||||
| $B$3 | Regular | 6,300,000 | |||||||
| Adjustable | Lower | Target | Upper | Target | |||||
| Cell | Name | Value | Limit | Result | Limit | Result | |||
| $B$2 | Regular | 150,000 | 0 | 3,300,000 | 150,000 | 6,299,999 | |||
| $C$2 | Deluxe | 50000 | 0 | 3000000 | 50000.0012631063 | 6300000.08336501 |
Answer Report 1
| Microsoft Excel 9.0 Answer Report | ||||||
| Target Cell (Max) | ||||||
| Cell | Name | Original Value | Final Value | |||
| $B$3 | Regular | 0 | 6,300,000 | |||
| Adjustable Cells | ||||||
| Cell | Name | Original Value | Final Value | |||
| $B$2 | Regular | 0 | 150,000 | |||
| $C$2 | Deluxe | 0 | 50000 | |||
| Constraints | ||||||
| Cell | Name | Cell Value | Formula | Status | Slack | |
| $B$9 | LHS | 600,000 | $B$9<=$C$9 | Binding | 0 | |
| $B$8 | LHS | 160,000 | $B$8<=$C$8 | Binding | 0 | |
| $B$11 | LHS | 50,000 | $B$11>=$C$11 | Not Binding | 50,000 | |
| $B$10 | LHS | 150,000 | $B$10>=$C$10 | Not Binding | 150,000 |
Sensitivity Report 1
| Microsoft Excel 9.0 Sensitivity Report | |||||||
| Adjustable Cells | |||||||
| Final | Reduced | Objective | Allowable | Allowable | |||
| Cell | Name | Value | Cost | Coefficient | Increase | Decrease | |
| $B$2 | Regular | 150,000 | 0 | 20 | 2 | 6.8 | |
| $C$2 | Deluxe | 50000 | 0 | 66 | 34 | 6 | |
| Constraints | |||||||
| Final | Shadow | Constraint | Allowable | Allowable | |||
| Cell | Name | Value | Price | R.H. Side | Increase | Decrease | |
| $B$9 | DL hr | 600,000 | 8.50 | 600000 | 200000 | 120000 | |
| $B$8 | mach hr | 160,000 | 7.50 | 160000 | 40000 | 40000 | |
| $B$11 | D>0 | 50,000 | 0.00 | 0 | 50000 | 1E+30 | |
| $B$10 | R>0 | 150,000 | 0.00 | 0 | 150000 | 1E+30 |
Limits Report 1
| Microsoft Excel 9.0 Limits Report | |||||||||
| Worksheet: [Ch4 part 2 page 50.xls]input | |||||||||
| Report Created: 7/22/2004 10:39:43 AM | |||||||||
| Target | |||||||||
| Cell | Name | Value | |||||||
| $B$3 | Regular | 6,300,000 | |||||||
| Adjustable | Lower | Target | Upper | Target | |||||
| Cell | Name | Value | Limit | Result | Limit | Result | |||
| $B$2 | Regular | 150,000 | 0 | 3,300,000 | 150,000 | 6,299,999 | |||
| $C$2 | Deluxe | 50000 | 0 | 3000000 | 50000.0012631063 | 6300000.08336501 |
input
| Regular | Deluxe | |||
| 150,000 | 50000 | |||
| 6,300,000 | ||||
| Contraints | ||||
| LHS | RHS | |||
| 160,000 | 160,000 | mach hr constraint | ||
| 600,000 | 600,000 | DL hr constraint | ||
| 150,000 | 0 | R nonnegative | ||
| 50,000 | 0 | D nonnegative |
