Question

The following is a question from Quantitative Methods for Business & Economics.

6. Given the information below answer the following questions. 1 Fixed Cost $1,000.00 3 Variable Costecost/unit $25.00 5 Selling price=price/unit $45.00 7 Production volume=#units 40 9 Total Cost $2,000.00 11 Total Revenue $1,800.00 13 Total Profit -$200.00 1) What is the general formula that you should enter in Excel to compute the TOTAL COST and what cell should the formula be entered based on the worksheet above? 2) What is the general formula that you should enter in Excel to compute the TOTAL PROFIT and what cell should the formula be entered based on the worksheet above? Goal Seek 3) Using a "what if analysis" fill in the three blanks under the "goal seek" so that the Break Even Point for the data listed in the worksheet above can be determined. Set Cell: To value: By changing cell: OK cancel

Answer 1

Solution:

1. Total cost is simply sum of fixed cost and variable cost. As the names suggest, fixed cost is fixed and independent of the quantity produced. On the other hand, variable cost keeps changing depending on the units produced, it varies as the quantity is increased or decreased.

Thus, total fixed cost = $1000

Total variable cost = 25*Q where Q is the production volume (no. of units produced)

So, total cost = total fixed cost + total variable cost

Total cost = 1000 + 25*40 (=2000, just as shown in the required cell)

This makes us reach the following general excel formula:

Total cost = B1 + B3*B7

This formula should be entered in the cell adjacent to 'Total Cost' (so adjacent to A9) that is, in B9.

2. Total profit is simply total revenue - total cost.

Further, total revenue = selling price per unit*quantity

So, as per given excel, total revenue = 45*40 (= B5*B7)

Then, from first part and above formula, we can write total profit as:

Total profit = 45*40 - 2000 = -200 (just as shown in the required cell)

General formula = B5*B7 - B1 - B3*B7 = (B5 - B3)*B7 - B1

This should be entered in cell adjacent to total profit, that is, in cell B13.

3. In Goal Seek under What if Analysis:

Set Cell value is simply the formula cell for which we require the desired value.

To value is the desired value

By changing cell is the variable value which is required to be changed to reach the desired cell

So, breakeven point simply means the point where there is no profit no loss situation, so total profit = 0 (or total revenue = total cost)

Then, set cell is cell carrying formula for total profit, that is, B13

To value, or the desired value, for total profit is then 0

And value to be changed is quantity, as rest remain same and point is determined as the output level where breaker occurs. So, by changing cell B7.