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

Morrisey & Brown 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

Morrisey and Brown

Income Statement

For the Three Months Ending September

	July	August	September
Sales in Units	4,000	4,500	5,000
Sales Revenue	$400,000	$450,000	$500,000
Cost of Goods Sold	265,000	295,000	325,000
Gross Margin	135,000	155,000	175,000
Selling and Administrative Expenses:
Administrative Expense	14,000	14,000	14,000
Shipping Expense	20,000	22,500	25,000
Salaries and Commissions	78,000	84,000	90,000
Insurance Expense	6,000	6,000	6,000
Depreciation Expense	9,000	9,000	9,000
Total Selling & Administrative Expenses	133,000	141,000	150,000
Operating Income	$2,000	$13,000	$25,000

Identify each of the companys individual expenses (both product and period) as either a variable, fixed or mixed cost. For the FC list the total fixed costs each month, for each fixed cost. For VC list the variable cost per unit for each month, for each variable cost. For MC list the average cost per unit for each mixed cost per month.

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

Morrisey and Brown expect to produce and sell 6,000 units in October. Prepare an absorption income statement for October. Do not combine expenses but show each expense separately in the appropriate category.

Prepare a Contribution Margin Income Statement based on October sales of 6,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 $75,000 in target income? (Round your answer to the nearest unit using the Excel Round Up function.)

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

Morrisey and Brown are considering a multimedia advertising campaign that should increase sales by $25,000 per month. The ad campaign will cost and additional $7,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.) Morrisey and Brown expect to sell 6,000 units in October. Prepare an absorption income statement for October (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

6,000

Sales Revenue

Operating Income

4) Prepare a Contribution Margin Income Statement based on the October sales of 6,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

6,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 $75,000 in target income.

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

Target Income

$50,000

7) Give one example of how Morrisey and Brown could increase projected operating income without increasing total sale revenue.

8) Morrisey and Brown are considering a multimedia advertising campaign that should increase sales by $25,000 per month. The ad campaign will cost an additional $7,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.

PLEASE SHOW ALL FORMULAS