Explore BrainMass

Sensitivity Analysis with a Profit Target

This content was STOLEN from BrainMass.com - View the original, and get the already-completed solution here!

You are provided with the following data relating to gizmo division of Tumbledown plc.

Variable cost per unit = $320 for outputs up to 16,000 units
= $240 for extra outputs over 16,000 units

Fixed costs = $1,200,000

Sales during the last period were 16000 units @ $400 per unit

The directors are far from satisfied with the above position, and at their last board meeting set a profit target of $400,000 for the next period, and suggested that, in order to achieve this target, one of the following options be adopted:

(i) Reduce selling price by $20 per unit

(ii) Spend $100,000 on TV advertising, reducing the selling price to $360 per unit

(iii) Increase quality at a cost of $10 per unit and maintain the selling price at $400 per unit

(iv) Buy a new machine which will cut variable cost by $20 per unit, but would increase fixed cost by $72,000. The selling price would remain at $400 per unit


(a) Calculate the number of gizmos required to break even under present price and cost conditions. Calculate how many units would have to be sold to meet the profit target for each of the suggested options

(b) Write a brief note for the directors discussing the implications of your findings and recommending which option they should adopt. Your report should address any other important matters which should be considered in arriving at their final decision

© BrainMass Inc. brainmass.com October 25, 2018, 6:07 am ad1c9bdddf

Solution Summary

This solution illustrates one way to compute a break-even point when variable costs change with the level of volume.

See Also This Related BrainMass Solution

LP in EXCEL Solver, Sensitivity Analysis and Limits Report

Acme Babyfoods mixes two strengths of apple juice. One quart of Beginner's juice is made from 30 fluid ounces of water and 2 fluid ounces of apple juice concentrate. One quart of Advanced juice is made from 20 fluid ounces of water and 12 fluid ounces of concentrate. Every day Acme has available 30,000 fluid ounces of water and 3,600 fluid ounces of concentrate. Acme makes a profit of 20 cents on each quart of Beginner's juice and 30 cents on each quart of Advanced juice. How many quarts of each should Acme make each day to get the largest profit? How would this change if Acme made a profit of 40 cents on Beginner's juice and 20 cents on Advanced juice?

You will be turning in two (2) deliverables, a short write-up of the project and the spreadsheet showing your work.

Write up:

Problem introduction

Write up the LP model for the problem. Include the objective function and all constraints, including any non-negativity constraints.

Present the optimal solution, based on your work in Excel. Explain what the results mean.

Write a paragraph addressing the part of the problem pertaining to sensitivity analysis and shadow price.


As previously noted, please set up your problem in Excel and find the solution using Solver. Clearly label the cells in your spreadsheet. You will turn in the entire spreadsheet, showing the setup of the model, and the results.

View Full Posting Details