1. Assessing computer literacy: For purposes of assessing computer literacy , use the
accompanying data as directed below.
Graphical analysis:
a. Create a scatterplot of the observed number of units produced and the related
manufacturing overhead.
b. Fit a regression line to the scatterplot.
c. Display the equation of that line and also r-squared on the scatterplot.
d. Simple regression: Use the Regression tool under the add-in ?Data Analysis? of
Excel to generate a spreadsheet of output data with ?UNITS PRODUCED? as the
INDEPENDENT variable and ?MANUFACTURING OVERHEAD? as the
DEPENDENT variable.
e. Multiple regression: Use the Regression tool to determine a regression equation
using the following two independent variables?(1) ?units produced? and (2)
?number of production batches? and using manufacturing overhead as the
DEPENDENT variable.
2. Assessing critical thinking: For purposes of assessing critical thinking, please answer the
following questions:
a. What kind of relationship does the scatterplot show between the variables identified
in 1(a)?
b. Using the result of question 1(d), what is your estimate of manufacturing overhead if
150 units were to be produced?
c. How would you describe the quality of the relationship in part (a) based on the value
of r-squared?
d. After doing 1(e), do you believe that adding a second independent variable, ?number
of production batches? to the regression analysis improves the resulting regression
estimate compared to the result obtained using the simple regression equation that
you computed by using just one independent variable, ?number of units produced??
Explain the reason for your answer from the Regression tool output.
e. DETERMINE THE FOLLOWING DATA: (1) average values for DM cost/unit and
DL cost/unit and (2) the VARIABLE manufacturing overhead cost/unit and the total
FIXED manufacturing overhead cost from the SIMPLE regression analysis. Once you
have the data, calculate the BREAKEVEN POINT IN UNITS by inserting a formula
into a cell on your Excel spreadsheet.
Final Version of Acct. 306 Assessment ProjectSpring 2017 1. Assessing computer literacy: For purposes of assessing computer literacy , use the accompanying data as directed below. Graphical analysis: a. Create a scatterplot of the observed number of units produced and the related manufacturing overhead. b. Fit a regression line to the scatterplot. c. Display the equation of that line and also r-squared on the scatterplot. d. Simple regression: Use the Regression tool under the add-in \"Data Analysis\" of Excel to generate a spreadsheet of output data with \"UNITS PRODUCED\" as the INDEPENDENT variable and \"MANUFACTURING OVERHEAD\" as the DEPENDENT variable. e. Multiple regression: Use the Regression tool to determine a regression equation using the following two independent variables(1) \"units produced\" and (2) \"number of production batches\" and using manufacturing overhead as the DEPENDENT variable. 2. Assessing critical thinking: For purposes of assessing critical thinking, please answer the following questions: a. What kind of relationship does the scatterplot show between the variables identified in 1(a)? b. Using the result of question 1(d), what is your estimate of manufacturing overhead if 150 units were to be produced? c. How would you describe the quality of the relationship in part (a) based on the value of r-squared? d. After doing 1(e), do you believe that adding a second independent variable, \"number of production batches\" to the regression analysis improves the resulting regression estimate compared to the result obtained using the simple regression equation that you computed by using just one independent variable, \"number of units produced?\" Explain the reason for your answer from the Regression tool output. e. DETERMINE THE FOLLOWING DATA: (1) average values for DM cost/unit and DL cost/unit and (2) the VARIABLE manufacturing overhead cost/unit and the total FIXED manufacturing overhead cost from the SIMPLE regression analysis. Once you have the data, calculate the BREAKEVEN POINT IN UNITS by inserting a formula into a cell on your Excel spreadsheet. Queens Lamps Inc specializes in manulaeturing custom designed lamps. It is a low volume manufacturer. Its cost data for four years is as follows. Sales Price Per Unit: $400.00 1|niariable Selling and General Expenses per unit 5 12.00 Monthly Fixed Selling and General Expenses 5 2,000 Number of Direct Observation Units Production Machine Material Direct Manutacturing Number Month Year Produced Batches Hours Cost Labor Cost Overhead 1 Jan 20K1 11? 8 1,541 3,554 4,238 10,991 2 Feb 20K1 101 4 1,321 3,059 4,231 9,529 3 Mar 20K1 84 4 931 2,50? 3,85? 9,928 4 Apr 2011 118 ? 1,565 3,543 4,581 10,625 5 M6152 20K1 90 9 1,284 2,992 3,982 10,829 6 Jun 20K1 9? 5 1,446 2,920 4,322 10,298 ? Jul 20K1 126 11 1,464 3,900 5,635 10,854 8 Aug 20K1 65 5 945 1,92? 2,504 10,201 9 Sep 2011 132 3 1,808 3,936 5,241 10,310 10 Oct 20K1 28 6 1,03? 2,545 3,414 9,333 11 NEW 20K1 125 3 1,49? 3,498 4,29? 9,929 12 Dec 20K1 130 10 1,59? 3,649 5,530 11,564 13 Jan 20x2 116 5 1,266 3,666 4,492 10,223 14 Feb 20):: 23 6 998 2,064 3,190 10,103 15 Mar 20x2 119 6 1,53? 3,622 4,021 11,315 16 Apr 20x2 36 9 482 1,102 1,330 9,920 1? M6152 20x2 120 3 1,522 3,584 3,919 11,053 18 Jun 20x2 42 ? 549 1,242 2,026 9,23? 19 Jul 20x2 68 11 8?? 2,128 2,644 10,483 20 Aug 20x2 5? 6 240 1,22? 2,318 10,152 21 Sep 20x2 5? 10 205 1,646 2,322 10,650 22 Oct 20x2 20 5 949 2,253 3,004 9,291 23 NEW 20x2 129 6 1,542 3,840 5,322 11,283 24 Dec 20x2 115 9 1,550 3,420 4,290 10,290 qattachments_2ef8851754936b65f8adfc3786ac6582905c71a2 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: 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 Page 1 qattachments_2ef8851754936b65f8adfc3786ac6582905c71a2 $400.00 $12.00 $2,000 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 Units Produced Number of Production Batches Machine Hours 117 8 1541 101 4 1321 84 4 931 118 7 1565 90 9 1284 97 5 1446 126 11 1464 65 5 945 132 3 1808 78 6 1037 125 3 1497 130 10 1597 116 5 1766 73 6 998 119 6 1537 36 9 482 120 3 1522 42 7 549 68 11 877 57 6 740 57 10 705 70 5 949 129 6 1542 115 9 1550 Page 2 qattachments_2ef8851754936b65f8adfc3786ac6582905c71a2 Direct Material Cost Direct Labor Cost Manufacturing Overhead 3554 4738 10991 3059 4231 9529 2507 3857 9928 3543 4581 10675 2992 3987 10829 2970 4322 10298 3900 5635 10854 1977 2504 10201 3936 5241 10310 2545 3414 9333 3498 4797 9929 3649 5530 11564 3666 4497 10723 2064 3190 10103 3622 4071 11315 1102 1330 9920 3584 3919 11053 1242 2026 9237 2128 2644 10483 1727 2318 10157 1646 2322 10650 2253 3004 9791 3840 5377 11283 3420 4790 10790 Page 3
