Download practice test 2 solution guide for finance 384 class corporate valuation and decision risk and more Exercises Finance in PDF only on Docsity!
Finance 384 - Sp14CR – Solution (Rev Sp15) Practice Test Questions 2 – Financial Forecasting and Stock Analysis
Directions: The Practice Test 2 Spreadsheet is meant to emulate the question types that you will see in Quiz #2 and Test 2. Please use this as a study aid accordingly, i.e., follow all of the directions letter.
- Brett Bergeron (F382, Sp’05) is a financial analyst with Smith-Barney and his task is to predict COST OF SALES ( COS ) for Sturm Ruger & Co. Inc. ( RGR ) on the basis of SALES. He will use the Q1. Worksheet and the Regression Toolpack Analysis Tool to predict COS based on Sales—He will use cell B26 as the output cell and correctly follow the remaining directions. a. Copy the correct data from the Data_1 Worksheet into cells C5:C23 for the Y- variable and into cells D5:D23 for the X-Variable. b. Calculate the annual growth rates for the X-variable (E6:E23) and determine the Average Annual Growth Rate (H21). c. Use the Average Annual Growth Rate to project the X-variable for 2010 and put the result in cell H25. d. Use the Excel Toolpak Regression Analysis to run the regression. Put your NUMERIC answers from Excel in the outlined cells (E25:G25) shown in the spreadsheet facsimile below.
Column Row A B C D E F G H 3 Sturm Ruger Co. Inc. 4 Q1. Year COS Sales Annual Growth 5 1991 $97,018^ $136,781^ - 6 1992 $105,826 $156,075 14.106% 7 1993 $123,336^ $194,199^ 24.427% 8 1994 $125,439^ $196,437^ 1.152% 9 1995 $134,930^ $192,469^ -2.020% 10 1996 $150,200^ $223,295^ 16.016% 11 1997 $146,143 $209,383 -6.230% 12 1998 $157,048^ $211,580^ 1.049% 13 1999 $170,650^ $241,664^ 14.219% 14 2000 $144,503^ $202,654^ -16.142% 15 2001 $134,449^ $174,330^ -13.977% 16 2002 $125,376 $161,587 -7.310% 17 2003 $113,189^ $147,914^ -8.462% 18 2004 $115,725^ $145,624^ -1.548% 19 2005 $128,343^ $154,722^ 6.248%^ Sales 20 2006 $139,610^ $167,620^ 8.336%^ Growth 21 2007 $117,186 $156,485 -6.643% 4.92% 22 2008 $138,730^ $181,483^ 15.975% 23 2009 $183,380^ $270,985^ 49.317%^ 2010 24 Projected COS Intercept Slope Sales 25 Put Output in Cell B26 $189,285^ 30914.2099^ 0.5570358^ $284,
In the labeled boxes provided below the spreadsheet place the EXACT spreadsheet FORMULAS that you used to calculate the indicated values. You must use cell references in your formulas. Note: If your formulas do not WORK in an actual spreadsheet they will receive no partial credit.
E6: =(D6/D5)-1 E25: =F25+(G25*H25)
H21: =AVERAGE(E6:E23) F25: =C
H25: =D23*(1+H21) G25: =C
- Karianne Yost (F382, Sp’06 and former SLU All-Southland Conference Soccer Player) now works for Shearson Lehman Bros. , Houston and is predicting SALES for Sturm Ruger & Co. Inc. ( RGR ) on the basis of YEAR. She will use the Q2. Worksheet and the Regression Toolpack Analysis Tool to predict Sales based on Year—Using cell B26 as the output cell.
Use the Excel Toolpak Regression Analysis to run the regression. Put your NUMERIC answers from Excel in the outlined cells (E25:G25) shown in the spreadsheet facsimile below.
Column Row A B C D E F G H 3 Sturm Ruger Co. Inc. 4 Q2. Year Sales 5 1991 $136, 6 1992 $156, 7 1993 $194, 8 1994 $196, 9 1995 $192, 10 1996 $223, 11 1997 $209, 12 1998 $211, 13 1999 $241, 14 2000 $202, 15 2001 $174, 16 2002 $161, 17 2003 $147, 18 2004 $145, 19 2005 $154, 20 2006 $167,620 SALES REGRESSION LINE 21 2007 $156,485 (put regression output in cell B26) 22 2008 $181,483 2010 23 2009 $270,985 Predicted Sales=
Intercept Slope Year
- Norene Tucker (F382 Fa’03) is a grade-school music teacher who is trying to make decisions about her investment portfolio. Norene is predicting Dividends per share (DPS) for Sturm Ruger & Co. Inc. ( RGR ) on the basis of Earnings per share (EPS). She will use the Q3. Worksheet and the Regression Toolpack Analysis Tool to predict DPS based on EPS—She will use cell B26 as the output cell and correctly follow the remaining directions. a. Copy the correct data from the Data_1 Worksheet into cells C5:C23 for the Y- variable and into cells D5:D23 for the X-Variable. b. Calculate the annual growth rates for the X-variable (E6:E23) and determine the Average Annual Growth Rate (H21). c. Use the Average Annual Growth Rate to project the X-variable for 2010 and put the result in cell H25. d. Use the Excel Toolpak Regression Analysis to run the regression. Put your NUMERIC answers from Excel in the outlined cells (E25:G25) shown in the spreadsheet facsimile below.
Column Row A B C D E F G H 3 Sturm Ruger Co. Inc. 4 Q3. Year Divs EPS Annual Growth 5 1991 $0.600^ $0.54^ - 6 1992 $0.625^ $0.82^ 51.852% 7 1993 $0.525^ $1.22^ 48.780% 8 1994 $0.600^ $1.27^ 4.098% 9 1995 $0.700^ $0.97^ -23.622% 10 1996 $0.800^ $1.28^ 31.959% 11 1997 $0.800^ $1.03^ -19.531% 12 1998 $0.800^ $0.87^ -15.534% 13 1999 $0.800^ $1.25^ 43.678% 14 2000 $0.800^ $1.00^ -20.000% 15 2001 $0.800^ $0.50^ -50.000% 16 2002 $0.800^ $0.31^ -38.000% 17 2003 $0.800^ $0.46^ 48.387% 18 2004 $0.600^ $0.18^ -60.870% 19 2005 $0.300^ $0.03^ -83.333%^ XXX 20 2006 $0.000^ $0.04^ 33.333%^ Growth 21 2007 $0.000^ $0.46^ 1050.000%^ 68.31% 22 2008 $0.000^ $0.43^ -6.522% 23 2009 $0.310^ $1.44^ 234.884%^ 2010 24 Projected YYY
Intercep t
Slope XXX
25 Put Output in Cell B26 $0.993^ 0.3705^ 0.2567824^ $2.
In the labeled boxes provided below the spreadsheet place the EXACT spreadsheet FORMULAS that you used to calculate the indicated values. You must use cell references in your formulas. Note: If your formulas do not WORK in an actual spreadsheet they will receive no partial credit.
E6: =(D6/D5)-1 E25: =F25+(G25*H25)
H21: =AVERAGE(E6:E23) F25: =C
H25: =D23*(1+H21) G25: =C
D255: =STDEVP(D$130:D$249)/SQRT(12) D258: =SLOPE(D130:D249,$C130:$C249)
d. Use the Excel Toolpak Analysis Tool to conduct the Two-Sample t-Test analysis.
- Use Cell B261 as the destination cell for the results of comparing the mean returns for the S&P 500 vs. CAT. The full results of which should appear in B261:D273. Copy the results into the spreadsheet facsimile below.
Column Row B C D 260 Put Output in Cell B261. 261 t-Test: Two-Sample Assuming Unequal Variances
263 Variable 1^ Variable 2 264 Mean^ -0.01284316^ 0. 265 Variance^ 0.316608398^ 1. 266 Observations^120 267 Hypothesized Mean Difference^0 268 df^170 269 t Stat^ -2. 270 P(T<=t) one-tail^ 0. 271 t Critical one-tail^ 1. 272 P(T<=t) two-tail^ 0. 273 t Critical two-tail^ 1.
e. Based on the two-tailed test statistic, can the Null Hypothesis of no significant mean difference between the ten-year return for Caterpillar versus the S&P 500 Index be rejected at the 5% level. You must use the “IF” function to correctly identify the answer in conjunction with cell references to the two-tailed T-test results to generate the answer.
B C D
275 Q4e. Using the “IF” function and based on 276 the two-tailed t-test can the null hypothesis 277 of no significant difference be rejected? 278 Answer? YES
In the Labelled boxes provided below the spreadsheet copy the EXACT spreadsheet EQUATIONS / FUNCTION that you used to calculate the indicated values. You must use cell references in your formulas. Note: If your formula does not WORK in an actual spreadsheet it will receive no partial credit.
D278: =IF(ABS(C269)>C273,"YES","NO")
d. Use the Excel Toolpak Analysis Tool to conduct the Two-Sample t-Test analysis.
- Use Cell G213 as the destination cell for the results of comparing the mean returns for the Campbell’s Soup vs. the S&P 500. The full results of which should appear in G213:I225. Copy the results into the spreadsheet facsimile below.
Column Row G H I 212 Put Output in Cell G213. 213 t-Test: Two-Sample Assuming Unequal Variances 214 215 Variable 1^ Variable 2 216 Mean^ 0.008560988^ 0. 217 Variance^ 0.0025693^ 0. 218 Observations^96 219 Hypothesized Mean Difference^0 220 Df^187 221 t Stat^ 0. 222 P(T<=t) one-tail^ 0. 223 t Critical one-tail^ 1. 224 P(T<=t) two-tail^ 0. 225 t Critical two-tail^ 1.
e. Based on the two-tailed test statistic, can the Null Hypothesis of no significant mean difference between the eight-year return for Campbell’s Soup versus the S&P 500 Index rejected at the 5% level. You must use the “IF” function to correctly identify the answer in conjunction with cell references to the two-tailed T-test results to generate the answer.
G H I
227 Q5e. Using the “IF” function and based on 228 the two-tailed t-test can the null hypothesis 229 of no significant difference be rejected? 230 Answer? NO
In the Labelled boxes provided below the spreadsheet copy the EXACT spreadsheet EQUATIONS / FUNCTION that you used to calculate the indicated values. You must use cell references in your formulas. Note: If your formulas do not WORK in an actual spreadsheet they will receive no partial credit.
I230: =IF(ABS(H221)>H225,"YES","NO")
N247: =STDEVP(N$126:N$241)/SQRT(12) N250: =SLOPE(N126:N241,$M126:$M241)
c.d.Use the Excel Toolpak Analysis Tool to conduct the Two-Sample t- Test analysis.
- Use Cell L253 as the destination cell for the results of comparing the mean returns for the S&P 500 vs. Entergy. The full results of which should appear in L253:N265. Copy the results into the spreadsheet facsimile below.
Column 251 L M N 252 Put Output in Cell L253. 253 t-Test: Two-Sample Assuming Unequal Variances 254 255 Variable 1^ Variable 2 256 Mean^ -0.008493472^ 0. 257 Variance^ 0.318139088^ 0. 258 Observations^116 259 Hypothesized Mean Difference^0 260 Df^224 261 t Stat^ -1. 262 P(T<=t) one-tail^ 0. 263 t Critical one-tail^ 1. 264 P(T<=t) two-tail^ 0. 265 t Critical two-tail^ 1.
e. Based on the two-tailed test statistic, can the Null Hypothesis of no significant mean difference between the nine-year return for Entergy versus the S&P 500 Index be rejected at the 5% level. You must use the “IF” function to correctly identify the answer in conjunction with cell references to the two-tailed T-test results to generate the answer.
L M N
267 Q6e. Using the “IF” function and based on 268 the one-tailed t-test can the null hypothesis 269 of no significant difference be rejected? 270 Answer? NO
In the labelled boxes provided below the spreadsheet copy the EXACT spreadsheet EQUATIONS / FUNCTION that you used to calculate the indicated values. You must use cell references in your formulas. Note: If your formulas do not WORK in an actual spreadsheet they will receive no partial credit.
N270: =IF(ABS(M261)>M265,"YES","NO")
