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. Highlight the following in YELLOW on the spreadsheet:

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. What is the coefficient of determination? How did multiple (versus single) regression help/hurt the r-squared?

3. Break-Even Analysis: Calculate the following, assuming Simple Regression (show work in the Break-Even tab):

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?

4. After completing the spreadsheet above, take the Regression Quiz on BlackBoard. Upload the regression file to BlackBoard.

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

Variable Selling and General Expenses per unit 12

Monthly Fixed Selling and General Expenses 2,000

Observation Number

Month Year

Number of Production Batches

Units Produced

Manufacturing Overhead

Machine Hours

Direct Material Cost

Direct Labor Cost

1 Jan 20X1 8 121 10,991 1,557 3,703 4,646

2 Feb 20X1 4 103 9,724 1,259 3,059 4,408

3 Mar 20X1 5 82 9,830 970 2,411 3,674

4 Apr 20X1 7 123 11,006 1,631 3,653 4,723

5 May 20X1 10 95 10,413 1,235 2,850 4,028

6 Jun 20X1 5 100 10,403 1,391 2,970 4,280

7 Jul 20X1 11 125 11,307 1,479 3,750 5,750

8 Aug 20X1 5 68 9,716 955 2,060 2,530

9 Sep 20X1 3 130 10,310 1,808 3,822 4,992

10 Oct 20X1 6 80 9,622 988 2,448 3,520

11 Nov 20X1 4 124 10,237 1,483 3,683 4,613

12 Dec 20X1 10 128 11,120 1,597 3,802 5,530

13 Jan 20X2 5 121 10,311 1,715 3,630 4,453

14 Feb 20X2 7 71 10,103 951 2,173 3,039

15 Mar 20X2 7 122 10,777 1,507 3,587 4,197

16 Apr 20X2 10 35 9,822 473 1,050 1,330

17 May 20X2 4 116 10,527 1,493 3,480 4,083

18 Jun 20X2 8 44 9,331 578 1,294 2,006

19 Jul 20X2 11 71 10,697 886 2,087 2,698

20 Aug 20X2 6 57 9,767 771 1,727 2,440

21 Sep 20X2 10 56 10,241 692 1,697 2,419

22 Oct 20X2 5 73 9,695 940 2,146 3,066

23 Nov 20X2 6 128 10,746 1,498 3,802 5,171

24 Dec 20X2 9 114 10,790 1,615 3,420 4,697