Data
for Acct. 306 Assessment Project—Spring 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 $12.00
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
Acc 306 Quantitative Methods Project
Cost Behavior Analysis using Regression
GENERAL INSTRUCTIONS
Data should be analyzed in Excel. Everything should be
appropriately labeled.
1. 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?