50.1K

Verified Solution

Link Copied!

Hi everyone. Please see attached the excel document to use for your case study. As mentioned in class, it is due the 16th (same day as final) by 7:00pm. If you have any questions, feel free to email me!

Excel Case Study-1.xlsx download

Rubric:

• 2 pts: Successfully performed a V-lookup to get City & Product Cost information in the Sales worksheet
• 3 pts: Used nested if function, to get the Sales Amount after discount
• 2 pts: Successfully Created a Pivot table to show the Sales Amount after Discount by City and Product ID
• 3 pts: Successfully created an amortization schedule.
• can you please change the numbers a littile bit

Product ID Sum of Sales Amou Sum of Sales Amount after discount 1 2 3 4 31500 11000 4350 9450 800 23750 4600 5 31050 10700 4290 9157.5 785 23370 4560 1900 6 7 8 2000 9 3 City Boston 5 Boston 6 Boston Boston 8 Boston 9 Boston 10 Boston 11 Boston 12 Boston 13 Boston 14 Boston 15 Chicago 16 Chicago 17 Chicago 18 Chicago 19 Chicago 20 Chicago 21 Chicago 22 Chicago 23 Chicago 24 Chicago 25 Chicago 26 Dallas 1280 6050 1228 6050 10 1 58500 34000 2 3 2850 6750 4 5 57225 33400 2820 6547.5 1100 18762.5 5310 5900 1472 9597.5 6 1100 19000 5400 6000 1520 9625 7 8 9 10 1 2 3 4 27 Dallas 28 Dallas 29 Dallas 30 Dallas 31 Dallas 32 Dallas 33 Dallas 34 Dallas 13200 29500 2527.5 10192.5 1035 12207.5 5520 8087.5 1800 G25 13500 30000 2550 10350 1050 12350 5600 8250 1840 5 6 7 8 t's safer to stay in Protected View B Enable Editing Option Bank Name 5 1 62 Bank A c D E F G H Number of Compounding Annual Interest Loan Duration Periods per Year Present Rate in Years Payment per Period Future Value 12 9.20% 5 $ (1,564) $ 75,000 S 4 6.75% 3.5 $ (5,656) $ 70,000 $ 12 15.00% 4 $ (1,658.11) $ 72,000 $ 2 3.80% 3 $ (3,397.814) $ 65,000 $ Total Yearly M Bank B N 73 Value Bank C Payments 83 Bank D $(18,770) 9 10 $(67,869) $(19,897) $(40,774) Loan Interest Period $75,000 0.007666667 60 Pmt $1,564 1,564 71 12 Choose a loan option that you think will be optimal for the business and create an amortization schedule for the same. 13 Period Remaining 14 Principal Interest Payment Principal 15 1 $ Payment 75,000 $ 16 575.000$ 989.000 2 $ 74,011 $ 567.418 $ 17 3 $ 996.582 73,014 $ 559.777 $ 18 1,004.223 4 $ 72,010$ 552.078$ 19 1,011.922 5 $ 70,998$ 544.320 S 1,019.680 20 6 $ 69,979 $ 536.503 $ 1,027.497 21 7 $ 68,951 $ 528.625 $ 1,035.375 22 8 $ 67,916 $ 520.687 $ 1,043.313 23 9 $ 66,872 $ 512.688$ 1,051.312 22 10 $ 65,821 S 504.628 $ 1,059.372 25 11 $ 64,762 $ 496.507 $ 1,067.493 25 12 $ 63,694 $ 488.322 $ 1,075.678 27 13 $ 62,619 $ 480.076 $ 1,083.924 28 14 $ 61,535$ 471.765 S 1,092.235 29 15 $ 60,442 $ 463.392 $ 1,100.608 30 16) $ 59,342 $ 454.954 $ 1,109.046 es 446.451 S 58,233 $ 31 17 $ 1,117.549 18$ 437.883$ 32 57,115$ 1,126.117 17.00 429.250 $ 33 19 $ 55,989 $ 109.254 420.550 $ 1,143.450 34 54,854 S 201 S 420.959 411.783 S 1.152.217 53,711 $ 35 21 s 402.950 $ 36 52,559 $ 1.161.050 402.950 22 s 394.048$ 1.169.952 37 23 S 385.079 S 38 24 $ 376.040 S 39 25 S 1.197.067 40 26 s 1,206.245 41 27 s 25.057S 1,134.750 1,178.921 1,187.960 51,398 $ 50,228s 49,049 $ 47,861 S 46,664 S 366.933$ 357.755 $ 348.507S 1,215.493 Ceas Fince Functions fo A F L M City 9 B D E G H 1 N Z Ltd - Product and Store Information 2 Product Information Store Information This worksheet provides product and store information for 8 Product ID Product Name Product Cost Store ID different home furniture pieces. This sheet contains two 4 1 Sofa 1500 101 New York City tables. The first table, Product Information, takes a look at 5 2 Bed 1000 102 Philadelphia each products ID, respective name, and cost. The second 6 3 Study Table 150 103 Boston table, Store Information, looks at the stores ID and the city 7 4 Recliner 450 104 it's located in. Chicago 8 5 Chair 50 105 Los Angeles 6 Dining set Below, see instructions that you will need to complete for 950 106 San Fransisco the following sheets. 10 7 TV Stand 200 107 Dallas 11 8 Dresser 250 108 Seattle 12 9 Book Case 80 109 Miami 13 10 Storage Cabinet 275 110 Salt Lake City 14 15 16 Perform a V-look up to get the City and Product Cost values. 17 Calculate the Sales Amount(Product Cost * Quantity) 18 Use the Month Function to get the month number The company offers a discount to all purchases made during the months of January and December(see cells A2:B4 in "Sales" worksheet) After populating the month number in column" l", use an If function to calculate the discount based on the purchase month(All purchases except the one made in January and December have a 0% discount) Calculate the Sales Amount after discount(Sales Amount-Discount) Create a Pivot Table in a new worksheet to show the Sales Amount before and after discount by City and Product ID. 28 30 31 32 33 34 35 36 37 38 Product and Store Information Pivot Table Sales Finance Functions uses. Unless you need to edit, it's safer to stay in Protected View =VLOOKUP(E10, Dataw,2,FALSE) B Enable Editing C Month Sales Data Discount 5% D E 1 F G H 12 10% Product ID 9 3 3 9 3 10 6 3 7 Product Cost 80 150 80 150 275 950 150 200 Discount 0 0 0 0 0 0 0 9 - 10 0 9 8 1 Date Customer Name 9/26/2020 Clayton Friatt 7/28/2020 Winny Pilpovic 5/16/2020 Durante Belchambers 6/14/2020 Constantin Raybould 7/13/2020 Hedvige Godbert 10/13/2020 Jackquelin Udy 2/8/2020 Ric Steinor 2/11/2020 Benedikt Kitlee 11/20/2020 Anestassia Baudain 11/13/2020 Elyn Standon 9/14/2020 Amara Fulbrook 9/2/2020 Rees Haddington 2/27/2020 Travus Huntar 1/29/2020 Chicky Cromack 1/17/2020 Lisetta Bruhnicke 2/11/2020 Andee Bennington 2/17/2020 Mead Aspin 10/8/2020 Glendon Danet 2/24/2020 Artair Novotna 8/25/2020 Horatia Butler 3/2/2020 Brigglfill 10/27/2020 Oliy Blackhurst 7/9/2020 Victoria Dudden 12/15/2020 Roxine Leither 1/18/2020 Joaquin Gillatt 9/19/2020 Alec Lillicrop 11/12/2020 Glennis Padden Olvan Janssen Abagael Dymick 12/7/2020 Malchy Winckworth Berke Deehan 4/9/2020 10/28/2020 Kingsley Ower Product and Store Information 8 6 1 1 4 1 1 Store ID City Quantity Sales Amount Month 110 Salt Lake City 109 240 Miami 9 1 150 109 Miami 7 1 80 109 Miami 5 1 150 104 Chicago 6 1 275 104 7 Chicago 1 950 109 20 Miami 1 150 2 107 Dallas 3 600 2 109 Miami 3 240 11 104 Chicago 2 550 11 106 San Fransisco 3 240 9 9 109 Miami 3 750 9 105 Los Angeles 3 4500 2 104 Chicago 3 750 1 103 Boston 2 950 1 108 Seattle 1 1500 2 103 Boston 2 3000 2 103 Bostoni 2 300 10 103 Boston 3 3 2 105 Los Angeles 3 4500 8 110 Salt Lake City 2 3000 3 101 New York City 3 600 10 109 Miami 1 250 7 104 Chicago 3 3000 12 104 Chicago 2 550 1 Chicago 3 104 3000 9 80 1 101 11 New York City 50 12 1 105 Los Angeles 1 450 1 12 Chicago 104 12 450 1 Miami 109 4 550 1 102 Philadelphia 10 100 2 110 Salt Lake City Sales Amount aller discount 240 150 80 150 275 950 150 600 240 550 240 750 4500 7125 902.5 1500 3000 300 2850 4500 3000 600 250 2700 3 6 80 275 80 250 1500 250 950 1500 1500 150 950 1500 1500 200 250 1000 275 1000 se se 450 450 950 50 2850 0 0 0 0 0 37.5 47.5 0 0 0 0 0 0 0 0 300 27.5 0 0 5 45 45 1 1 7 8 2 10 3000 2 30 9 5 405 12/24/2020 12/17/2020 4 403 4 0 100 6 0 5 Pivot Table Finance Functions Sales cady Product ID Sum of Sales Amou Sum of Sales Amount after discount 1 2 3 4 31500 11000 4350 9450 800 23750 4600 5 31050 10700 4290 9157.5 785 23370 4560 1900 6 7 8 2000 9 3 City Boston 5 Boston 6 Boston Boston 8 Boston 9 Boston 10 Boston 11 Boston 12 Boston 13 Boston 14 Boston 15 Chicago 16 Chicago 17 Chicago 18 Chicago 19 Chicago 20 Chicago 21 Chicago 22 Chicago 23 Chicago 24 Chicago 25 Chicago 26 Dallas 1280 6050 1228 6050 10 1 58500 34000 2 3 2850 6750 4 5 57225 33400 2820 6547.5 1100 18762.5 5310 5900 1472 9597.5 6 1100 19000 5400 6000 1520 9625 7 8 9 10 1 2 3 4 27 Dallas 28 Dallas 29 Dallas 30 Dallas 31 Dallas 32 Dallas 33 Dallas 34 Dallas 13200 29500 2527.5 10192.5 1035 12207.5 5520 8087.5 1800 G25 13500 30000 2550 10350 1050 12350 5600 8250 1840 5 6 7 8 t's safer to stay in Protected View B Enable Editing Option Bank Name 5 1 62 Bank A c D E F G H Number of Compounding Annual Interest Loan Duration Periods per Year Present Rate in Years Payment per Period Future Value 12 9.20% 5 $ (1,564) $ 75,000 S 4 6.75% 3.5 $ (5,656) $ 70,000 $ 12 15.00% 4 $ (1,658.11) $ 72,000 $ 2 3.80% 3 $ (3,397.814) $ 65,000 $ Total Yearly M Bank B N 73 Value Bank C Payments 83 Bank D $(18,770) 9 10 $(67,869) $(19,897) $(40,774) Loan Interest Period $75,000 0.007666667 60 Pmt $1,564 1,564 71 12 Choose a loan option that you think will be optimal for the business and create an amortization schedule for the same. 13 Period Remaining 14 Principal Interest Payment Principal 15 1 $ Payment 75,000 $ 16 575.000$ 989.000 2 $ 74,011 $ 567.418 $ 17 3 $ 996.582 73,014 $ 559.777 $ 18 1,004.223 4 $ 72,010$ 552.078$ 19 1,011.922 5 $ 70,998$ 544.320 S 1,019.680 20 6 $ 69,979 $ 536.503 $ 1,027.497 21 7 $ 68,951 $ 528.625 $ 1,035.375 22 8 $ 67,916 $ 520.687 $ 1,043.313 23 9 $ 66,872 $ 512.688$ 1,051.312 22 10 $ 65,821 S 504.628 $ 1,059.372 25 11 $ 64,762 $ 496.507 $ 1,067.493 25 12 $ 63,694 $ 488.322 $ 1,075.678 27 13 $ 62,619 $ 480.076 $ 1,083.924 28 14 $ 61,535$ 471.765 S 1,092.235 29 15 $ 60,442 $ 463.392 $ 1,100.608 30 16) $ 59,342 $ 454.954 $ 1,109.046 es 446.451 S 58,233 $ 31 17 $ 1,117.549 18$ 437.883$ 32 57,115$ 1,126.117 17.00 429.250 $ 33 19 $ 55,989 $ 109.254 420.550 $ 1,143.450 34 54,854 S 201 S 420.959 411.783 S 1.152.217 53,711 $ 35 21 s 402.950 $ 36 52,559 $ 1.161.050 402.950 22 s 394.048$ 1.169.952 37 23 S 385.079 S 38 24 $ 376.040 S 39 25 S 1.197.067 40 26 s 1,206.245 41 27 s 25.057S 1,134.750 1,178.921 1,187.960 51,398 $ 50,228s 49,049 $ 47,861 S 46,664 S 366.933$ 357.755 $ 348.507S 1,215.493 Ceas Fince Functions fo A F L M City 9 B D E G H 1 N Z Ltd - Product and Store Information 2 Product Information Store Information This worksheet provides product and store information for 8 Product ID Product Name Product Cost Store ID different home furniture pieces. This sheet contains two 4 1 Sofa 1500 101 New York City tables. The first table, Product Information, takes a look at 5 2 Bed 1000 102 Philadelphia each products ID, respective name, and cost. The second 6 3 Study Table 150 103 Boston table, Store Information, looks at the stores ID and the city 7 4 Recliner 450 104 it's located in. Chicago 8 5 Chair 50 105 Los Angeles 6 Dining set Below, see instructions that you will need to complete for 950 106 San Fransisco the following sheets. 10 7 TV Stand 200 107 Dallas 11 8 Dresser 250 108 Seattle 12 9 Book Case 80 109 Miami 13 10 Storage Cabinet 275 110 Salt Lake City 14 15 16 Perform a V-look up to get the City and Product Cost values. 17 Calculate the Sales Amount(Product Cost * Quantity) 18 Use the Month Function to get the month number The company offers a discount to all purchases made during the months of January and December(see cells A2:B4 in "Sales" worksheet) After populating the month number in column" l", use an If function to calculate the discount based on the purchase month(All purchases except the one made in January and December have a 0% discount) Calculate the Sales Amount after discount(Sales Amount-Discount) Create a Pivot Table in a new worksheet to show the Sales Amount before and after discount by City and Product ID. 28 30 31 32 33 34 35 36 37 38 Product and Store Information Pivot Table Sales Finance Functions uses. Unless you need to edit, it's safer to stay in Protected View =VLOOKUP(E10, Dataw,2,FALSE) B Enable Editing C Month Sales Data Discount 5% D E 1 F G H 12 10% Product ID 9 3 3 9 3 10 6 3 7 Product Cost 80 150 80 150 275 950 150 200 Discount 0 0 0 0 0 0 0 9 - 10 0 9 8 1 Date Customer Name 9/26/2020 Clayton Friatt 7/28/2020 Winny Pilpovic 5/16/2020 Durante Belchambers 6/14/2020 Constantin Raybould 7/13/2020 Hedvige Godbert 10/13/2020 Jackquelin Udy 2/8/2020 Ric Steinor 2/11/2020 Benedikt Kitlee 11/20/2020 Anestassia Baudain 11/13/2020 Elyn Standon 9/14/2020 Amara Fulbrook 9/2/2020 Rees Haddington 2/27/2020 Travus Huntar 1/29/2020 Chicky Cromack 1/17/2020 Lisetta Bruhnicke 2/11/2020 Andee Bennington 2/17/2020 Mead Aspin 10/8/2020 Glendon Danet 2/24/2020 Artair Novotna 8/25/2020 Horatia Butler 3/2/2020 Brigglfill 10/27/2020 Oliy Blackhurst 7/9/2020 Victoria Dudden 12/15/2020 Roxine Leither 1/18/2020 Joaquin Gillatt 9/19/2020 Alec Lillicrop 11/12/2020 Glennis Padden Olvan Janssen Abagael Dymick 12/7/2020 Malchy Winckworth Berke Deehan 4/9/2020 10/28/2020 Kingsley Ower Product and Store Information 8 6 1 1 4 1 1 Store ID City Quantity Sales Amount Month 110 Salt Lake City 109 240 Miami 9 1 150 109 Miami 7 1 80 109 Miami 5 1 150 104 Chicago 6 1 275 104 7 Chicago 1 950 109 20 Miami 1 150 2 107 Dallas 3 600 2 109 Miami 3 240 11 104 Chicago 2 550 11 106 San Fransisco 3 240 9 9 109 Miami 3 750 9 105 Los Angeles 3 4500 2 104 Chicago 3 750 1 103 Boston 2 950 1 108 Seattle 1 1500 2 103 Boston 2 3000 2 103 Bostoni 2 300 10 103 Boston 3 3 2 105 Los Angeles 3 4500 8 110 Salt Lake City 2 3000 3 101 New York City 3 600 10 109 Miami 1 250 7 104 Chicago 3 3000 12 104 Chicago 2 550 1 Chicago 3 104 3000 9 80 1 101 11 New York City 50 12 1 105 Los Angeles 1 450 1 12 Chicago 104 12 450 1 Miami 109 4 550 1 102 Philadelphia 10 100 2 110 Salt Lake City Sales Amount aller discount 240 150 80 150 275 950 150 600 240 550 240 750 4500 7125 902.5 1500 3000 300 2850 4500 3000 600 250 2700 3 6 80 275 80 250 1500 250 950 1500 1500 150 950 1500 1500 200 250 1000 275 1000 se se 450 450 950 50 2850 0 0 0 0 0 37.5 47.5 0 0 0 0 0 0 0 0 300 27.5 0 0 5 45 45 1 1 7 8 2 10 3000 2 30 9 5 405 12/24/2020 12/17/2020 4 403 4 0 100 6 0 5 Pivot Table Finance Functions Sales cady

Answer & Explanation Solved by verified expert

See Answer