Excel Assignment #1

Preparing a Contribution Margin Income Statement and Operating Leverage

Spring 2013

1. Assume that a company is budgeting to sell 2,500 units of a product at a selling price per unit of $32. The variable cost per unit is $26 and total fixed costs are $5,000.

REQUIRED

Prepare a contribution margin income statement and calculate operating leverage.

2. Suppose the company is unsure exactly how many units they will sell. As such, their marketing department has provided a worst case scenario where sales would be 1,500 units and a best case scenario where sales would be 2,700 units. Assume that the selling price per unit, variable cost per unit and fixed costs will remain constant (per part 1).

REQUIRED

Prepare a contribution margin income statement and calculate operating leverage for both the worst case scenario (sales of 1,500 units) and the best case scenario (sales of 2,700 units).

3. Suppose the company believes that 2,500 units is the most likely volume of sales. However, it is unsure at what selling price per unit it will be able to charge. The marketing department has provided a high estimate of $40 per unit and a low estimate of $30 per unit. Assume that variable costs per unit and fixed costs will remain constant (per part 1).

REQUIRED

Prepare a contribution margin income statement and calculate operating leverage for both the high ($40 per unit) and low ($30 per unit) estimate of the selling price.

4. Suppose the company believes that 2,500 units is the most likely volume of sales and that $32 is the most likely selling price per unit. However, the production department is unsure as to the exact variable cost per unit. The production department has provided a high cost per unit of $29 and a low cost per unit of $19. Assume that fixed costs will remain constant (per part 1).

REQUIRED

Prepare a contribution margin income statement and calculate operating leverage for both the high variable cost per unit ($29) and the low cost per unit ($19).

5. Suppose the company believes that 2,500 units is the most likely volume of sales, that $32 is the most likely selling price per unit, and that $26 is the most likely variable cost per unit. However, the accounting department is unsure as to the exact value of fixed costs. They have provided a high estimate of fixed costs to be $6,500 and a low estimate of fixed costs to be $2,500.

REQUIRED

Prepare a contribution margin income statement and calculate operating leverage for both the high estimate of fixed costs ($6,500) and for the low estimate of fixed costs ($2,500).

6. Based on the contribution margins you have created in parts 1-5, answer the following questions (your comparison is relative to part 1 data):

  1. When sales volume decreases,
  2. What is the impact on net income?
  3. What is the impact on the contribution margin?
  1. When sales volume increases,
  2. What is the impact on net income?
  3. What is the impact on the contribution margin?
  1. When the sales price per unit increases,
  2. What is the impact on net income?
  3. What is the impact on the contribution margin?
  1. When the sales price per unit decreases,
  2. What is the impact on net income?
  3. What is the impact on the contribution margin?
  1. When the variable cost per unit increases,
  2. What is the impact on net income?
  3. What is the impact on the contribution margin?
  1. When the variable cost per unit decreases,
  2. What is the impact on net income?
  3. What is the impact on the contribution margin?
  1. When fixed costs increase,
  2. What is the impact on net income?
  3. What is the impact on the contribution margin?
  1. When fixed costs decrease,
  2. What is the impact on net income?
  3. What is the impact on the contribution margin?

Rules regarding the completion of the Excel Assignments:

NOTE: You MUST use Microsoft EXCEL and not any other spreadsheet program.

1)You MUST use the Excel Templates provided on the Accounting 1020 website for this quarter.

2)You MUST download the Excel Templates using YOUR account from the Accounting 1020 website. Each template is individually coded to prevent academic dishonesty. Any student found using the template from another student’s account AND the student that allows another student to use his/her account will also receive a zero on the assignment and be subject to further penalties for academic dishonesty.

3)You MUST sign the academic honesty pledge or you will automatically receive a zero--NO EXCEPTIONS!

4)You MUST use formulas and links whenever possible. If you simply type in your solutions rather than use formulas, you will receive a zero.

5)You MUST name your Excel files per the instruction sheet (LastNameFirstNameExcel1). Failure to name your Excel file correctly will result in a zero--NO EXCEPTIONS. Please make sure that there are no blank spaces in the file name (e.g., SmithJohnExcel1)

6)Your Instructor will inform you how to send your assignments electronically. Failure to follow YOUR INSTRUCTOR’S INSTRUCTIONS will result in a grade of zero for the assignment (note that each instructor may have his/her own required method of turning in the assignment).

7)To receive credit, your instructor must RECEIVE your assignment by the due date no later than 11pm without exception.

8)This assignment MUST be completed individually. Working together constitutes academic dishonesty and will result in receiving a failing grade for the quarter.

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