Question

Break even analysis USE EXCEL

Gallant Carpet Cleaning cannot meet demand with its current equipment. Gallant Carpet Cleaning is considering two new carpet cleaning machines. Machine A cleans carpets to residential standards, while machine B cleans and sanitizes carpets to hospital standards. Machine A costs $25,000 and machine B costs $36,000. The company estimates its cost per cleaning (including all chemicals) for machine A will be $10 and machine B will be $8. The revenue per carpet cleaned for A would be $42, while B could bring in $45 per carpet, due to the higher degree of sanitation provided.

Complete the table below for the data as well as equations (using Q) for Total cost (TC), Total Revenue (TR) and Profit (Pr).

# of Carpets cleaned = Q	Machine A	Machine B
FC
v= Variable cos1/unit
Selling price =R
TC
TR
Profit (Pr)
Break-Even Q

Machine B needs to clean how many more carpets than machine A to break even?

How many carpets Q to be cleaned will make you become indifferent between choosing Machine A or B? Show your work.

Plot the Profit graphs (on same graph) for each machine type. From your graph, recommend over what range of more carpet cleaning demand (Q) which machine type will be preferred.

Answer 1

The following question can be solved through calculations in

1) Formulation of the table and calculations

Formula used for break even point

2) Formulas used in Excel

Total cost = Fixed costs + (Break even point (in units) * Variable cost per unit)

Total revenue = Break even point (in units) * Sales cost per unit

Total profit is 0 as at breakeven point the profit is 0.

A) Number of extra carpets Machine B needs to clean more than machine A to break even = 973 - 782 = 191

B) Lets assume both Machine A and MAchine B produce Q carpets

Profit for machine A = (Q - 782)* Profit per unit

= (Q -782 ) * (42=10) = 32* (Q -782)

Profit for machine B = (Q - 973)* Profit per unit

= (Q -973) * (45=8) = 37* (Q -973)

These two profits must be equal so that the company becomes indifferent between choosing Machine A or B.

Hence, 32* (Q -782) =  37* (Q -973)

Q = 2196

Ans c) To plot the profit graphs, we need to fromulate a table

Formulas used

Combined graph

From the graph we can see that from 0 to 2196 range of carpets, Machine A would be more profitable

After 2196 carpets, Machine B would be more profitable.