Copy of ACC 331 Module 2 Spreadsheet Exam Chapters 5-6 Solutions Mod

Copy of ACC 331 Module 2 Spreadsheet Exam Chapters 5-6 Solutions Mod

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: ACC 331 Spreadsheet Exam Chapters 5 and 6 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? sales price $60.00 variable cost per unit $40.00 contribution margin per unit $20.00 contribution margin ratio 33.33% b. What is the company's break­even in units and sales dollars? break even in units 15,000 break even in $ $900,000 c. If sales increase by 100 units, by how much should net operating income increase? Units produced and sold Sales Variable expenses Contribution margin Fixed expenses Net operating income 20,100 $1,206,000 $804,000 $402,000 $300,000 $102,000 increases $2,000 You can also solve by just doing the following: CM per unit $20.00 Increase in sales units 100 Income increase $2,000 d. How many units would the company have to sell to attain target profits of $125,000? Assume the original data. Fixed cost target profit contribution margin units $300,000 $125,000 $425,000 21,250 e. What is the company's margin of safety in dollars, assuming the original data selling 20,000 units? break even in $ contribution margin margin of safety $900,000 $400,000 $500,000 f. What is the company's degree of operating leverage using the original data? contribution margin net operating income degree of operating leverage $400,000 $100,000 4.00 2. The Garry Corporation's most recent contribution format income statement is shown below: Sales units Sales dollars Variable expenses Contribution margin Fixed expenses Net Operating income Total 15,000 $225,000 135,000 90,000 35,000 $55,000 Per Unit $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. Sales units Sales dollars Variable expenses Contribution margin Fixed expenses Net Operating income Total Per Unit 16,500 $239,250 $14.50 148,500 9.00 90,750 $5.50 35,000 $55,750 increases $750 b. The selling price decreases $1.00 per unit, fixed expenses increase by $15,000, and the sales volume Sales units Sales dollars Variable expenses Contribution margin Total 14,250 $199,500 128,250 71,250 Per Unit $14.00 9.00 $5.00 Fixed expenses Net Operating income 50,000 $21,250 decreases $33,750 c. The selling price increases by 25%, variable expense increases by $0.75 per unit, and the sales volume Sales units Sales dollars Variable expenses Contribution margin Fixed expenses Net Operating income Total Per Unit 12,750 $239,063 $18.75 124,313 $9.75 114,750 $9.00 35,000 $79,750 increases $24,750 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%. Sales units Sales dollars Variable expenses Contribution margin Fixed expenses Net Operating income Total Per Unit 13,200 $217,800 $16.50 132,000 $10.00 85,800 $6.50 20,000 $65,800 increases $10,800 All but situation b increase net income; situation increase income the most. 3. In July, Meers Corporation sold 3,700 units of its only product. Its total sales were $107,300, its total 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 Total 3,400 Per Unit Sales dollars Variable expenses Contribution margin Fixed expenses Net Operating income $98,600 61,200 37,400 34,800 $2,600 $29.00 $18.00 $11.00 4. The management of Dethlefsen Corporation would like to have a better understanding of the behavior of its inspection costs. The company has provided the following data: Direct Labor Hours 5,089 5,042 5,026 5,073 5,029 5,040 5,070 5,027 4,995 Inspection Cost $33,122 $32,929 $32,870 $33,065 $32,906 $32,913 $33,050 $32,875 $32,746 JAN FEB MAR APR MAY JUN JUL AUG SEP Management believes that inspection cost is a mixed cost that depends on direct labor­hours. Required: a. Using the high low method determine the variable cost per unit, the total fixed cost, and express in a for Cost Hours Highest cost $33,122 5,089 Lowest cost $32,746 4,995 differences $376 94 Variable cost $4.00 per unit variable cost at 5089 hours $20,356 variable cost at 4995 hours $19,980 fixed cost = $12,766 or formula $12,766 + ($4.00*(Expected hours) = Total cost 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. See the worksheet with the excel solution: Formula = is the same, interecept is $12766.52 (fixed cost) and variable 1 is $4 (variable cost per unit) as shown on the excel solution worksheet as R squared = 0.99 This would indicate that 99% of all variance of the data is explained by the model which is extrem Along with this is that the t­value for the variable and intercept are high above 2.0 and the P values are very small numbers well below .05. This means it is highly unlikely that the intercept and variable could be equal to zero. Given all the above the model would be a very good predictor of the inspection cost. t income statement for last month: during the month. unit, and contribution margin ratio? or 33.33% or perating income increase? $900,000 ses $2,000 target profits of $125,000? Proof: Units produced and sold Sales Variable expenses Contribution margin Fixed expenses 21,250 $1,275,000 $850,000 $425,000 $300,000 Net operating income ing the original data selling 20,000 units? $125,000 g the original data? income statement is shown below: each of the following conditions (consider each case independently): ses by $0.50 per unit. ses $750 s increase by $15,000, and the sales volume decreases by 5%. ases $33,750 reases by $0.75 per unit, and the sales volume decreases by 15%. ses $24,750 st increases by $1.00 per unit, fixed expenses ses $10,800 crease income the most. duct. 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. have a better understanding of the behavior following data: hat depends on direct labor­hours. er unit, the total fixed cost, and express in a formula. pected hours) = Total cost $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. t is $12766.52 (fixed cost) and variable 1 is $4.0003 as shown on the excel solution worksheet as high low. data is explained by the model which is extremely high. nd intercept are high above 2.0 and 5. This means it is highly unlikely that the od predictor of the inspection cost. SUMMARY OUTPUT Regression Statistics Multiple R 1 R Square 0.99 Adjusted R Square.99 0 Standard Error 10.69 Observations 9 ANOVA df Regression Residual Total SS MS 1 108787.56 108787.56 7 800 114.29 8 109587.56 F Significance F 951.89 0 X Variabl Residuals 50 -50 4,980 5,000 X Var 33500 Y 32500 5,089 Coefficients Standard Error t Stat P-value Lower 95%Upper 95% Lower 95.0%pper 95.0% U Intercept 12766.52 653.93 19.52 0 11220.22 14312.82 11220.22 14312.82 X Variable 1 4 0.13 30.85 0 3.69 4.31 3.69 4.31 RESIDUAL OUTPUT Observation Predicted Y Residuals Standard Residuals 1 33124.01 -2.01 -0.2 2 32936 -7 -0.7 3 32871.99 -1.99 -0.2 4 33060.01 4.99 0.5 5 32884 22 2.2 6 32928 -15 -1.5 7 33048.01 1.99 0.2 8 32876 -1 -0.1 9 32747.99 -1.99 -0.2 PROBABILITY OUTPUT Percentile 5.56 16.67 27.78 38.89 50 61.11 72.22 83.33 94.44 Y 32746 32870 32875 32906 32913 32929 33050 33065 33122 X Variable 1 Residual Plot Residuals 50 -50 4,980 5,000 5,020 5,040 5,060 5,080 5,100 X Variable 1 X Variable 1 Line Fit Plot 33500 Y 32500 5,042 5,029 5,089 5,026 5,073 5,040 5,070 Column D 5,027 Column B 4,995 X Variable 1 Normal Probability Plot 33500 Y 32500 5. 56 27 .7 8 50 72 .2 2 Column G 94 .4 4 Sample Percentile ...
View Full Document

Ask a homework question - tutors are online