ࡱ> sur{ ?bjbjzz ;x&""|||||8L3.88HHH###3333333$4}7~B3| ##  B3||HHW3 F|H|H3 3,b.HP PF-3m303-7 7(b.b.7|/#"E]q###B3B3 ###3    7#########" B: Acct 2220 Zeigler: P2-27A - Using Excel for Regression Analysis (Pg 89) When analyzing the costs of a business, certain costs may include both fixed and variable components. This type of cost is known as a mixed cost. If we do not know the breakdown of these cost components, we can analyze the data and estimate (i.e. predict) what portion of a mixed cost is fixed and what portion is variable. Problem P2-27A will be used to examine the three approaches to cost estimation as presented in Chapter 2. In addition to the original P2-27A assign using the Scattergraph and High-Low methods, we have another choice. We can also perform Regression Analysis (aka: Least Squares Regression) on the same data with Excel. Regression is a statistical averaging technique that considers historical data observations and can help management predict future cost expectations. See page 74 of our text. Per pg 74, our focus will be on the interpretation of the following summary output items: Intercept: Represents the estimated fixed cost component of our cost formula equation. X Variable 1: Represents the estimated variable cost component of our cost formula. R Square (Coefficient of Determination): A statistical measure indicating the correlation (closeness of the relationship) of the data being analyzed. How accurate is the historical data in predicting the future? Does the data support a reasonable, consistent pattern of behavior? Do changes in X (activity) really cause (explain) changes in Y (total cost)? We can use regression to create Cost Formulas that help management predict future costs. In this case, we wish to predict the fixed and variable components of a series of total (mixed) cost observations. This formula can be written as: Y (total predicted cost) = a (some fixed cost) + b (some variable cost per unit)* X (some activity level). Therefore, our cost prediction formula would be: Y = a + b(X) See page 74 footnote B) Step by Step approach to complete Regression work in Excel: 1) Before starting, read pages 70-75 for an understanding of the topic at hand. Then, review the original assignment, on page 89, before proceeding. Next, open the posted Excel worksheet and use the data from P2-27A in adjacent columns (see format on reverse page). Add labels so others can understand your work. Once you have completed this, click the Data tab on the top toolbar, then find Data Analysis. Choose Regression to start the process. Perform Regression: Now, enter the X and Y cell-ranges of your numerical data (only) into the regression dialog box. Leave all other check boxes as is (i.e. default). Next, enter a cell address in the dialog box where you want your output to be placed (output range) on your worksheet. Click OK to run the regression and review the output results. From the output, create a cost formula for predicting future costs in a Y=a+b(X) format (see above). How does this compare with the predictions you created with the High-Low and Scattergraph methods in the original P2-27A problem? Review the regression discussion in the text for an interpretation of Intercept, X Variable 1 and R Square Next, lets create a graph of the data. First, highlight the X and Y range of data (include the column headers as well). Then, click on the Insert tab and choose Scatter chart. Choose the basic (first) scatter chart without lines. Under Chart Tools, choose the Design drop-down menu from the toolbar. Notice the many pre-created designs that you could use. Next, select Layout and choose Trendline. Add a Linear Trendline. This line represents the basis for your Y = A + b(X) prediction equation (i.e. the regression line). Last, click on the trendline you created and then right-mouse click to obtain the Format Trendline dialog box. Select the two check boxes at the bottom relating to Display Equation and R-squared on the chart. Both should now show on the chart. Widen your chart and notice how some points are fairly far from the trendline. These are most likely Outlier data points and could possibly be eliminated to get a better prediction (and therefore, R-Squared) of the relationship between X (level of activity) causing Y (total cost). Clean up the chart, making sure to include the original data, regression output and your chart all on a single worksheet. Add your name and class period (after adding a text box) anywhere on your sheet and print for collection. See the suggested Excel format on the next page SUGGESTED WORKSHEET FORMAT (Prepare in Excel) Quinton Woodcrafting Company (QWC) - Predicting future cabinetry costs Adding Regression to Problem 2-27A - page 89A) INPUT Number of CabinetsTotalProducedCostMonth(X)(Y)Jan800$21,000FebMarAprMayJunJulAugSepOctNovDecB) OUTPUT: PLACE REGRESSION OUTPUT BELOW C) YOUR CHART: Add labels, etc. and be sure it makes sense to the reader without your need to interpret it.     PAGE   7?EFGHINSXeoq 5 = > D K M ] e m w | ׹ױ~~~vjbhCJaJh]h^>*CJaJhYgCJaJh^CJaJhbkCJaJhE\jCJaJh'/lCJaJhihCJaJhUh@LCJ aJhCJaJhYgCJaJh -LhCJaJh -Lh'fCJaJhK4ICJaJh -LhoCJaJh -Lh5%CJaJh -LhxCJaJ&HI = /0xx]0^gdFMm$ ]^gd$$^`a$gdbk]gd1h^hgd@L & F]gde & F.].gdo|   & ) , p q r t y } 9 ¶«蠘͐zozozggzzhCJaJh\`h*CJaJh\`h'fCJaJh\`h\`CJaJhYgCJaJh]CJaJh\`h1CJaJh\`h fCJaJh1h f5CJaJh`-h fCJaJho__CJaJh`-h'fCJaJhK4ICJaJhihCJaJh'/lCJaJhbkCJaJ(9 = C L a m p  = G ļti[SG[h`-h'f>*CJaJh5%CJaJh`-h'f5CJ\aJh]h'fCJaJh]hdY5>*CJaJh]h'f5>*CJaJh]hbk5>*CJaJh]5>*CJaJh]hYgCJaJh^CJaJh]CJaJh]h]5>*CJaJh CJaJh`-h'fCJaJhihCJaJh\`h'fCJaJhK4ICJaJG I Z d l  ' w  !"/0Y^_ij|qf[Pfh\`hCJaJh\`h CJaJh\`hzaCJaJhK4IhzaCJaJhK4Ih'fCJaJhK4Ih'f5CJaJhh'f>*CJaJhCJaJh^h@LCJ aJhK4ICJaJh`-h!sCJaJh`-h'f5CJ\aJh`-h'f>*CJaJh5%CJaJh`-h'fCJaJh`-hK5CJ\aJ  T]hԶ{m{b{T{TFhfShK4I5CJ\aJhfSh/5CJ\aJh 5CJ\aJhfSh5%5CJ\aJhfSh'f5CJ\aJhfSh'fCJ\aJhCJ\aJh\`h'fCJ\aJh]h]CJ\aJh\`h $CJaJhUCJaJh\`hzaCJaJh\`h'fCJaJh\`hjCJaJh\`h'4)CJaJh\`hCJaJBKLRô{rg_gWgOgDhbkh1CJaJhbkCJaJhE\jCJaJh]CJaJhbkhbkCJaJhbk5CJaJhbkhj$5CJaJh -Lh'f5>*CJ\aJh -Lhr5>*CJ\aJh -Lh5>*CJ\aJh -Lhj$5>*CJ\aJh?Eh'fCJ aJhfSh'f5>*CJaJh]5>*CJaJhfShK4I5>*CJaJhfS5CJ\aJRijkntuv  #-./ļıļ|qqfqfq[h$hPCJaJh$hQCJaJh$h\`CJaJhCJaJh$h'f>*CJaJh$h CJaJh$h'fCJaJh$h@LCJaJh$hq|CJaJhMCJaJh$hCJaJh$CJaJhbkhq|CJaJh]CJaJhbkh1CJaJhbkh CJaJ!/GKat{ @FKLWfio lmoʾttthh CJaJhhG*CJaJhq >CJaJhh5CJaJhh'fCJaJhhgBmCJaJhhxCJaJhhx>*CJaJho__hPCJ aJh$h_CJRHZaJh$h$jrCJaJhCJaJh$h_CJaJ+xyJgd$a$gdgd ( & F]gd'4) ]^gdP & F]gd l]l^gd7\ & F|]|gd1^gd & FL]Lgd0xx]0^gd_m$opqry  ?PQZ]hnvwxyzɾꨠzzzlaUhh7\>*CJaJho__hCJ aJhh56CJaJhh56>*CJaJhh56>*CJaJhCJaJhbkCJaJhhCJaJhhxCJaJhh'4)CJaJhh CJaJhhgBmCJaJhh{CJaJhh CJaJhh/CJaJz~ ;@Heg|  "')1źЯЯФЎ{s{h{s{h]h\`hnCJaJh\`h;^dCJaJhB2CJaJh\`hCJaJhg CJaJh\`hhCJaJh"!h"!CJaJh"!h7\CJaJh\`hCJaJh\`hCJaJh\`hg4,CJaJh\`h7\CJaJhh>*CJaJhh7\>*CJaJhhg4,>*CJaJ$12?JX]j}(6r8YdlmɾɾɾɾԳ~sshsh\`h;uHCJaJh\`h CJaJh\`h'f>*CJaJhgBmCJaJh\`h&CJaJh\`h'fCJaJho__hPCJ aJh\`h7\CJaJh\`hnCJaJh\`h%[CJaJh\`h CJaJh\`hhCJaJh\`hCJaJh\`h;^dCJaJ(457ռzpaUFaUhEhv5>*CJ\aJhb5>*CJ\aJhEhj5>*CJ\aJh\`h>*\ h2h56>*CJ\aJ# *h2h56>*CJ\aJ *h?Eh (5>*CJ\aJh?Eh'f5>*CJaJh?Eh?E5>*CJaJh\`hgLCJaJh\`h'fCJaJhg CJaJh?EhPCJ aJh\`h'f>*CJaJ !"&:DHIJQ\_ghilouvxz{|}~ŽymahT#h'fCJ\aJhT#h*CJ\aJ *hT#h=CJ\aJ *hT#hCJ\aJhT#h'fCJaJ ho__\hT#h\hT#hi\hT#h'f\hT#h5%\hT#h>*\hT#hi>*\ hE\j>*\h\`h $>*OJQJ\^JhEh'f5>*CJ\aJ!Jwxyz{|}ekd$$Ifl8F"q&'' '}&6    44 la`$If $Ifgdo__}~$Ifekdz$$Ifl8F1q&'=''}&6    44 la`  $&+/16:<ADELOPW[и *hT#h'fCJ\]aJ *hT#h'fCJ\aJhT#hCJ\aJhT#hjCJ\aJhT#h`-CJ\aJhT#hjCJ\aJhT#h'fCJaJhT#h'fCJ\aJ8MG>>GGG $$Ifa$$Ifkd$$Ifl֞1&k"q&''U ''E'E'F'}&644 la`G>> $$Ifa$kd$$Ifl֞1&k"q&''U ''E'E'F'}&644 la`$IfG>>> $$Ifa$kd<$$Ifl֞1&k"q&''U ''E'E'F'}&644 la`$IfG>>> $$Ifa$kd$$Ifl֞1&k"q&''U ''E'E'F'}&644 la`$IfG>> $$Ifa$kd$$Ifl֞1&k"q&''U ''E'E'F'}&644 la`$If>kd`$$Ifl֞1&k"q&''U ''E'E'F'}&644 la`$If $$Ifa$QH $$Ifa$kd$$Iflֈ&k"q&'''E'E'F'}&644 la`$If $Ifgd $If $$Ifa$ $$Ifa$gd  MD8D2$If $$Ifa$gd $$Ifa$kd$$Ifl֞1&k"q&''U ''E'E'F'}&644 la`G>>> $$Ifa$kd>$$Ifl֞1&k"q&''U ''E'E'F'}&644 la`$If $%G>> $$Ifa$kd$$Ifl֞1&k"q&''U ''E'E'F'}&644 la`$If%&'()*+/>kd$$Ifl֞1&k"q&''U ''E'E'F'}&644 la`$If $$Ifa$/0123456>kd*$$Ifl֞1&k"q&''U ''E'E'F'}&644 la`$If $$Ifa$6:;<=>?@$If $$Ifa$@AEFGHIJMD>>>>>$If $$Ifa$kd$$Ifl֞1&k"q&''U ''E'E'F'}&644 la`JKLPQRSTG> $$Ifa$kdr $$Ifl֞1&k"q&''U ''E'E'F'}&644 la`$IfTUVW[\]^G> $$Ifa$kd $$Ifl֞1&k"q&''U ''E'E'F'}&644 la`$If^_`abcdeG> $$Ifa$kd $$Ifl֞1&k"q&''U ''E'E'F'}&644 la`$If[bcjkmsuvͿynyn``QB *hT#hCJ\]aJ *hT#hPCJ\]aJhT#h'fCJ\]aJhT#h> CJaJhT#hCJaJhT#hCJ\aJhT#h'fCJaJhT#h'fCJ\aJhT#h*CJ\aJhT#hCJ\aJ *hT#hCJ\aJ *hT#h'fCJ\aJ *hT#hCJ\aJhT#h`-CJ\aJhT#h`-CJaJefghijGkd^ $$Ifl֞1&k"q&''U ''E'E'F'}&644 la`$If$If $$Ifa$?kd $$Ifl\q&'}&}&644 la`Dkd` $$Ifl֞1&k"q&''U ''E'E'F'}&644 la`ytP$Ifxkd $$Ifl\\&k"q&'2'E'F'}&644 la`$If"#$ylff$If $If]gd $If]gdPxkd $$Ifl\\&k"q&'2'E'F'}&644 la` !"%&')*,-/0239:;=>?ֹ֠||xha ha0Jjha0JUh?Bjh?BUhT#h'f>*hT#h'fCJaJhT#hWCJ\]aJhT#hjCJ\]aJhT#h'f>*CJ\]aJhT#h'fCJ\]aJhT#hPCJ\]aJhT#hCJ\]aJ$%&()+,./12;<=>&`#$]gdoShkd$$IflPFk"q&'w'F'}&6    44 la`ytP>?]gdoS9 0&P1:pP/ =!"#$% x$$If`!vh#v#v #v:V l8}&6,55 59/ a`x$$If`!vh#v=#v#v:V l8}&6,5=559/ a`$$If`!vh#v#vU #v#vE#vF#v:V l}&6,55U 55E5F59/ a`$$If`!vh#v#vU #v#vE#vF#v:V l}&6,55U 55E5F59/ a`$$If`!vh#v#vU #v#vE#vF#v:V l}&6,55U 55E5F59/ a`$$If`!vh#v#vU #v#vE#vF#v:V l}&6,55U 55E5F59/ / / / / a`$$If`!vh#v#vU #v#vE#vF#v:V l}&6,55U 55E5F59/ a`$$If`!vh#v#vU #v#vE#vF#v:V l}&6,55U 55E5F59/ a`$$If`!vh#v#v#vE#vF#v:V l}&6,555E5F59/ a`$$If`!vh#v#vU #v#vE#vF#v:V l}&6,55U 55E5F59/ a`$$If`!vh#v#vU #v#vE#vF#v:V l}&6,55U 55E5F59/ a`$$If`!vh#v#vU #v#vE#vF#v:V l}&6,55U 55E5F59/ a`$$If`!vh#v#vU #v#vE#vF#v:V l}&6,55U 55E5F59/ a`$$If`!vh#v#vU #v#vE#vF#v:V l}&6,55U 55E5F59/ a`$$If`!vh#v#vU #v#vE#vF#v:V l}&6,55U 55E5F59/ a`$$If`!vh#v#vU #v#vE#vF#v:V l}&6,55U 55E5F59/ a`$$If`!vh#v#vU #v#vE#vF#v:V l}&6,55U 55E5F59/ a`$$If`!vh#v#vU #v#vE#vF#v:V l}&6,55U 55E5F59/ a`$$If`!vh#v#vU #v#vE#vF#v:V l}&6,55U 55E5F59/ a`\$$If`!vh#v}&:V l\}&6,5}&9/ a`$$If`!vh#v#vU #v#vE#vF#v:V l}&6,55U 55E5F59/ a`ytP$$If`!vh#v2#vE#vF#v:V l\}&6,525E5F59/ a`$$If`!vh#v2#vE#vF#v:V l\}&6,525E5F59/ a`~$$If`!vh#vw#vF#v:V lP}&6,5w5F59/ a`ytP^ 2 0@P`p2( 0@P`p 0@P`p 0@P`p 0@P`p 0@P`p 0@P`p8XV~_HmH nH sH tH @`@ NormalCJ_HaJmH sH tH DA`D Default Paragraph FontVi@V  Table Normal :V 44 la (k (No List 6>@6 Title$a$ 5CJ \@J@@ Subtitle$a$ 5>*CJ \4 @4 Footer  !.)@!. Page Number424 *Header  !H@BH X[ Balloon TextCJOJQJ^JaJ@R@ P List Paragraph ^PK![Content_Types].xmlN0EH-J@%ǎǢ|ș$زULTB l,3;rØJB+$G]7O٭V$ !)O^rC$y@/yH*񄴽)޵߻UDb`}"qۋJחX^)I`nEp)liV[]1M<OP6r=zgbIguSebORD۫qu gZo~ٺlAplxpT0+[}`jzAV2Fi@qv֬5\|ʜ̭NleXdsjcs7f W+Ն7`g ȘJj|h(KD- dXiJ؇(x$( :;˹! I_TS 1?E??ZBΪmU/?~xY'y5g&΋/ɋ>GMGeD3Vq%'#q$8K)fw9:ĵ x}rxwr:\TZaG*y8IjbRc|XŻǿI u3KGnD1NIBs RuK>V.EL+M2#'fi ~V vl{u8zH *:(W☕ ~JTe\O*tHGHY}KNP*ݾ˦TѼ9/#A7qZ$*c?qUnwN%Oi4 =3N)cbJ uV4(Tn 7_?m-ٛ{UBwznʜ"Z xJZp; {/<P;,)''KQk5qpN8KGbe Sd̛\17 pa>SR! 3K4'+rzQ TTIIvt]Kc⫲K#v5+|D~O@%\w_nN[L9KqgVhn R!y+Un;*&/HrT >>\ t=.Tġ S; Z~!P9giCڧ!# B,;X=ۻ,I2UWV9$lk=Aj;{AP79|s*Y;̠[MCۿhf]o{oY=1kyVV5E8Vk+֜\80X4D)!!?*|fv u"xA@T_q64)kڬuV7 t '%;i9s9x,ڎ-45xd8?ǘd/Y|t &LILJ`& -Gt/PK! ѐ'theme/theme/_rels/themeManager.xml.relsM 0wooӺ&݈Э5 6?$Q ,.aic21h:qm@RN;d`o7gK(M&$R(.1r'JЊT8V"AȻHu}|$b{P8g/]QAsم(#L[PK-![Content_Types].xmlPK-!֧6 0_rels/.relsPK-!kytheme/theme/themeManager.xmlPK-!0C)theme/theme/theme1.xmlPK-! ѐ' theme/theme/_rels/themeManager.xml.relsPK] ?x | 9 G R/oz1[? 39J} %/6@JT^e$>?!"#$%&'()*+,-./01245678:; !8@0(  B S  ?ԌՌ@@:*urn:schemas-microsoft-com:office:smarttagsStreet;*urn:schemas-microsoft-com:office:smarttagsaddress XC&()+,./12=@&()+,./12=@EFHHIIXe 55DK rtyp  tu i o l m @ H 7!"uv%&&())+,./12=@EFHHIIXe 55DK rtyp  tu i o l m @ H 7!"uv%&&())+,./12=@S9s\r}w 4LPok6VX.%)> Zpn/sfFg^`o()^`.pLp^p`L.@ @ ^@ `.^`.L^`L.^`.^`.PLP^P`L.^`o()^`.pLp^p`L.@ @ ^@ `.^`.L^`L.^`.^`.PLP^P`L.^`>*o() ^`hH. pLp^p`LhH. @ @ ^@ `hH. ^`hH. L^`LhH. ^`hH. ^`hH. PLP^P`LhH.^`.^`.pp^p`.@ @ ^@ `.^`.^`.^`.^`.PP^P`.^`.^`.pp^p`.@ @ ^@ `.^`.^`.^`.^`.PP^P`. ^`56>*o() ^`hH. pLp^p`LhH. @ @ ^@ `hH. ^`hH. L^`LhH. ^`hH. ^`hH. PLP^P`LhH. ^`56>*o() ^`hH. pLp^p`LhH. @ @ ^@ `hH. ^`hH. L^`LhH. ^`hH. ^`hH. PLP^P`LhH.r}S9)> Z 4/sVXok6                           U        pl         AAyBYfxR''jn +-|:6\rHxR${+ZBYf+- ''jn\r${+Z "!bknTEP!Q4R ? }R y e fGYVuaE/5^za3 ^1,> b*zT#$<$j$5%('4)/F)qX+g4,T-/1 2B23o3 679 :'G;q;w<q >L?]AEG%oGvH;uHK4I|}K -L@L MdOQ'6RoSrTUFWWdY'YkZ7\4j\o__\`;^d'pe'fYgih{iE\j'/l@lgBmGn0o1p$jr0tGwxhxz{.{|3} %[_bgLh;3: ]GMK5js AKv0e &356mMR[d]xyxV fS0b (EWKiE]E/hFM~*ae6?EGMPX[ $[C`1*3_6<0711Bjko9g !sq|=,ff:S V `-b32l $hdf Jfr'#+2IYV:3ufJi+y$?BG*Ztfjp &(@?@UnknownG*Ax Times New Roman5Symbol3. *Cx Arial5. .[`)TahomaA$BCambria Math"h;);)% $% $!Kfr43QHX?2!xx EXCEL REGRESSION ASSIGNJames F. ZeiglerJFZ(       Oh+'0`    ( 4@HPXEXCEL REGRESSION ASSIGNJames F. Zeigler Normal.dotmJFZ2Microsoft Office Word@@u@F{@F{%՜.+,0 hp   Microsoft$  EXCEL REGRESSION ASSIGN Title  !"#$%&'()*+,-./0123456789:;<>?@ABCDFGHIJKLMNOPQRSTUVWXYZ[\]^_`acdefghiklmnopqtRoot Entry Fp vData =1TableE#8WordDocument;xSummaryInformation(bDocumentSummaryInformation8jCompObjr  F Microsoft Word 97-2003 Document MSWordDocWord.Document.89q