When fixed cost is 2000, unit variable cost is 10, unit price is 8, and sales quantity is 500, what are the total cost, total revenue, net profit, and break-even quantity?

Excel by Geoffrey

 

Break-Even Analysis in Excel

  • Basic concept of break-even analysis
    1. A firm reaches break-even production quantity when its total revenue equals to the total of its fixed cost and variable cost.
    2. Total revenue = unit price * production quantity;

Variable cost = unit variable cost * production quantity; thus

Break-even quantity =                 Fixed cost_________              
(Unit price – Unit variable cost)

 

  • I don’t see the Developer tab
    1. Click the Microsoft Office Button, and then click Excel Options.
    2. Click Popular, and then select the Show Developer tab in the Ribbon check box.
  • Create a scroll bar to set the Fixed Cost
    1. Developer à Insert à ActiveX Controls à Select Scroll Bar.
    2. Draw the scroll bar in cell D4.
    3. Right click the mouse; in the properties window, type 500 as the value of LargeChange, C4 as the value of LinkedCell, 2000 as the value of Max, 500 as the value of Min, and 10 as the value of SmallChange.
    4. Press the Enter key; close the properties window.
    5. Click the Design Mode button and try the scroll bar control by clicking the arrows as well as the area between them.
  • Create a scroll bar to set the Unit Variable Cost in cell D6
    1. Repeat above necessary steps.
    2. In the properties window, type C6 as the value of LinkedCell, 10 as the value of Max, and 1 as the value of Min.
  • Create a scroll bar to set the Unit Price in cell D8
    1. Repeat above necessary steps.
    2. In the properties window, type C8 as the value of LinkedCell, 20 as the value of Max, and 5 as the value of Min.
  • Create a scroll bar to set the Sale Quantity in cell D12
    1. Repeat above necessary steps.
    2. In the properties window, type 100 as the value of LargeChange, C12 as the value of LinkedCell, 1000 as the value of Max, and 0 as the value of Min, and 5 as the value of SmallChange.
  • In cell C10, type the formula: =IF(C8>C6, C4/(C8-C6), “No Solution”) then click enter
  • In cell G4, type the formula: =C8-C6 then click enter
  • In cell G6, type the formula: =C4+C6*C12  then click enter
  • In cell G8, type the formula: =C8*C12 then click enter
  • In cell G10, type the formula: =G8-G6 then click enter
  • Type the words shown on the screen in cell B2, B4, B6, B8, B10, B12, F6, F8, and F10.
  • Create the Profit Analysis chart
    1. Select cells F6, G6, F8, G8, F10, G10.
    2. Click Insert à Column à select the first 2-D Column chart.
    3. Click the Layout tab under Chart Tools, click Chart or Axis Titles in the Labels group, type Profit Analysis as Chart title and U.S. Dollars as vertical axis title.
    4. Click Data Labels in the Labels group to add data labels.
    5. Right click the Y axis, and select Format Axis; under the Axis Options type -4000 as the value of Minimum and 20000 as the value of Maximum.
    6. Click the Format tab under Chart Tools to format chart and plot.

Questions:

  1. When fixed cost is 1000, unit variable cost is 4, unit price is 10, and sales quantity is 800, what are the total cost, total revenue, net profit, and break-even quantity?
  2. When fixed cost is 1500, unit variable cost is 6, unit price is 14, and sales quantity is 1000, what are the total cost, total revenue, net profit, and break-even quantity?
  3. When fixed cost is 2000, unit variable cost is 10, unit price is 8, and sales quantity is 500, what are the total cost, total revenue, net profit, and break-even quantity?