Unformatted text preview: ACC 331 Spreadsheet Exam Chapters 5 and 6
In order to do these problems it is important to know how to use excel regression solution in data analysis You can review the excel regression and analysis in the AVP section and the Recommended Activities section of th 1. The following is Arkadia Corporation's contribution format income statement for last month:
Units produced and sold
Sales
Variable expenses
Contribution margin
Fixed expenses
Net operating income 20,000 $1,200,000 $800,000 $400,000 $300,000 $100,000 The company has no beginning or ending inventories units during the month.
Required:
a. What is the company's sales price and variable cost per unit, and contribution margin ratio? $60 is the selling price per unit. Sales divided by number of units produce $40 is the variable cost per unit. Variable expenses divided by number of b. What is the company's breakeven in units and sales dollars? $20 Is the Contribution Margin per unit. 15,000 Is the number of units to be sold to breakeven. Target profit + Fixed 0.33 Is the Contribution Ratio. Contribution Margin divided by sales. $900,000 Is the Sales Dollar break even. Fixed expenses divided by CM ratio
c. If sales increase by 100 units, by how much should net operating income increase? $402,000 400,000 $2,000 Is the amount increase in operting income on additional 100 units. d. How many units would the company have to sell to attain target profits of $125,000? Assume the original data. 21,250 Units would need to be sold to obtain a profit of $125,000. Units that e. What is the company's margin of safety in dollars, assuming the original data selling 20,000 units? $300,000 Is the Margin of Safety in dollars. Margin of Safety in dollars=actual s f. What is the company's degree of operating leverage using the original data? 4 Is the degree of operating leverage. Degree of operating leverage=C
2. The Garry Corporation's most recent contribution format income statement is shown below:
Total Per Unit Sales units
Sales dollars
Variable expenses
Contribution margin
Fixed expenses
Net Operating income 15,000 $225,000 135,000 90,000 35,000 $55,000 $15.00 9.00 $6.00 Required:
Prepare a new contribution format income statement under each of the following conditions (consider eac
a. The sales volume increases by 10% and the price decreases by $0.50 per unit.
15000 Units X 1.10% = Sales units
Sales dollars
Variable expenses
Contribution margin
Fixed expenses
Net Operating income 16,500 Total 16,500 $239,250 148,500 90,750 35,000 $55,750 Per Unit $14.50 9.00 $5.50 b. The selling price decreases $1.00 per unit, fixed expenses increase by $15,000, and the sales volume Sales Volume = 15,000 X .95 Sales units
Sales dollars
Variable expenses
Contribution margin
Fixed expenses
Net Operating income 13,537.50 Total 14,250 $199,500 128,250 71,250 50,000 $21,250 Per Unit $14.00 9.00 $5.00 c. The selling price increases by 25%, variable expense increases by $0.75 per unit, and the sales volume
Selling Price per unit X 1.25% =
Unit sales 15,000 X .85 = Sales units
Sales dollars
Variable expenses
Contribution margin
Fixed expenses
Net Operating income 18.75 12,750 Total 12,750 $239,063 124,313 114,750 35,000 $79,750 Per Unit
18.75 9.75 $9.00 d. The selling price increases by $1.50 per unit, variable cost increases by $1.00 per unit, fixed expenses decrease by $15,000, and sales volume decreases by 12%. Selling price per unit 15 +1.50=
unit sales 15000 x .88 = Sales units
Sales dollars
Variable expenses
Contribution margin
Fixed expenses
Net Operating income $16.50 13,200 Total 13,200 $217,800 132,000 85,800 20,000 $65,800 Per Unit $16.50 10.00 $6.50 3. In July, Meers Corporation sold 3,700 units of its only product. Its total sales were $107,300, its t variable expenses were $66,600, and its total fixed expenses were $34,800.
Required:
a. Construct the company's contribution format income statement for July in good form. Sales units
Sales dollars
Variable expenses
Contribution margin
Fixed expenses
Net Operating income Total 3,700 $107,300 66,600 40,700 34,800 $5,900 Per Unit $29.00 18.00 $11.00 b. Redo the company's contribution format income statement assuming that the company sells 3,400 units Sales units
Sales dollars
Variable expenses
Contribution margin
Fixed expenses
Net Operating income Total 3,400 $98,600 61,200 37,400 34,800 $2,600 Per Unit $29.00 18.00 $11.00 4. The management of Dethlefsen Corporation would like to have a better understanding of the beh of its inspection costs. The company has provided the following data:
Direct Labor
Inspection
Hours
Cost
JAN 5,089 $33,122 FEB 5,042 $32,929 MAR 5,026 $32,870 APR 5,073 $33,065 MAY 5,029 $32,906 JUN 5,040 $32,913 JUL 5,070 $33,050 AUG 5,027 $32,875 SEP 4,995 $32,746 Management believes that inspection cost is a mixed cost that depends on direct laborhours.
Required:
a. Using the high low method determine the variable cost per unit, the total fixed cost, and express in a for
high level cost low level cost
= variable cost
high level actvtylow level actvty
Fixed cost element = total cost variable cost element
Total cost = $12,766 + ($4 X # of labor hours) b. Using the regression analysis determine the variable cost per unit, the total fixed cost, and express in a Explain what the regression analysis printout for this problem, tells you about the independent and dep and the fixed cost or intercept. How good of a predictor is the model and how did you determine this. JAN
FEB
MAR
APR
MAY
JUN
JUL Direct Labor
Hours
X 5,089 5,042 5,026 5,073 5,029 5,040 5,070 Inspection
Cost
Y $33,122 $32,929 $32,870 $33,065 $32,906 $32,913 $33,050 AUG
SEP a) INTERCEPT:
SLOPE:
RSQ: 5,027 4,995 12766.52
4
0.99 $32,875 $32,746 The total fixed cost is $12,766.52
The variable cost per unit is $4.00
The RSQ is essentialy 1, the highest value you can achiev
that signifies that 99% of the variation in inspection costs c) The formula in generic terms is Y= a + bX. The formula specifc to this problem is: Y = $12,766.52 + $4 (the n d) The graph printout shows a pretty straight line. And all points show a close proximation to it. I think this su use excel regression solution in data analysis.
tion and the Recommended Activities section of the Module’s GO TO menu. ormat income statement for last month: during the month.
unit, and contribution margin ratio? unit. Sales divided by number of units produced and sold
r unit. Variable expenses divided by number of units produced and sold
rgin per unit. to be sold to breakeven. Target profit + Fixed expenses divided by Contribution Margin tio. Contribution Margin divided by sales.
ak even. Fixed expenses divided by CM ratio
perating income increase? in operting income on additional 100 units. target profits of $125,000?
e sold to obtain a profit of $125,000. Units that need to besold= (Target Profit + fixed expense)/unit CM
ing the original data selling 20,000 units? in dollars. Margin of Safety in dollars=actual salesbreak even sales
g the original data? ting leverage. Degree of operating leverage=CM/net operating income
mat income statement is shown below: each of the following conditions (consider each case independently):
ses by $0.50 per unit. s increase by $15,000, and the sales volume decreases by 5%. reases by $0.75 per unit, and the sales volume decreases by 15%. st increases by $1.00 per unit, fixed expenses product. Its total sales were $107,300, its total nses were $34,800.
ement for July in good form. nt assuming that the company sells 3,400 units. e to have a better understanding of the behavior the following data: Highest cost Lowest cost hat depends on direct laborhours.
er unit, the total fixed cost, and express in a formula. $33,122 $32,746 5089 4995 FCE = 33,122  (4 X 5089) = = $376.00
94 = $4.00 $12,766 t per unit, the total fixed cost, and express in a formula. blem, tells you about the independent and dependent variable, is the model and how did you determine this.
b) $33,200
$33,100
$33,000
$32,900
Column D $32,800
$32,700
$32,600
$32,500
4,980 5,000 5,020 5,040 5,060 5,080 5,100 $32,900
Column D $32,800
$32,700
$32,600
$32,500
4,980 5,000 5,020 5,040 5,060 5,080 tal fixed cost is $12,766.52
riable cost per unit is $4.00
SQ is essentialy 1, the highest value you can achieve indicating an extremely high correlation
gnifies that 99% of the variation in inspection costs is explained by direct labor hours.
specifc to this problem is: Y = $12,766.52 + $4 (the number of direct labor hours).
points show a close proximation to it. I think this suggests the data is reliable. 5,100 ense)/unit CM Column D 5,100 Column D 5,100 ...
View
Full Document
 Spring '11
 Hiles
 Accounting, Arkadia Corporation, The Garry Corporation, 33,122

Click to edit the document details