quantitative business analysis

profileNickphoto
_hw_6_r.xlsx
Home>Business & Finance homework help>Management homework help>quantitative business analysis

Q1 and 2 Regression & Inference

Q1 The table on the right shows the Quiz, Homework, Midterm, and Final exam scores for 68 students who took this class in an earlier semester.
A. What impact, positive or negative, would you expect each one of these variables to have on the final exam score?
B. Run a multiple linear regression with the Final Exam score as the dependent variable against these three independent variables. Do any have an unexpected impact?
C. How well do these independent variables explain the Final exam score?
D. Which independent variable(s) is (are) statististically significant in explaining the Final exam score?
E. Delete any insignificant X variables and re-run the regression. Now how good is your fit?
Q2. Draw inferences from these data and results. For instance, do these data suggest that if a student does well before the final exam, they slack off on study because they think they can coast and still do well on the final exam? Or are they motivated to do even better to lock up their A? How about students who did poorly prior to the final exam? Are they motivated to put more time into the course to raise their scores? Do they succeed? (Hint: Compute the change in scores between midterm and final for students who got A on the midterm - 90% or higher - and the same for students who got D - 59% - or below.) Student ID ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF!
ERROR:#REF! 80% 109% 90% 67%
ERROR:#REF! 60% 66% 60% 58%
ERROR:#REF! 74% 109% 65% 58%
ERROR:#REF! 64% 58% 53% 48%
ERROR:#REF! 57% 77% 98% 60%
ERROR:#REF! 88% 98% 70% 75%
ERROR:#REF! 81% 87% 92% 85%
ERROR:#REF! 77% 96% 57% 83%
ERROR:#REF! 61% 86% 92% 73%
ERROR:#REF! 83% 105% 71% 63%
ERROR:#REF! 50% 60% 45% 52%
ERROR:#REF! 39% 98% 74% 71%
ERROR:#REF! 77% 94% 97% 73%
ERROR:#REF! 80% 102% 98% 90%
ERROR:#REF! 56% 111% 97% 85%
ERROR:#REF! 84% 114% 86% 63%
ERROR:#REF! 67% 113% 65% 81%
ERROR:#REF! 59% 84% 51% 44%
ERROR:#REF! 85% 115% 89% 94%
ERROR:#REF! 88% 105% 89% 69%
ERROR:#REF! 93% 104% 108% 102%
ERROR:#REF! 86% 123% 114% 94%
ERROR:#REF! 63% 95% 69% 94%
ERROR:#REF! 80% 110% 89% 83%
ERROR:#REF! 52% 41% 54% 58%
ERROR:#REF! 87% 120% 89% 79%
ERROR:#REF! 46% 63% 60% 85%
ERROR:#REF! 39% 100% 81% 60%
ERROR:#REF! 88% 112% 82% 63%
ERROR:#REF! 77% 121% 105% 87%
ERROR:#REF! 75% 67% 76% 79%
ERROR:#REF! 68% 105% 89% 81%
ERROR:#REF! 70% 92% 81% 81%
ERROR:#REF! 65% 103% 104% 110%
ERROR:#REF! 73% 109% 109% 92%
ERROR:#REF! 80% 82% 101% 69%
ERROR:#REF! 87% 99% 92% 81%
ERROR:#REF! 70% 113% 95% 85%
ERROR:#REF! 72% 44% 62% 63%
ERROR:#REF! 76% 96% 71% 56%
ERROR:#REF! 46% 86% 47% 77%
ERROR:#REF! 50% 92% 57% 67%
ERROR:#REF! 34% 36% 51% 68%
ERROR:#REF! 73% 106% 79% 69%
ERROR:#REF! 73% 104% 79% 62%
ERROR:#REF! 70% 110% 68% 87%
ERROR:#REF! 66% 105% 80% 79%
ERROR:#REF! 66% 101% 84% 62%
ERROR:#REF! 68% 80% 68% 69%
ERROR:#REF! 69% 0% 71% 46%
ERROR:#REF! 46% 58% 81% 54%
ERROR:#REF! 49% 23% 60% 50%
ERROR:#REF! 63% 110% 98% 94%
ERROR:#REF! 40% 12% 63% 75%
ERROR:#REF! 63% 78% 57% 60%
ERROR:#REF! 66% 22% 49% 60%
ERROR:#REF! 87% 110% 86% 69%
ERROR:#REF! 45% 112% 60% 48%
ERROR:#REF! 57% 98% 78% 90%
ERROR:#REF! 60% 85% 94% 94%
ERROR:#REF! 29% 33% 59% 75%
ERROR:#REF! 57% 108% 60% 58%
ERROR:#REF! 65% 95% 65% 63%
ERROR:#REF! 65% 80% 55% 65%
ERROR:#REF! 89% 108% 81% 73%
ERROR:#REF! 46% 107% 61% 60%
ERROR:#REF! 86% 123% 114% 100%
ERROR:#REF! 40% 67% 72% 71%

Q3 Data Tables

Q3 An investor is curious about what saving $1200 per year ($100 per month) would be worth in 30 years both in todays dollars and future dollars. She also is curious about which factor has the greatest impact, the saving per month, inflation, or the return she earns.
A Build a spreadsheet that includes savings, inflation, and return as the inputs, ending values in 30 years both in today's and future dollars as the outputs. Use 3% inflation and 7% return as the base case.
B Compile a single two way data table to estimate the ending value for $100 savings per month with inflation ranging from 0% to 6% and returns ranging from 4% to 10%.
C Compute the average, maximum, and minimum from the above two-way data table.
D Compile three one-way data tables to perform a standard sensitivity analysis to see which factor has the greatest impact.

Q4 Pivot Tables

Q4 Below is a one-month dataset for a company that sells products (toys) in four regions using sales representatives. The company currently has 31 customers.
A Set up a pivot table showing total sales by Salesrep/product by region (see sample below).
B The sales manager has heard that one of the Sales Reps may have overcharged a customer by a large amount to generate excess short-term profits, which are used to compute compensation for the Sales Reps. If the customer finds out, they will may sue the company. Use a pivot table or filters to see if any odd profit figures have occurred and if so, who is the customer and who is the Sales Rep.
Date Product Region SalesRep Customer Units Revenue COGS
1/1/11 Quad East Sioux AA 16 $432.00 $232.00 Sample
1/2/11 Bellen South Gault KBTB 24 $528.00 $240.00 Regional Revenue
1/2/11 Quad West Pham AST 30 $810.00 $435.00 Sales Rep/Product East MidWest South West Grand Total
1/2/11 Bellen West Pham SFWK 19 $418.00 $190.00 Chin $4,397 $3,494 $2,227 $2,004 $12,122
1/3/11 Sunshine East Pham FM 38 $722.00 $304.00 Bellen $1,474 $726 $2,200
1/3/11 Carlota MidWest Chin FM 20 $460.00 $220.00 Carlota $1,357 $460 $1,817
1/3/11 Sunset South Pham WSD 23 $483.00 $212.75 etc.
1/3/11 Sunshine South Sioux PCC 6 $114.00 $48.00
1/4/11 Sunset MidWest Sioux BBT 29 $609.00 $268.25
1/5/11 Sunset South Pham TTT 57 $1,197.00 $527.25
1/5/11 Sunshine West Franks ET 12 $228.00 $96.00
1/5/11 Carlota West Franks ITTW 60 $1,380.00 $660.00
1/6/11 Sunshine East Franks PSA 54 $1,026.00 $432.00
1/6/11 Sunshine South Smith QT 40 $760.00 $320.00
1/6/11 Carlota West Sioux KPSA 18 $414.00 $198.00
1/6/11 Quad West Sioux PCC 64 $1,728.00 $928.00
1/7/11 Carlota MidWest Franks HHH 12 $276.00 $132.00
1/7/11 Sunset MidWest Sioux PCC 22 $462.00 $203.50
1/7/11 Sunset West Pham T 61 $1,281.00 $564.25
1/7/11 Sunset West Pham WT 53 $1,113.00 $490.25
1/7/11 Bellen West Smith T 27 $594.00 $270.00
1/8/11 Quad West Chin HII 16 $432.00 $232.00
1/9/11 Carlota East Franks WSD 38 $874.00 $418.00
1/9/11 Bellen East Pham EPP 40 $880.00 $400.00
1/9/11 Sunshine East Smith PCC 42 $798.00 $336.00
1/9/11 Bellen South Chin WSD 26 $572.00 $260.00
1/9/11 Bellen South Sioux QT 15 $330.00 $150.00
1/10/11 Sunset East Gault TRU 27 $567.00 $249.75
1/10/11 Carlota East Pham AA 63 $1,449.00 $693.00
1/10/11 Quad East Smith DFR 17 $459.00 $246.50
1/10/11 Sunset East Smith MBG 17 $357.00 $157.25
1/10/11 Bellen MidWest Gault FM 7 $154.00 $70.00
1/10/11 Sunshine MidWest Smith TTT 8 $152.00 $64.00
1/10/11 Sunshine South Chin TRU 30 $570.00 $240.00
1/10/11 Sunset South Sioux MBG 47 $987.00 $434.75
1/10/11 Quad West Smith KBTB 65 $1,755.00 $942.50
1/11/11 Quad East Franks ITW 14 $378.00 $203.00
1/11/11 Quad East Smith BBT 49 $1,323.00 $710.50
1/12/11 Sunset MidWest Gault ITW 19 $399.00 $175.75
1/12/11 Bellen South Chin WT 7 $154.00 $70.00
1/13/11 Bellen MidWest Pham PLOT 57 $1,254.00 $570.00
1/13/11 Sunshine MidWest Pham PSA 33 $627.00 $264.00
1/13/11 Bellen MidWest Pham PSA 40 $880.00 $400.00
1/13/11 Carlota South Smith FM 52 $1,196.00 $572.00
1/14/11 Carlota East Franks JAQ 34 $782.00 $374.00
1/15/11 Sunset East Gault AST 63 $1,323.00 $582.75
1/15/11 Sunshine MidWest Chin YTR 11 $209.00 $88.00
1/16/11 Bellen MidWest Sioux YTR 56 $1,232.00 $560.00
1/16/11 Carlota South Smith QT 31 $713.00 $341.00
1/17/11 Quad East Franks HII 62 $1,674.00 $899.00
1/17/11 Quad East Gault SFWK 43 $1,161.00 $623.50
1/17/11 Carlota East Sioux PCC 39 $897.00 $429.00
1/17/11 Quad MidWest Chin EPP 61 $1,647.00 $884.50
1/17/11 Sunshine MidWest Franks HHH 59 $1,121.00 $472.00
1/17/11 Bellen MidWest Smith PCC 16 $352.00 $160.00
1/17/11 Quad West Chin PCC 10 $270.00 $145.00
1/18/11 Sunshine East Franks AA 24 $456.00 $192.00
1/18/11 Bellen East Franks DFGH 20 $440.00 $200.00
1/18/11 Sunset East Gault MNGD 12 $252.00 $111.00
1/18/11 Bellen East Pham DFR 59 $1,298.00 $590.00
1/18/11 Sunshine MidWest Chin PCC 62 $1,178.00 $496.00
1/18/11 Quad MidWest Smith PLOT 17 $459.00 $246.50
1/18/11 Carlota South Smith YTR 53 $1,219.00 $583.00
1/18/11 Sunshine West Sioux AST 8 $152.00 $64.00
1/19/11 Bellen South Sioux T 35 $770.00 $350.00
1/20/11 Carlota East Chin ET 59 $1,357.00 $649.00
1/20/11 Bellen East Gault PSA 10 $220.00 $100.00
1/20/11 Sunshine West Sioux YTR 58 $1,102.00 $464.00
1/21/11 Quad East Chin FM 58 $1,566.00 $841.00
1/21/11 Sunshine MidWest Pham DFR 23 $437.00 $184.00
1/21/11 Sunshine MidWest Smith QT 64 $1,216.00 $512.00
1/21/11 Sunset South Pham HHH 13 $273.00 $120.25
1/21/11 Bellen South Smith TRU 11 $242.00 $110.00
1/21/11 Quad West Gault ITW 56 $1,512.00 $812.00
1/22/11 Quad South Franks FM 29 $783.00 $420.50
1/22/11 Carlota South Smith KBTB 29 $667.00 $319.00
1/22/11 Bellen West Pham AST 53 $1,166.00 $530.00
1/22/11 Bellen West Sioux YTR 35 $770.00 $350.00
1/22/11 Bellen West Smith WT 6 $132.00 $60.00
1/23/11 Quad East Franks PCC 44 $1,188.00 $638.00
1/23/11 Bellen East Gault DFGH 9 $198.00 $90.00
1/23/11 Quad East Pham WT 22 $594.00 $319.00
1/23/11 Sunshine South Chin T 49 $931.00 $392.00
1/24/11 Carlota MidWest Franks BBT 13 $299.00 $143.00
1/24/11 Quad West Gault HII 63 $1,701.00 $913.50
1/25/11 Sunshine MidWest Franks JAQ 21 $399.00 $168.00
1/26/11 Bellen East Chin KPSA 17 $374.00 $170.00
1/26/11 Bellen MidWest Gault PSA 21 $462.00 $210.00
1/26/11 Sunset South Sioux DFGH 44 $924.00 $407.00
1/26/11 Bellen West Pham FRED 61 $5,000.00 $610.00
1/27/11 Carlota West Gault QT 10 $230.00 $110.00
1/28/11 Sunset East Smith ZAT 36 $756.00 $333.00
1/28/11 Quad MidWest Smith YTR 42 $1,134.00 $609.00
1/28/11 Carlota South Franks MNGD 46 $1,058.00 $506.00
1/28/11 Quad South Franks YTR 44 $1,188.00 $638.00
1/28/11 Quad South Gault KBTB 56 $1,512.00 $812.00
1/28/11 Bellen South Sioux QT 25 $550.00 $250.00
1/28/11 Sunshine South Smith FRED 51 $969.00 $408.00
1/28/11 Sunset West Chin FM 62 $1,302.00 $573.50
1/28/11 Carlota West Smith LOP 15 $345.00 $165.00
1/29/11 Bellen East Chin AST 50 $1,100.00 $500.00
1/29/11 Quad MidWest Gault PLOT 10 $270.00 $145.00
1/30/11 Sunshine South Sioux TTT 23 $437.00 $184.00
1/30/11 Quad South Sioux TTT 25 $675.00 $362.50
1/30/11 Sunshine West Gault DFR 49 $931.00 $392.00

Q5 Breakeven

Q5 Krashnburn Insurance Company is a start-up company hoping to sell insurance policies online to people who want insurance for their families in case their plane crashes. An actuary has advised them claims will likely amount to about 10 basis points (0.1%) of the insured amount and that most insurance companies charge 20 basis points for the face value of the policy. They believe the face value of the average policy will be $60,000. They also believe their fixed costs will be $200,000 per year, and they will sell 200 policies per month in the first year. Growth after that is unknown.
A If their costs remain the same, how many years will be take to reach profitability if the number of policies sold increases by 10% per year?
B If their costs remain the same, how fast will they need to grow to breakeven in the third year?
Mean ERROR:#DIV/0! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF!
Max $0
Min $0
Stdev ERROR:#DIV/0!

Q6 Extra Credit

30% Extra Credit Question: You must answer all of the prior questions to be eligible for extra on this question
Q6 "I just turned 25. How much will I have to save each year for each $1,000,000 I want on my 72nd birthday based on the assumptions shown for how much I will increase the savings per year to keep up with inflation and how fast I expect the portfolio to grow on average.
Perform a sensitivity analysis on this problem to see which factor has the greatest impact on the ending value, inflation or growth rate. Draw a spider plot and tornado chart.
Inputs:
Already Saved: $0
Save Per Yr. $2,619 $218.26 Per month
Inflation Adjustment 3%
Growth Rate 6%
Output:
Nest Egg $1,000,000
Calculations:
Value at End of Year EOY Age Save EOY Cumm.
0 24 0 0
1 25 $2,619 $2,619
2 26 $2,698 $5,474
3 27 $2,779 $8,581
4 28 $2,862 $11,958
5 29 $2,948 $15,623
6 30 $3,036 $19,597
7 31 $3,127 $23,900
8 32 $3,221 $28,556
9 33 $3,318 $33,587
10 34 $3,417 $39,020
11 35 $3,520 $44,881
12 36 $3,626 $51,199
13 37 $3,734 $58,005
14 38 $3,846 $65,332
15 39 $3,962 $73,214
16 40 $4,081 $81,687
17 41 $4,203 $90,791
18 42 $4,329 $100,568
19 43 $4,459 $111,061
20 44 $4,593 $122,317
21 45 $4,731 $134,387
22 46 $4,872 $147,322
23 47 $5,019 $161,180
24 48 $5,169 $176,020
25 49 $5,324 $191,906
26 50 $5,484 $208,904
27 51 $5,648 $227,087
28 52 $5,818 $246,530
29 53 $5,992 $267,314
30 54 $6,172 $289,525
31 55 $6,357 $313,254
32 56 $6,548 $338,598
33 57 $6,745 $365,658
34 58 $6,947 $394,544
35 59 $7,155 $425,372
36 60 $7,370 $458,265
37 61 $7,591 $493,352
38 62 $7,819 $530,772
39 63 $8,053 $570,671
40 64 $8,295 $613,206
41 65 $8,544 $658,543
42 66 $8,800 $706,855
43 67 $9,064 $758,331
44 68 $9,336 $813,167
45 69 $9,616 $871,573
46 70 $9,905 $933,772
47 71 $10,202 $1,000,000
(Note - Excel's PMT function works for 0% inflation adjustment but will not allow for graduated payments.)
For monthly savings, PMT gives: ($3,962.26)