Acc 306 Quantitative Methods Project

Cost Behavior Analysis using Regression

GENERAL INSTRUCTIONS

Data should be analyzed in Excel.Everything should be appropriately labeled.

1.Simple Regression:Examine the relationship between the number of units produced and the total manufacturing overhead by performing Regression Analysis and generating a "Line-fit" plot.Consider the following in your analysis:

a.What is the coefficient of correlation and what does it say about the relationship?

b.What is the coefficient of determination and what does it say about the relationship?

c.What is the equation of this line?( y = mx + b), where Total Cost = (Variable CostPer Unit * Units) + Total Fixed Cost.

2.Multiple Regression:Examine the relationship between the number of units produced and the number of production batches and their effect on total manufacturing overhead.Consider the following in your report:

a.What is the coefficient of determination?How did multiple (versus single) regression help/hurt the r-squared?

b.What is the estimated Variable Manufacturing Overhead Per Unit?

c.What is the estimated Total Fixed Manufacturing Overhead?

3.Break-Even Analysis:Calculate the following:

a.What is the average per Unit Direct Materials Cost?

b.What is the average per Unit Direct Labor Cost?

c.Assume variable General and Administrative expenses are $12 per unit.What is the total variable cost per unit?

d.Assume total fixed General and Administrative expenses are $2,000 per month.What is the total fixed cost per month?

e.Assume sales price per unit is $400.What is the Contribution Margin Per Unit?

f.What is the Break Even Point in Units?

Data for Acct. 306 Assessment ProjectSpring 2017 Queens Lamps Inc specializes in manufacturing custom designed lamps. It is a low volume manufacturer. Its cost data for four years is as follows. Sales Price Per Unit: $ 400.00 Variable Selling and General Expenses per unit Monthly Fixed Selling and General Expenses Observation Number 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 Month Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Year 20X1 20X1 20X1 20X1 20X1 20X1 20X1 20X1 20X1 20X1 20X1 20X1 20X2 20X2 20X2 20X2 20X2 20X2 20X2 20X2 20X2 20X2 20X2 20X2 $ $ 12.00 2,000 Number of Direct Production Units Manufacturing Machine Material Batches Produced Overhead Hours Cost 8 121 10,991 1,557 3,703 4 103 9,724 1,259 3,059 5 82 9,830 970 2,411 7 123 11,006 1,631 3,653 10 95 10,413 1,235 2,850 5 100 10,403 1,391 2,970 11 125 11,307 1,479 3,750 5 68 9,716 955 2,060 3 130 10,310 1,808 3,822 6 80 9,622 988 2,448 4 124 10,237 1,483 3,683 10 128 11,120 1,597 3,802 5 121 10,311 1,715 3,630 7 71 10,103 951 2,173 7 122 10,777 1,507 3,587 10 35 9,822 473 1,050 4 116 10,527 1,493 3,480 8 44 9,331 578 1,294 11 71 10,697 886 2,087 6 57 9,767 771 1,727 10 56 10,241 692 1,697 5 73 9,695 940 2,146 6 128 10,746 1,498 3,802 9 114 10,790 1,615 3,420 w volume manufacturer. Direct Labor Cost 4,646 4,408 3,674 4,723 4,028 4,280 5,750 2,530 4,992 3,520 4,613 5,530 4,453 3,039 4,197 1,330 4,083 2,006 2,698 2,440 2,419 3,066 5,171 4,697 Acc 306 Quantitative Methods Project Cost Behavior Analysis using Regression GENERAL INSTRUCTIONS Data should be analyzed in Excel. Everything should be appropriately labeled. 1. Simple Regression: Examine the relationship between the number of units produced and the total manufacturing overhead by performing Regression Analysis and generating a \"Line-fit\" plot. Consider the following in your analysis: a. What is the coefficient of correlation and what does it say about the relationship? b. What is the coefficient of determination and what does it say about the relationship? c. What is the equation of this line? ( y = mx + b), where Total Cost = (Variable Cost Per Unit * Units) + Total Fixed Cost. 2. Multiple Regression: Examine the relationship between the number of units produced and the number of production batches and their effect on total manufacturing overhead. Consider the following in your report: a. What is the coefficient of determination? How did multiple (versus single) regression help/hurt the r-squared? b. What is the estimated Variable Manufacturing Overhead Per Unit? c. What is the estimated Total Fixed Manufacturing Overhead? 3. Break-Even Analysis: Calculate the following: a. What is the average per Unit Direct Materials Cost? b. What is the average per Unit Direct Labor Cost? c. Assume variable General and Administrative expenses are $12 per unit. What is the total variable cost per unit? d. Assume total fixed General and Administrative expenses are $2,000 per month. What is the total fixed cost per month? e. Assume sales price per unit is $400. What is the Contribution Margin Per Unit? f. What is the Break Even Point in Units