Excel Assignment #2

ACCT 20200 Summer 2013

CVP Analysis

Joe, a student at ND, has decided to start a business. He has decided to open a Brat stand between the football stadium and the Mendoza College of Business Building on football Saturdays. He expects to sell Brats for $4.00 each. Below are a list of his expected costs.

Variable Costs (per unit):
Brat / $ 1.13
Buns / $ 0.52
Mustard / $ 0.05
Ketchup / $ 0.05
Sour Kraut / $ 0.18
Wrapper / $ 0.51
Total Variable Costs / $ 2.44
Fixed Costs:
Workers Wages / $7.50/hour, need two workers for three hours each
Rent space/Permit cost / $ 160.00
Gas for Grill / $ 100.00
Ice / 15 bags of ice at $3.89 each

PART 1: Required:

Assuming that Joe is only going to sell Brats,

  1. What are the total fixed costs for each football Saturday?
  2. What is the Contribution Margin per unit?
  3. What is the Contribution Margin percentage?
  4. What is the Breakeven Point in Units?
  5. What is the Breakeven Point in Sales Dollars?
  6. What unit sales are needed to earn a $200 profit?
  7. What Sales Dollars are required for a $200 profit?
  8. Suppose the selling price per unit decreases, what is the impact on the Breakeven point?
  9. Suppose the selling price per unit increases, what is the impact on the Breakeven point?
  10. Suppose the variable costs per unit increase, what is the impact on the Breakeven point?
  11. Suppose the variable costs per unit decrease, what is the impact on the Breakeven point?
  12. Suppose the fixed costs per unit increase, what is the impact on the Breakeven point?
  13. Suppose the fixed costs per unit decrease, what is the impact on the Breakeven point?

Joe is contemplating selling more than just Brats. In fact, he thinks that he should also sell hamburgers and bottled water. He believes that he will sell 110Brats, 85 hamburgers and 205 bottles of water. He expects to sell hamburgers for $4.50 each and bottles of water for $2.00 each. Below are the costs associated with each of these items:

Sales price per Hamburger / $ 4.50
Variable Costs (per unit):
Meat / $ 1.51
Buns / $ 0.47
Mustard / $ 0.05
Ketchup / $ 0.10
Cheese / $ 0.28
Pickles / $ 0.18
Total Variable Costs / $ 2.59
Sales Price for Bottled Water / $ 2.00
Variable Cost (per bottle) / $ 0.53

PART 2: REQUIRED

  1. Prepare a contribution margin income statement for each item to be sold and for the company as a whole.
  2. What is the weighted average Contribution Margin percentage (ratio)?
  3. At the Breakeven point, what will SALES DOLLARSof ALL PRODUCTS will be?
  4. At the Breakeven point, what will SALES DOLLARS of Brats will be?
  5. At the Breakeven point, what will SALES DOLLARS Hamburgers will be?
  6. At the Breakeven point, what will SALES DOLLARS Bottles of Water will be?

Excel Rules:

  1. You must use the Excel Templates provided on the ACCT 20200 website ( Note that each template is individualized for each student with an ID code for each student. It will be assumed a Honor Code VIOLATION if you use a template with someone else’s ID Code.
  1. You must use Microsoft EXCEL and not any other spreadsheet program. Failure to use MS Excel will result in a zero grade.
  1. You must work individually on this assignment. Working with another student is a violation of the Notre Dame Honor code and will be enforced when discovered.
  1. You must use links and formulas where ever possible. Failure to exploit Excel will result in a zero grade. There are only a couple of instances where typing numbers may be necessary.
  1. You must sign your name to the honor code statement (the first sheet of the Excel template). Failure to sign your name (by typing your name) will result in a zero grade.
  1. You must name your Excel file as: last name first name Excel 2. Failure to name your file in this exact syntax will result in a zero grade.
  1. You must save your file as either an xls or xlsx file so that it can be read by your instructor. Failure to turn in a file that is readable by your instructor will result in a zero grade.
  1. You must follow your instructor’s instructions on how to turn in the assignment. Failure to follow your instructor’s instructions will result in a zero grade.

© Copyright of Michael J. Meyer 2009. Use by permission only.