pls i need help with this assignment no price increase

MBA674 Midterm Exam - Spreadsheet Problems A couple of general things I wanted to make note of: This part of exam contains 6 application problems with 20 points each and 120 points total. 1b) Knowing how to use Pivot Tables is an essential skill for this class. For this question, all you have to do within the pivo -drag loan purpose to rows -drag checking to values -on the table where it says "sum of checking" right click and select "value field settings" -scroll down to stdev and click ok 1c) The directions here said to use the descriptive statistics, so if you got partial credit that is why. 2b) If you want to find the cutoff for the top 10%, you need to use 1-.10 for the probability. The cutoff for the top 10% is t bottom 90%. 3) If you're using method 2 we need the confidence.t function because we do not know the population standard deviatio What we're doing with a confidence interval is using a sample to estimate something in the population. It has nothing t So the confidence interval around the mean is the range we think the population mean is actually in. Using 95% confiden intervals from 100 samples of equal size we would expect 95 of them to contain the POPULATION MEAN. 6) While I said you can use any method, using as if statement is BY FAR the fastest and most reliable way to do this proble use an if statement by now (If there is a similar question on the final you will be required to use an IF statement). have to do within the pivot table window is: hy. cutoff for the top 10% is the same as the cutoff for the pulation standard deviation. opulation. It has nothing to do with a percent of the data. lly in. Using 95% confidence means that if we made ON MEAN. able way to do this problem. And I do expect that you can an IF statement). AP AP1. 1a. Use the countif function to count the number of loans for each Loan Purpos 1b. Use a pivot table to find the standard deviation of "Checking" for each loan 1c. Use descriptive statistics to find the mean, median, and standard deviation f "savings." Loan Purpose Checking Savings Months Customer Small Appliance Furniture New Car Furniture Education Furniture New Car Business Small Appliance Small Appliance Business New Car Business New Car New Car Used Car Furniture New Car Repairs Education Furniture Furniture Furniture Furniture New Car Business Used Car Used Car Small Appliance Small Appliance Education Small Appliance Small Appliance Business Business Small Appliance Furniture Small Appliance Small Appliance New Car Other $0 $739 $0 $1,230 $0 $389 $638 $347 $963 $4,754 $2,827 $0 $0 $229 $0 $533 $6,509 $493 $966 $0 $0 $989 $0 $3,305 $322 $578 $0 $821 $396 $228 $0 $129 $652 $732 $708 $683 $207 $0 $287 $12,348 $0 $17,545 $101 $3,871 $0 $0 $0 $485 $0 $10,723 $141 $245 $0 $0 $2,484 $0 $237 $236 $0 $485 $335 $1,708 $3,565 $0 $0 $407 $16,647 $895 $0 $150 $0 $490 $0 $162 $940 $715 $0 $323 $0 $128 $218 $0 Months Employed 13 12 25 19 13 40 11 13 14 37 25 49 11 10 25 13 31 49 13 28 7 34 13 25 37 11 22 19 49 37 19 37 31 13 16 49 5 25 9 49 13 49 0 119 14 45 13 16 2 9 4 0 15 14 63 26 8 4 33 116 2 16 5 23 23 15 33 58 46 24 12 7 32 2 34 46 41 1 40 42 74 0 Used Car Small Appliance Furniture Furniture Small Appliance Furniture New Car Business Small Appliance Small Appliance Furniture Business New Car Used Car Education Furniture New Car New Car Furniture New Car New Car Small Appliance New Car Small Appliance Small Appliance Business Used Car Furniture Furniture New Car Small Appliance Business Furniture Small Appliance New Car Business Business Repairs Business New Car Education New Car Education New Car Small Appliance Used Car Small Appliance Small Appliance $0 $109 $16,935 $189 $664 $537 $150 $6,520 $0 $138 $216 $0 $0 $660 $0 $724 $0 $897 $265 $947 $4,256 $0 $870 $917 $162 $595 $0 $789 $0 $0 $0 $746 $461 $140 $0 $659 $0 $717 $0 $667 $580 $0 $0 $763 $0 $1,366 $0 $552 $0 $14,643 $758 $2,665 $399 $0 $513 $442 $0 $8,357 $0 $0 $565 $863 $0 $322 $0 $800 $0 $656 $166 $922 $9,783 $885 $674 $2,886 $0 $626 $15,328 $0 $0 $904 $713 $784 $0 $806 $0 $3,281 $0 $759 $0 $680 $0 $104 $303 $899 $900 $1,732 25 37 31 12 7 19 17 25 19 25 16 28 22 25 37 13 19 19 37 29 11 13 19 25 16 13 31 7 25 22 10 28 13 37 13 13 49 43 25 12 61 19 19 16 25 37 13 37 26 60 33 1 119 3 75 8 5 5 36 6 10 28 114 16 32 5 60 10 8 46 17 4 115 31 0 0 5 9 81 28 69 85 2 3 32 0 9 6 17 3 20 59 3 25 3 11 Furniture Education Small Appliance Repairs Business Small Appliance Small Appliance Small Appliance New Car Small Appliance Furniture Small Appliance New Car Retraining Furniture New Car Education Business Furniture New Car Used Car Small Appliance Furniture Business Used Car Furniture Furniture Small Appliance Used Car Large Appliance Furniture Furniture Small Appliance Small Appliance Furniture New Car New Car New Car Furniture Small Appliance Business Furniture Small Appliance Small Appliance Small Appliance Other Business Repairs $0 $706 $1,257 $0 $0 $576 $273 $904 $522 $194 $0 $710 $0 $5,564 $0 $192 $0 $637 $514 $405 $457 $318 $5,133 $698 $0 $369 $644 $0 $305 $492 $9,621 $308 $0 $127 $0 $565 $0 $12,632 $0 $116 $0 $178 $6,851 $901 $13,496 $650 $509 $241 $0 $609 $19,155 $131 $0 $544 $0 $10,853 $374 $0 $0 $409 $828 $391 $0 $322 $829 $583 $0 $12,242 $0 $479 $939 $496 $0 $466 $889 $1,583 $876 $1,533 $893 $0 $12,760 $4,873 $0 $0 $0 $717 $959 $7,876 $0 $4,449 $0 $0 $0 $104 $0 $897 31 10 7 7 25 25 25 46 13 49 19 19 10 13 19 25 13 19 16 49 13 13 19 25 37 25 19 25 25 49 9 13 7 25 19 19 25 37 31 16 13 13 22 28 25 25 25 19 14 65 14 2 79 1 93 13 21 13 108 14 16 88 1 41 22 14 9 45 89 21 20 14 6 24 15 81 14 15 12 9 18 53 0 56 42 79 21 94 73 94 10 20 87 54 23 2 New Car Furniture Furniture Small Appliance Small Appliance Furniture Small Appliance Business Repairs Small Appliance Furniture New Car New Car Used Car New Car Used Car New Car Small Appliance New Car Used Car Education Furniture Large Appliance Furniture Furniture Business Used Car Small Appliance Small Appliance Used Car Small Appliance Repairs Furniture Used Car New Car New Car New Car Repairs Furniture Used Car Small Appliance New Car Business Small Appliance Small Appliance Business Furniture Furniture $698 $0 $0 $0 $12,974 $0 $317 $0 $0 $0 $192 $0 $0 $0 $0 $0 $942 $0 $3,329 $0 $0 $0 $0 $0 $0 $339 $0 $0 $0 $105 $0 $216 $113 $109 $0 $0 $8,176 $0 $468 $7,885 $0 $0 $0 $0 $0 $0 $0 $0 $4,033 $945 $836 $325 $19,568 $803 $10,980 $265 $609 $1,851 $199 $500 $509 $270 $457 $260 $3,036 $643 $0 $6,345 $922 $909 $775 $979 $948 $2,790 $309 $762 $970 $320 $861 $262 $692 $540 $470 $192 $12,230 $772 $14,186 $6,330 $18,716 $886 $750 $3,870 $3,273 $406 $461 $340 16 13 25 19 13 13 13 13 31 12 25 28 16 25 13 25 25 19 19 25 37 25 19 25 19 22 49 10 13 28 13 37 11 37 13 7 7 25 22 16 19 22 37 25 13 6 13 19 20 6 99 13 7 89 17 10 3 0 5 7 3 25 63 78 36 6 15 19 9 3 8 48 2 55 37 1 14 54 111 2 14 1 0 2 5 19 24 14 93 96 2 11 4 35 48 4 Small Appliance Small Appliance Furniture Used Car Business New Car New Car Furniture New Car Small Appliance Furniture Small Appliance Business Small Appliance New Car Education New Car New Car New Car Furniture Small Appliance Business Other Small Appliance New Car Small Appliance New Car Furniture Business Used Car Used Car Small Appliance Small Appliance Education Small Appliance Large Appliance Education Furniture Business New Car Repairs Furniture Small Appliance New Car Small Appliance Business New Car Small Appliance $0 $6,490 $734 $348 $0 $506 $0 $14,717 $172 $0 $644 $1,571 $0 $0 $617 $411 $0 $544 $586 $0 $0 $835 $0 $823 $0 $5,180 $0 $408 $0 $821 $522 $385 $585 $2,223 $5,588 $0 $0 $605 $352 $7,525 $0 $3,529 $2,715 $1,435 $560 $887 $895 $243 $305 $4,553 $0 $418 $0 $771 $0 $463 $8,948 $110 $0 $10,099 $0 $13,428 $0 $208 $0 $552 $0 $3,105 $483 $415 $0 $1,238 $0 $238 $0 $127 $663 $0 $624 $785 $0 $718 $0 $493 $152 $757 $0 $9,125 $0 $364 $498 $598 $0 $374 $156 $0 19 7 25 28 25 19 25 31 25 13 19 25 22 16 48 10 16 22 37 13 14 49 25 13 7 19 25 11 31 16 7 13 13 16 19 13 13 31 19 37 19 13 49 13 13 37 10 13 85 100 3 7 36 1 19 3 0 0 42 47 4 12 5 66 0 10 20 4 0 14 20 4 2 4 0 13 90 108 0 23 15 19 6 0 2 35 57 9 0 21 45 24 12 14 19 58 Used Car New Car Small Appliance Furniture New Car Used Car Business Furniture Used Car Small Appliance Small Appliance Business Furniture Small Appliance Small Appliance New Car New Car Used Car Small Appliance Business Education Furniture Business Other Furniture New Car New Car Business Used Car Business Small Appliance New Car New Car Furniture Repairs Small Appliance Small Appliance Furniture Furniture Furniture Furniture Other Education Small Appliance New Car Business Business Education $1,336 $0 $0 $0 $2,641 $0 $0 $0 $0 $0 $887 $0 $0 $0 $0 $18,408 $497 $0 $946 $986 $8,122 $0 $778 $645 $0 $682 $19,812 $0 $0 $859 $0 $0 $0 $0 $0 $0 $795 $0 $0 $0 $0 $852 $0 $0 $425 $0 $0 $0 $0 $508 $956 $636 $0 $1,519 $922 $180 $701 $296 $519 $800 $736 $11,838 $364 $212 $888 $999 $0 $578 $136 $734 $861 $855 $4,486 $2,017 $0 $500 $859 $3,305 $1,218 $9,016 $11,587 $8,944 $807 $867 $16,804 $347 $836 $142 $169 $3,613 $403 $836 $0 $11,481 $3,285 $164 37 13 25 22 13 40 19 5 22 16 7 49 13 7 5 13 16 25 16 28 22 37 49 25 10 37 25 25 31 25 13 49 22 25 25 31 49 16 16 7 19 61 7 25 19 25 7 13 11 3 4 41 71 74 29 2 108 8 42 2 6 70 35 9 3 0 83 1 4 111 21 17 3 85 37 1 89 26 38 22 46 66 75 27 40 5 4 53 6 83 5 0 7 18 21 65 New Car Used Car Used Car New Car New Car New Car Small Appliance New Car New Car Furniture Retraining New Car Education Education Furniture New Car Small Appliance Business New Car Small Appliance New Car Furniture New Car Furniture Small Appliance Small Appliance Furniture New Car New Car New Car New Car New Car Furniture Small Appliance Furniture New Car Repairs New Car Small Appliance Small Appliance New Car Used Car New Car Other Used Car Small Appliance Used Car New Car $11,072 $891 $0 $0 $219 $841 $8,060 $607 $0 $486 $0 $108 $0 $0 $0 $113 $1,613 $0 $757 $208 $0 $603 $0 $343 $977 $463 $197 $0 $0 $299 $0 $490 $0 $6,628 $0 $859 $0 $750 $256 $954 $296 $591 $0 $13,970 $0 $857 $0 $5,857 $298 $3,326 $0 $726 $8,636 $214 $0 $207 $0 $713 $19,766 $2,141 $0 $483 $0 $127 $0 $367 $0 $813 $4,089 $0 $0 $102 $271 $759 $949 $0 $0 $503 $911 $823 $0 $693 $0 $973 $0 $648 $0 $523 $271 $7,090 $0 $596 $0 $904 $0 $541 61 37 43 19 12 25 43 25 25 25 13 19 10 37 19 13 37 19 13 10 37 13 11 19 73 19 11 13 13 11 19 7 37 43 7 7 19 49 13 46 19 49 15 37 25 13 49 19 17 49 0 71 22 52 28 31 118 36 35 22 61 17 11 15 65 23 14 13 103 24 34 20 15 7 3 119 29 54 90 13 22 28 14 0 0 36 62 4 28 81 57 0 2 67 119 13 Furniture New Car Business Small Appliance New Car Education Education Furniture New Car Small Appliance Furniture Furniture Furniture Small Appliance Business Small Appliance Small Appliance Small Appliance Small Appliance Small Appliance New Car Furniture Education Repairs Small Appliance Furniture Small Appliance Small Appliance Repairs Small Appliance Small Appliance New Car Small Appliance New Car Business Small Appliance Used Car New Car Furniture New Car Used Car Small Appliance Used Car New Car Furniture Small Appliance Furniture Small Appliance $0 $154 $4,802 $0 $177 $0 $0 $337 $0 $716 $996 $837 $705 $0 $0 $7,710 $0 $531 $5,960 $129 $0 $941 $759 $596 $0 $987 $651 $0 $257 $460 $955 $0 $0 $798 $8,249 $0 $0 $959 $956 $1,482 $382 $883 $0 $12,721 $842 $0 $3,111 $0 $0 $302 $0 $538 $2,846 $0 $231 $702 $0 $2,688 $17,366 $0 $0 $425 $332 $214 $242 $0 $0 $272 $929 $124 $0 $17,124 $0 $612 $0 $862 $0 $146 $0 $14,190 $0 $396 $0 $519 $646 $0 $538 $344 $0 $204 $0 $148 $0 $435 $0 $914 37 37 49 25 19 49 25 25 13 13 13 10 37 37 49 49 25 31 11 46 31 37 37 13 10 25 13 10 10 16 13 25 19 7 9 13 49 49 25 37 49 31 25 13 31 43 19 19 2 12 9 107 33 83 24 114 5 16 111 18 101 102 75 29 42 77 21 19 20 31 9 27 30 59 14 99 89 21 10 2 6 90 1 95 32 62 46 92 73 23 9 40 5 2 16 0 New Car Used Car New Car New Car Small Appliance Small Appliance Furniture Furniture Small Appliance Small Appliance Business New Car New Car New Car Furniture Small Appliance Education Business Business New Car Used Car Used Car Large Appliance Furniture Business Furniture New Car Education Used Car New Car Furniture Furniture Used Car New Car Small Appliance Furniture New Car Furniture Used Car Used Car Education New Car New Car Small Appliance Furniture New Car $135 $2,472 $0 $10,417 $211 $16,630 $0 $642 $0 $296 $898 $478 $315 $122 $0 $0 $0 $670 $444 $3,880 $819 $0 $0 $0 $0 $0 $0 $0 $0 $0 $161 $0 $0 $789 $765 $0 $0 $983 $0 $0 $798 $0 $193 $497 $0 $0 $0 $0 $412 $19,811 $822 $0 $3,369 $0 $707 $818 $177 $4,071 $466 $460 $991 $17,653 $497 $4,014 $921 $0 $0 $607 $15,800 $369 $4,973 $0 $761 $471 $674 $547 $524 $815 $0 $989 $10,406 $957 $770 $950 $160 $276 $137 $579 $2,684 $0 $0 $0 37 37 25 13 8 11 25 13 7 19 22 10 13 37 7 22 41 31 28 23 13 37 16 7 25 40 25 7 37 13 13 19 11 31 10 19 37 13 13 25 25 22 13 7 31 25 7 41 22 27 5 47 17 65 26 93 105 40 3 109 3 4 24 21 51 37 23 17 40 23 17 30 92 52 69 40 106 13 4 0 24 11 3 5 7 91 25 70 5 51 53 103 of loans for each Loan Purpose. n of "Checking" for each loan purpose type. dian, and standard deviation for the column AP2. 2a. Use the norm.dist function to find the probability of "Months" being less than 24 months. 2b. Use the norm.inv function to find how many months puts a customer in the top 15%. Months (how long someone has been a customer) 13 25 19 13 40 11 13 14 37 25 49 11 10 25 13 31 49 13 28 7 34 13 25 37 11 22 19 49 37 19 37 31 13 16 49 5 25 9 49 13 49 25 37 31 12 7 19 17 25 19 25 16 28 22 25 37 13 19 19 37 29 11 13 19 25 16 13 31 7 25 22 10 28 13 37 13 13 49 43 25 12 61 19 19 16 25 37 13 37 31 10 7 7 25 25 25 46 13 49 19 19 10 13 19 25 13 19 16 49 13 13 19 25 37 25 19 25 25 49 9 13 7 25 19 19 25 37 31 16 13 13 22 28 25 25 25 19 16 13 25 19 13 13 13 13 31 12 25 28 16 25 13 25 25 19 19 25 37 25 19 25 19 22 49 10 13 28 13 37 11 37 13 7 7 25 22 16 19 22 37 25 13 6 13 19 19 7 25 28 25 19 25 31 25 13 19 25 22 16 48 10 16 22 37 13 14 49 25 13 7 19 25 11 31 16 7 13 13 16 19 13 13 31 19 37 19 13 49 13 13 37 10 13 37 13 25 22 13 40 19 5 22 16 7 49 13 7 5 13 16 25 16 28 22 37 49 25 10 37 25 25 31 25 13 49 22 25 25 31 49 16 16 7 19 61 7 25 19 25 7 13 61 37 43 19 12 25 43 25 25 25 13 19 10 37 19 13 37 19 13 10 37 13 11 19 73 19 11 13 13 11 19 7 37 43 7 7 19 49 13 46 19 49 15 37 25 13 49 19 37 37 49 25 19 49 25 25 13 13 13 10 37 37 49 49 25 31 11 46 31 37 37 13 10 25 13 10 10 16 13 25 19 7 9 13 49 49 25 37 49 31 25 13 31 43 19 19 37 37 25 13 8 11 25 13 7 19 22 10 13 37 7 22 41 31 28 23 13 37 16 7 25 40 25 7 37 13 13 19 11 31 10 19 37 13 13 25 25 22 13 7 31 25 16 7 AP3. 3a. Find a 95% confidence interval for the mean of "months employed." Write out the full confidence interval. You can use the either of the three methods. 3b. Explain what the confidence interval means. Months Employed 12 0 119 14 45 13 16 2 9 4 0 15 14 63 26 8 4 33 116 2 16 5 23 71 15 33 58 46 24 12 7 32 2 34 46 41 1 40 42 74 0 26 60 33 1 119 3 75 8 5 25 36 6 10 28 114 16 32 5 60 10 8 46 17 4 115 31 0 0 5 9 81 28 69 85 2 3 32 0 9 6 17 3 20 59 3 25 3 11 14 65 14 2 79 1 93 13 21 13 108 14 16 88 1 41 22 14 9 45 89 21 20 14 6 24 15 81 14 15 12 9 18 53 0 56 42 79 21 94 73 94 10 20 87 54 23 2 20 6 99 13 7 89 17 10 3 0 5 7 3 25 63 78 36 6 15 19 9 3 8 48 2 55 37 1 14 54 111 2 14 1 0 2 5 19 24 14 93 96 2 11 4 35 48 4 85 100 3 7 36 1 19 3 0 0 42 47 4 12 5 66 0 10 20 4 0 14 20 4 2 4 0 13 90 108 0 23 15 19 6 0 2 35 57 9 0 21 45 24 12 14 19 58 11 3 4 41 71 74 29 2 108 8 42 2 6 70 35 9 3 0 83 1 4 111 21 17 3 85 37 1 89 26 38 22 46 66 75 27 40 5 4 53 6 83 5 0 7 18 21 65 17 49 0 71 22 52 28 31 118 36 35 22 61 17 11 15 65 23 14 13 103 24 34 20 15 7 3 119 29 54 90 13 22 28 14 0 0 36 62 4 28 81 57 0 2 67 119 13 2 12 9 107 33 83 24 114 5 16 111 18 101 102 75 29 42 77 21 19 20 31 9 27 30 59 14 99 89 21 10 2 6 90 1 95 32 62 46 92 73 23 9 40 5 2 16 0 7 41 22 27 5 47 17 65 26 93 105 40 3 109 3 4 24 21 51 37 23 17 40 23 17 30 92 52 69 40 106 13 4 0 24 11 3 5 7 91 25 70 5 51 53 103 6 39 ull confidence interval. You can AP4. 4a. Compute net present value for the given data using a discount rate of 5%. 4b. Based on your calculations, is this a good investment to make? Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 Income $ (62,000) $ (74,000) $ (76,000) $ 100,000 $ 26,000 $ 74,000 $ 150,000 $ 88,000 $ 55,000 $ 12,000 AP5. Given the frequencies for each house age, compute the relative and the cumulative relative frequency. House Age Square Feet Market Value 33 33 33 33 33 33 32 32 32 32 32 32 1,812 1,812 2,028 1,850 1,666 1,666 1,914 1,842 1,836 1,732 1,791 1,852 $90,000.00 $91,000.00 $108,500.00 $96,000.00 $88,400.00 $87,500.00 $104,400.00 $93,300.00 $101,900.00 $87,600.00 $89,200.00 $100,800.00 32 32 32 32 32 32 32 32 28 28 28 28 28 28 28 28 28 28 28 28 28 27 27 27 27 27 27 27 27 27 1,620 1,692 2,372 2,372 2,123 1,620 1,731 1,666 1,520 1,588 1,598 1,484 1,484 1,520 1,484 1,468 1,520 1,520 1,588 1,784 1,520 1,484 1,701 1,520 1,484 1,668 1,484 1,520 1,684 1,581 $96,700.00 $87,500.00 $114,000.00 $113,200.00 $116,100.00 $94,700.00 $86,400.00 $87,100.00 $83,400.00 $81,500.00 $87,100.00 $82,600.00 $78,800.00 $87,600.00 $82,000.00 $88,100.00 $88,100.00 $84,400.00 $81,000.00 $91,300.00 $87,200.00 $79,800.00 $94,200.00 $88,600.00 $76,600.00 $90,900.00 $81,300.00 $100,700.00 $96,700.00 $120,700.00 AP6. 6. An online store offers free shipping on all purchases of $100 and over. For all items less than $100, shipping costs are equal to 10% of the purchase price. Given the list of purchases, create a column labeled \"final price\" that shows purchase price plus shipping where appropriate. (You can do this in multiple steps using any method you like, as long as the final price column is correct) Transactio Purchase n Number Amount Final Price 10001 10002 10003 10004 10005 10006 10007 10008 10009 10010 10011 10012 10013 10014 10015 10016 10017 10018 10019 10020 10021 10022 10023 10024 10025 10026 10027 10028 $209.37 $18 $22.83 $20.31 $22.06 $15.22 $20.60 $18.25 $174.18 $19.58 $17.91 $22.90 $22.26 $19.04 $17.42 $18.54 $19.74 $22.03 $236.49 $19.30 $23.73 $19.96 $20.75 $22.37 $24.03 $24.59 $155.91 $16.43 00, shipping costs are shipping where e column is correct)