Question

Three entrepreneurs plan to open a copy shop. It costs $5000 per year to rent a copier. It costs $0.03 per copy to operate the copier. Other fixed costs of running the store will amount to $400 per month. The planned price per copy is $0.10. The shop will be open 365 days per year. Each copier can make up to 100,000 copies per year.

SHOW THE FOLLOWING IN EXCEL( show the formulas and calculation)within excel !!!  THANK YOU SO SO MUCH!

1. Use Goal Seek feature to determine annual break-even point for the use of three rented copiers by changing (1) copy units, (2) unit pricing, (3) per variable costs, and (4) fixed costs.

Answer 1

Please refer to the following Excel snapshots for the calculations:

The volume that we have taken here is the daily production.

Hence, the variable cost and total sales will be multiplied by 365.

For 3 copier machines,

Cost = Total Fixed Cost + Total Variable Cost

Total Fixed Cost = 5000 * 3 + 400 * 12

Total Variable Cost = 0.03 * Volume * 365 days

Cost = 5000 * 3 + 400 * 12 + 0.03 * Volume * 365

Total Revenue = 0.1 * Volume * 365

Profit = Total Sales - Total Cost

For breakeven analysis, we set the profit = 0

Here, for 3 copiers, we can see that the breakeven volume is between 700 and 800 units of daily production.

We use Data -> What If Analysis -> Goal Seek

We always set the Profit = 0 for break-even analysis

Changing:

1) Copy units

2) Unit pricing

3) per variable cost

4) Fixed Costs

The initialization example I talked about in the comments is as follows:

Here, the formula remains the same but we need a target price per unit for 1000 copy units such that it breaks even at a cost of 5000 * 3 + 400 * 12 + 1000 * 0.03 * 365 = 30750

Kindly up-vote this answer if it was helpful to you. This will help me in maintaining the quality of my answers. Do reach out to me in case of any queries. Thank you.

Happy Learning!