ACC 2362 Managerial Accounting: Excel Project #2 Chapters 6 & 7 - Cost Behavior, High-Low Method, Contribution Margin Income Statement, and Break-Even Analysis

Read these instructions completely before you begin the Excel project.

Academic Honesty: The project should reflect your individual work. This is NOT a group project sharing answers or spreadsheet formats is considered academic dishonesty. If you use a spreadsheet from a prior semester or someone elses spreadsheet, you will receive a zero for the project and an honor code violation will be filed.

Due date: Monday, March 6th at the START of your class period or earlier. If you arrive more than 10 minutes late to class to turn in your project, there will be a 25% penalty. If you turn it in after class has ended, you will NOT receive credit for the assignment.

Deliverables: Attach a copy of the cover sheet to a printed copy of the completed Excel spreadsheet which will be turned in during your class period. An electronic copy of the Excel spreadsheet only (no cover sheet) will be submitted to TRACS via the Drop Box function in accordance with the due date and time stated above. You will not receive credit unless both the printed copy and electronic copy are turned in by the due date. No hand written assignments or e-mail attachments will be accepted.

Specific Directions: This assignment will not be done through MyAccountingLab. It must be completed using Excel, a spreadsheet application.

Use the Excel Project #2 S17 template as your starting point. Remember not to use a spreadsheet from a prior semester or someone elses spreadsheet or you will receive a zero for the project.

The data included in the template will serve as your data source for all your other cells in Excel. Everything you add in the template should either be a cell reference or an Excel formula. No hard-coded numbers.

Add page numbers within Excel if your assignment is more than one page.

Print portrait on only one side of the page (no double-sided).

Review the Excel Instructions and Hints posted for the first Excel Project.

Grading: Assignment is worth 10 points. Presentation of the information and how easily it can be read will be considered in the grading of the project.

6 points will be for the content, accuracy of the calculations, and completeness.

4 points will be for the use of technology and communication of data.

Lastly, remember, the project is due at the START of your class period. There is normally a back up in the computer lab on the due date with students printing the assignment at the last hour so dont be caught. Remember, there are penalties associated with late work. Dont wait until the last minute to work on it, print out the results to review it for accuracy.

ACC 2362 Managerial Accounting: Excel Project #2 Chapters 6 & 7 - Cost Behavior, High-Low Method, Contribution Margin Income Statement, and Break-Even Analysis

Edwards & Shaw is a merchandising company that is the sole distributor of a product that is increasing in popularity. The companys income statement for the three most recent months is listed below.

Edwards and Shaw

Income Statement

For the Three Months Ending September

	July	August	September
Sales in Units	5,000	4,000	6,500
Sales Revenue	$625,000	$500,000	$812,500
Cost of Goods Sold	265,000	220,000	332,500
Gross Margin	360,000	280,000	480,000
Operating Expenses:
Advertising Expense	5,000	5,000	5,000
Shipping Expense	20,000	16,000	26,000
Salaries and Commissions	75,000	70,000	82,500
Legal Expense	3,000	3,000	3,000
Depreciation Expense	9,000	9,000	9,000
Total Operating Expenses	112,000	103,000	125,500
Operating Income	$248,000	$177,000	$354,500

Identify each of the companys individual expenses (both product and period) as either a variable, fixed or mixed cost.

Using the high-low method, separate each of the individual mixed cost into the variable rate and fixed cost elements. State the cost equation for each individual mixed costs.

Edwards and Shaw expect to produce and sell 7,000 units in October. Prepare an absorption income statement for October.

Prepare a Contribution Margin Income Statement based on October sales of 7,000 units. Do not combine expenses but show each expense separately in the appropriate category.

Calculate the contribution margin per unit and the variable cost ratio.

How many units would need to be sold to generate $50,000 in target income? (Round your answer to the nearest unit using the Excel Round Up function.)

Give one example of how Edwards and Shaw could increase projected operating income without increasing total sales revenue.

Edwards and Shaw are considering a multimedia advertising campaign that should increase sales by $45,000 per month. The ad campaign will cost an additional $2,500 per month and will be considered a fixed cost. How will the ad campaign affect product cost? How will the increase in fixed costs affect the break-even point? Explain.

1) Identify each of the company's expenses as either variable, fixed, or mixed. For the FC list the total fixed costs per month for each month. For VC list the variable cost per unit for each month. For MC list the average cost per unit for each mixed cost per month.

July

August

September

Fixed Costs

Variable Costs

Mixed Costs

2) Using the high-low method, separate each of the mixed expenses into variable and fixed elements. State the cost equation for each mixed cost. You may have more than one mixed cost.

Variable Rate

Fixed Cost

Cost Formula

3. Edwards and Shaw expect to sell 7,000 units in October. Prepare an absorption income statement for October (assume we produce and sell the same number of units).

Sales in Units

7,000

Sales Revenue

Operating Income

4) Prepare a Contribution Margin Income Statement based on the October sales of 7,000 units (assume we produce and sell the same number of units.) Do not combine expenses but show each expense separately in the appropriate category.

Sales in Units

7,000

Sales Revenue

Operating Income

5) Calculate the contribution margin per unit. Calculate the variable cost ratio.

6) Calculate how many units would need to be sold to generate $50,000 in target income.

Round up to the nearest unit using the Excel Round Up function.

Target Income

$50,000

\\