1) CREATE A SCATTERPLOT OF THE OBSERVED NUMBER OF UNITS PRODUCED AND THE RELATED MANUFACTURING OVERHEAD....

Transcribed Image Text:

1) CREATE A SCATTERPLOT OF THE OBSERVED NUMBER OF UNITS PRODUCED AND THE RELATED MANUFACTURING OVERHEAD. 2) FIT THE REGRESSION LINE TO THE SCATTERPLOT. DISPLAY THE EQUATION OF THAT LINE AND ALSO THE R-SQUARED ON THE SCATTERPLOT. 3) USING SIMPLE REGRESSION, USE THE REGRESSION TOOL UNDER 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. 4) USING THE RESULT OF QUESTION # 3, WHAT IS YOUR ESTIMATE OF MANUFACTURING OVERHEAD IF 150 UNITS WERE TO BE PRODUCED? 5) HOW WOULD YOU ASSESS THE QUALITY OF THE RELATIONSHIP IN QUESTION # 3 BASED ON THE VALUE OF R-SQUARED? 6) USE OF BREAKEVEN ANALYSIS TECHNIQUES DETERMINE THE FOLLOWING DATE: A) AVERAGE VALUE FOR DM COST/UNIT AND DL COST/UNIT. B) VARIABLE MANUFACTURING OVERHEAD COST/UNIT. TOTAL FIXED MANUFACTURING OVERHEAD COST FROM THE SIMPLE REGRESSION ANALYSIS. D) 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 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 Month Jan Feb Mar Apr May Jun Jul 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Units Year Produced 20X1 20X1 20X1 20X1 20X1 20X1 20X1 20X1 20X1 20X1 20X1 20X1 20X2 20X2 20X2 20X2 20X2 20X2 20X2 20X2 20X2 20X2 20X2 20X2 121 103 82 123 95 100 125 68 130 80 124 128 121 71 122 35 116 44 71 57 56 73 128 114 Number of Production Batches 8 4 5 7 10 5 11 5 3 6 4 10 5 7 7 10 4 8 11 6 10 5 6 9 $ 12.00 $ 2,000 Machine Hours 1,557 1,259 970 1,631 1,235 1,391 1,479 955 1,808 988 1,483 1,597 1,715 951 1,507 473 1,493 578 886 771 692 940 1,498 1,615 Direct Material Direct Labor Cost Cost 3,703 3,059 2,411 3,653 2,850 2,970 3,750 2,060 3,822 2,448 3,683 3,802 3,630 2,173 3,587 1,050 3,480 1,294 2,087 1,727 1,697 2,146 3,802 3,420 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 Manufacturing Overhead 10,991 9,724 9,830 11,006 10,413 10,403 11,307 9,716 10,310 9,622 10,237 11,120 10,311 10,103 10,777 9,822 10,527 9,331 10,697 9,767 10,241 9,695 10,746 10,790 1) CREATE A SCATTERPLOT OF THE OBSERVED NUMBER OF UNITS PRODUCED AND THE RELATED MANUFACTURING OVERHEAD. 2) FIT THE REGRESSION LINE TO THE SCATTERPLOT. DISPLAY THE EQUATION OF THAT LINE AND ALSO THE R-SQUARED ON THE SCATTERPLOT. 3) USING SIMPLE REGRESSION, USE THE REGRESSION TOOL UNDER 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. 4) USING THE RESULT OF QUESTION # 3, WHAT IS YOUR ESTIMATE OF MANUFACTURING OVERHEAD IF 150 UNITS WERE TO BE PRODUCED? 5) HOW WOULD YOU ASSESS THE QUALITY OF THE RELATIONSHIP IN QUESTION # 3 BASED ON THE VALUE OF R-SQUARED? 6) USE OF BREAKEVEN ANALYSIS TECHNIQUES DETERMINE THE FOLLOWING DATE: A) AVERAGE VALUE FOR DM COST/UNIT AND DL COST/UNIT. B) VARIABLE MANUFACTURING OVERHEAD COST/UNIT. TOTAL FIXED MANUFACTURING OVERHEAD COST FROM THE SIMPLE REGRESSION ANALYSIS. D) 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 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 Month Jan Feb Mar Apr May Jun Jul 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Units Year Produced 20X1 20X1 20X1 20X1 20X1 20X1 20X1 20X1 20X1 20X1 20X1 20X1 20X2 20X2 20X2 20X2 20X2 20X2 20X2 20X2 20X2 20X2 20X2 20X2 121 103 82 123 95 100 125 68 130 80 124 128 121 71 122 35 116 44 71 57 56 73 128 114 Number of Production Batches 8 4 5 7 10 5 11 5 3 6 4 10 5 7 7 10 4 8 11 6 10 5 6 9 $ 12.00 $ 2,000 Machine Hours 1,557 1,259 970 1,631 1,235 1,391 1,479 955 1,808 988 1,483 1,597 1,715 951 1,507 473 1,493 578 886 771 692 940 1,498 1,615 Direct Material Direct Labor Cost Cost 3,703 3,059 2,411 3,653 2,850 2,970 3,750 2,060 3,822 2,448 3,683 3,802 3,630 2,173 3,587 1,050 3,480 1,294 2,087 1,727 1,697 2,146 3,802 3,420 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 Manufacturing Overhead 10,991 9,724 9,830 11,006 10,413 10,403 11,307 9,716 10,310 9,622 10,237 11,120 10,311 10,103 10,777 9,822 10,527 9,331 10,697 9,767 10,241 9,695 10,746 10,790

Answer rating: 100% (QA)

Solution Given that the following information about 1 Required scatter plot of the observed number o... View the full answer