Data (given)
Sales Price	$100	per unit
Variable Costs	$38	per unit
Fixed Cost	$620
Excel Formulas
Contribution Margin		per unit
Break-Even Point		units
Data Table
Use the data and calculations above to create a data table below that will be used to graph of costs, profits and volume.
Units	Costs	Gross Revenue	Profit
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

You are to build a spreadsheet that can calculate break-even point. Break-even point is the number of units that a business has to sell to negate costs and begin producing a profit. The calculations will update when you change the costs (fixed or variable) or sales price.

Hint: The break-even point cannot be calculated with one Excel function. Through this case you will learn how to accomplish a complex task by breaking the problem into smaller steps. This case will guide you through the steps.

HINT: STEP-BY-STEP WALKTHROUGH

To help you get started, the file already has jacket sales, cost data, and column titles for you to fill out. Complete the following steps:

1. Excel Formula Section: Perform the following calculations using the given data.

   1. In cell C7, calculate the contribution margin per jacket unit.

      $fill in the blank 2

   2. In cell C8, calculate the break-even point in jacket units.

      fill in the blank 3

2. Data Table Section: Perform the following calculations using the given data. Hint: You need to use absolute references.

   Note: You must create this data table to be able to graph the break-even point.

   1. In cell C13, create a formula using the number of units in column B and costs (fixed and variable) to calculate the total cost for producing that many units. For example, in C13, what is the total costs for producing one jacket?

      Important: Although cost is often thought of as a negative number in accounting, keep your calculations in positive numbers.

      $fill in the blank 4

      Copy and paste your formula into the remainder of column C.

   2. In cell D13, create a formula using the number of units in column B and the sales price to calculate the gross revenue for selling that many jackets. For example, in D13, what is the gross revenue for selling one jacket?

      $fill in the blank 5

      Copy and paste your formula for the remainder of column D.

3. Graph: Select the data table you have created in cells C12D42. Create a line graph for this data. Where the cost and gross revenue lines cross is the break-even point. Choose the correct graph.

   A.		B.
   C.		D.

   The correct graph is

   graph Agraph Bgraph Cgraph D

   .

4. Use your spreadsheet to explore how costs and volume interact. What happens to the break-even point if you raise the fixed costs?

   The break-even point will

   increasedecrease

   .

   What happens to the break-even point if you increase your sales price?

   The break-even point will

   increasedecrease

   .

   What happens to the break-even point if you lower the variable costs?

   The break-even point will

   increasedecrease

   .

5. Calculate profits in column E. For example, in E13, what is the profit for producing one jacket? Use a minus sign to enter loss, if any.

   $fill in the blank 10

6. Using the graph, explain how a break-even point changes in relation to cost and sell price.

   The input in the box below will not be graded, but may be reviewed and considered by your instructor.