Practical computer based workshop session using spreadsheets to answer ‘what if?’ situations using CVP analysis.

ALL CALCULATIONS ARE READY IN A EXCEL DOCUMENT, ONLY COMMENT NEEDED

Current situation

The selling price is £15.00, with a variable cost per unit sold of £10.50. The expected number of units sold is 350,000. Fixed costs are currently £700,000.

Option 1

This option is an expansion of the operation, which allows the volume of sales to be increased, but additional fixed costs are incurred. The selling price remains at £15.00 and the variable costs at £10.50, however the volume of sales will increase to 430,000 and the fixed costs would be £950,000.

Option 2

This is an adaptation of the product, making a cheaper alternative product for the market. The selling price would drop to £12; the variable costs are £7. The number of customers will increase to 490,000, with the fixed costs at £950,000.

Option 3

This is an adaptation of the product, making a superior quality product for the market. The selling price would increase to £20 and the variable costs would increase to £15.5. The number of customers would decrease to 270,000 and fixed costs would remain at £700,000.

Completed spreadsheet is in a file- showing the current situation and alternatives with breakeven chart in Excel – illustrate the current situation.

!! Make only written comment concerning the alternatives and your recommendations to the manager.!!