Goal Seek Exercise # 5b (Excel 2016 (or later version))

You have a company producing units of Product A. Your fixed costs for a year are $100,000. The cost of making one unit is $3.40. You can sell a unit for $5.00. Assume you can sell as many units as you produce. If you make 100,000 units per year how much profit will you make? Create a worksheet 5b to answer this question. Have the total cost and the total sales and the difference (total sales minus total cost) which is the profit, included in the worksheet. Use Goal Seek to determine how many units you need to produce in order to break even (i.e. to have zero profit). Again, use Goal Seek to determine what the lowest selling price is for you to break even if you sell 100,000 units per year. Show both sets of figures in worksheet 5b.

Goal Seek Exercise # 5c (Excel 2016 (or later version))

You will have to copy the data in worksheet 5b to a new worksheet called 5c to do this part. For the same company as in 5b, use Data Table with 2 variables and vary

1. the selling price in steps of $0.50 from $6.00 to $3.00, i.e., $6.00, $5.50, $5.00, $4.50, $4.00, $3.50, and finally $3.00
2. the units sold in steps of 10,000 from 150,000 all the way down to 50,000 units, i.e., 150000, 140000, 130000 etc. down to 50000.

Calculate the profits for each combination of selling price and units sold in your worksheet and then answer these questions in your Excel worksheet 5c itself. Use the numbers from your Data Table to arrive at the answers.

For 150,000 units sold, the selling price must be between ?? and ?? in order to break even. For 140,000 units sold, the selling price must be between ?? and ?? in order to break even. For 130,000 units sold, the selling price must be between ?? and ?? in order to break even. For 120,000 units sold, the selling price must be between ?? and ?? in order to break even. For 110,000 units sold, the selling price must be between ?? and ?? in order to break even. For 100,000 units sold, the selling price must be between ?? and ?? in order to break even. For 90,000 units sold, the selling price must be between ?? and ?? in order to break even. For 80,000 units sold, the selling price must be between ?? and ?? in order to break even. For 70,000 units sold, the selling price must be between ?? and ?? in order to break even. For 60,000 units sold, the selling price must be between ?? and ?? in order to break even. For 50,000 units sold, the selling price must be between ?? and ?? in order to break even.