i have posted first assignment then the preview steps. I would appreciate a detailed step by step explanation of each of the preview steps. your help is very much appreciated
F2 General Merge & Center S%) M Cities Austin Dallas-Fort Worth Houston Shipping $ 24 $ 20 $ 30 Shipped From AUS Shipping Shipping Cat Refund A C D 1 Input Area 2 Order Total Threshold s 1.000 3 Total Days Delivery Goal 10 4 Shipping Refund Rate SON 5 Dute Date Date Airport Ordered Shipped Arrived Total Days Code 7 2021-04-05 2021-04-06 2021-04-09 4 8 -2021-04-05 2021-04-07 2021-04-15 10 AUS 9 2021-06-05 2021-04-07 2021-04-16 11 LAH 10 2021-04-OS 2021-04-07 2021-04-12 7 DFW 11 2011-06-OS 2021-04-09 2011-04.16 11 AH 12 2021-04-05-2021-04-05 2021-04-12 7 OFW 13 2021-04-05 2022-04-08 2021-04-16 11 AUS 14 2921-04-05 2021-04-08 2021-04-15 9 DEW 15 2021-04-06 2021-06-06 2021-04-09 3 OFW 16 2021-04-06 2021-06-08 2021-04-19 13 AUS 17 2021-04-06 2021-04-08 2021-04-14 8 18 2021-04-06 2021-04-09 2021-04-19 10 Drw 19 202101072021-04-22 2021.04.16 9 LAN 20 2021-04-07 2021-06-09 2021-04-16 9 AUS 21 2021-04-07 2021-04-09 2021-04-14 7 ofw 22 2021-04-07 2021-04-20 2021-04:30 23 OFW 23 2021-04-07 2021-04-12 2021-04-16 9 IAN 24 2021-04-08 2021-04-09 2021-04-14 25 2021-04-08 2021-04-09 2021-04-16 6 LAH OFW 25 2021-04-08 2021-04-08 2021-04-12 OFW 27 2021 04 03 2021-04-09 2021-04-13 5 LAH 20 2021-04-08 2021-04-12 2021-04-16 AUS 20 2021-06-08 2021-04-13 2021.08.19 11 DAN 30 2021-04-OR 2021-04-12 2021-04-19 11 OFW 31 2011-04-09 2021-04-12 2021-06-19 AUS 12 2011-04-09. 2021-06-12 2021-06-16 7 VAH 39 2021-02-09 2021-04-12-2011-04-16 2 DIW 30 2021-04-09 2011-01-09 2021-06-20 11 AL 35 2011-04-09 2021-04-09 2021-04-20 11 36 37 Order Total 975 $ 1,055 $ 1,025 $ 1.000 $ 2.535 $ 1890 $ 950 5 1.485 $ 550 $ 1.250 $ 1.600 $ 2.500 $ 1.425 $ 800 5 1.000 $ 1.125 $ 1625 $ 1345 $ 2.100 $ 1,425 5 1.825 $ 2,055 $ 1,635 $ 1.000 5 2,005 $ 1,745 $ 2.100 $ 1.005 . 960 Ready Mar Lo D BIU A 1 be $ 2 3 4 LO 6 7 8. Q. W ERT Y CD BESU Formulas khosravi_Exp 19.Excel View Tell me Data Review Calibri (Body 8 ! A A SE P 36 Wrap Tout A Marge & Center E MO WY G H . OE oop 2021 Autosave OH Home Insert Draw Page Layout X Pasta A1 sx Statistics C D St AUS Totale Aws MAM Orden er 1,000 7 bed orden . 11 Date Ordered Agent 12 2011-04-05 11 AU 2021040 975 te 14 AUS 5 1055 11 15 $ 10 7 16 100 11 17 2.535 10 3 1,199 1 200100 AUS S 950 5 20 2011-06 w 5 1.45 DW 21 550 2010 11 AUS 22 2001-02 1.210 TAP 23 1021046 1600 13 ww 2.500 LAM 1.6025 AUS so 20910 2. W 5 1.000 23 Drw 201310 5 LEN OP 2010 1.05 2010 $ 3.345 DEW 201 2.100 R 2001008 DIW 5 AZS 11 08 LAH 15 5 AUS 2001040 7,055 3 1.425 2016 TE 10310400 DW 1.00 10 AU 2 11 2001 or AUS 5 1.003 11 DAN 5 Cote 10010405 2010 100 MO Sort ow SRO TE SERT HE ST 3 TE 20210.00 Web Ready LI esc BE B IV 2 AutoSave OF RESU. Home Insert Draw Page Layout Formulas Data Review View 11 * Pasta Calibr (Bodyl BTV 5x State III lile V A 19 Al X D E H 1 State 2 California Colorado Texas 5 Revenue $ 55,945 $ 30,650 $ 41.00 9 10 11 12 14 15 16 17 18 19 20 21 22 23 24 25 25 27 28 29 30 31 32 33 14 15 36 37 38 40 41 44 Wook MI Ready Loan Paste BIU Wrap Text Merge & Center v G . Interest Principal End Balance E Beg Balance $ 75,000.00 $ $ $ $ $ $ $ A1 fx Loan Information C D 1 Loan Information Payment 2 Loan (PV) $ 75,000.00 3 APR 5.00% 2 4 Monthly Rate 0.42% 3 5 Years for Loan 6 4 6 NPER 72 5 7 Monthly Payment $1.207.87 6 8 7 9 Cumulative Totalster Year 2 8 10 interest 9 21 Principal Paid 12 10 13 What if the $1.150 money payment 11 24 Month Payment 52 5 1.150.00 13 15 May Rate Needed 26 APR Need 14 15 18 16 15 17 20 18 19 20 23 21 24 23 25 5 $ $ $ $ $ 5 S 5 $ $ 5 $ 5 5 esc B @ Assignment Instructions pleted Assi 3 Copy the function to the range D8:035. Next, you want to display the city names that correspond with the city airport codes. 5 In cell F7, insert the SWITCH function to evaluate the airport code in cell E7. Include mixed cell references to the city names in the range F2:F4. Use the airport codes as text for the Value arguments. Copy the function to the range FB:F35. Now you want to display the standard shipping costs by city. 5 In cell H7, insert the IFS function to identify the shipping cost based on the airport code and the applicable shipping rates in the range G2:44. Use relative and mixed references correctly. Copy the function to the range H8:H35. Finally, you want to calculate a partial shipping refund if two conditions are met. in 5 In cell 17, insert an IF function with a nested AND function to determine shipping refunds. The AND function should ensure both conditions are met: Total Days is grater than Total Days Close Khosravi_Exp19_...zip Upload Complet Work Assignment on your Assignment Instructions refund is $0. Use mixed references as needed. Copy the function to the range 18:135 ow nt 6 The Stats worksheet contains similar data. Now you want to enter summary statistics. cell B2, insert the COUNTIF function to count the number of shipments for Austin (cell B1). Use appropriate mixed references to the range argument to keep the column letters the same. Copy the function to the range C2:02. In cell B3, insert the SUMIF function to calculate the total orders for Austin (cell B1). Use appropriate mixed references to the range argument to keep the column letters the same. Copy the function to the range C3:03. 7 8 In cell B4, insert the AVERAGEIF function to calculate the average number of days for shipments from Austin (cell B1). Use appropriate mixed references to the range argument to keep the column letters the same. Copy the function to the range C4:04. 9 Now you want to focus on shipments from Houston where the order was greater than $1,000. Close ork Assignment on your Upload Completed Assignment Instructions Now you want to focus on shipments from Houston where the order was greater than $1,000. 9 10 11 In cell C7, insert the COUNTIFS function to count the number of orders where the Airport Code is IAH (Cell D1) and the Order Total is greater than $1,000. In cell C8, insert the SUMIFS function to calculate the total orders where the Airport Code is IAH (Cell D1) and the Order Total is greater than $1,000. In cell C9, insert the MAXIFS function to return the highest order total where the Airport Code is IAH (Cell D1) and the Order Total is greater than $1,000 On the Map worksheet, insert a map for the states and revenues. Cut and paste the map in cell C1. Format the data series to show only regions with data and show all map labels. Change the map title to April 5-9 Gross Revenue. Use the Loan worksheet to complete the loan amortization table. 12 13 14 15 6 Close Upload Completed Assig Work Assignment on your Assignment Instructions X In cell 17, insert an IF function with a nested AND funcon to determine shipping refunds. The AND function should ensure both conditions are met: Total Days is grater than Total Days Delivery Goal (cell C3) and Order Total is equal to or greater than Order Total Threshold (cell C2). If both conditions are met, the refund is 50% (cell C4) of the Shipping Cost. Otherwise, the refund is $0. Use mixed references as needed. Copy the functie to the range 18:135 The Stats worksheet contains similar data. Now you want to enter summary statistics 5 In cell B2, insert the COUNTIF function to count the number of shipments for Austin (cell B1). Use appropriate mixed references to the range argument to keep the column letters the same. Copy the function to the range C2:02. In cell B3, insert the SUMIF function to calculate the total orders for Austin (cell B1). Use appropriate mixed references to the range argument to keep the column letters the same. Copy the function to the range C3:D3. In noll DA inert ihn AVEDACCIC funtion to inna tha 5 7 Close DCX Khosravi_Exp19_....zip Instructions Tanye digre U Nepie CIUITTRES S. Lupy wie function to the range C3:03, x 8 5 9 ch In cell B4, insert the AVERAGEIF function to calculate the average number of days for shipments from Austin (cell B1). Use appropriate mixed references to the range argument to keep the column letters the same. Copy the function to the range C4:04. Now you want to focus on shipments from Houston where the order was greater than $1,000. In cell C7, insert the COUNTIFS function to count the number of orders where the Airport Code is IAH (Cell D1) and the Order Total is greater than $1,000. In cell C8, insert the SUMIFS function to calculate the total orders where the Airport Code is IAH (Cell D1) and the Order Total is greater than $1,000. In cell C9, insert the MAXIFS function to return the highest order total where the Airport Code is IAH (Cell D1) and the Order Total is greater than $1,000 On the Map worksheet, insert a map for the states and revenues. 10 5 11 5 12 5 Close ex Khosravi_Exp19_...zip Upload Completed Work Assignment on your Assignment Instructions 12 Follow point On the Map worksheet, insert a map for the states and revenues. Cut and paste the map in cell C1. Format the data series to show only regions with data and show all map labels. 13 14 Change the map title to April 5-9 Gross Revenue, 15 Use the Loan worksheet to complete the loan amortization table. 16 In cell F2, insert the IPMT function to calculate the interest for the first payment. Copy the function to the range F3:F25. (The results will update after you complete the other functions and formulas.) In cell G2, insert the PPMT function to calculate the principal paid for the first payment. Copy the function to the range G3:G25. In cell H2, insert a formula to calculate the ending principal balance. Copy the formula to the range H3:H25. 17 18 Now you want to determine how much interest was paid during the first two years. ---- Close Work Assignment on your Assignment Instructions Upload Completed Assignm 11 Follow point 5 tio A wil ng 12 5 13 5 14 15 In cell C9insert the MAXIFS function to return the highest order total where the Airport Code is IAH (Cell D1) and the Order Total is greater than $1,000 On the Map worksheet, insert a map for the states and revenues. Cut and paste the map in cell C1. Format the data series to show only regions with data and show all map labels. Change the map title to April 5-9 Gross Revenue. Use the Loan worksheet to complete the loan amortization table. In cell F2, insert the IPMT function to calculate the interest for the first payment. Copy the function to the range F3:F25. (The results will update after you complete the other functions and formulas.) In cell G2, insert the PPMT function to calculate the principal pald for the first payment. Copy the function to the range G3:G25. In cell H2, insert a formula to calculate the ending principal balance. Copy the formula to the range H3:H25. Close 16 17 5 Bocx A Khosravi_Exp19.zip Work Assignment on your Assignment Instructions Upload Completed Assign 17 How int In cell H2, insert a formula to calculate the ending principal balance. Copy the formula to the range H3:H25. Now you want to determine how much interest was paid during the first two years 18 19 20 In cell B10, insert the CUMIPMT function to calculate the cumulative interest after the first two years. Make sure the result is positive In cell B11, insert the CUMPRINC function to calculate the cumulative principal paid at the end of the first two years. Make sure the result is positive You want to perform a what-if analysis to determine the rate if the monthly payment is $1,150 instead of $1,207.87. In cell B15, insert the RATE function to calculate the necessary monthly rate given the NPER, proposed monthly payment, and loan. Make sure the result is positive. Finally, you want to convert the monthly rate to an APR 5 21 5 Close Khosravi_Exp19_...zip Follow 5 point bo A will 5 ent on your Upload Completed Assignm Assignment Instructions x 19 In cell B11, insert the CUMPRINC function to calculate the cumulative principal paid at the end of the first two years. Make sure the result is positive 20 You want to perform a what-if analysis to determine the rate if the monthly payment is $1,150 instead of $1,207.87. In cell B15, insert the RATE function to calculate the necessary monthly rate given the NPER, proposed monthly payment, and loan. Make sure the result is positive. 21 Finally, you want to convert the monthly rate to an APR. In cell B16, insert a formula to calculate the APR for the monthly rate in cell B15. 22 Insert a footer on all sheets with your name on the left side, the sheet name code in the center, and the file name code on the right side Save and close Exp19_Excel_Cho7 CapAssessment_Shipping.xlsx. Exit Excel Submit the file as directed, 5 2 23 0 Close CX Khosravi_Exp19... 2.zip F2 General Merge & Center S%) M Cities Austin Dallas-Fort Worth Houston Shipping $ 24 $ 20 $ 30 Shipped From AUS Shipping Shipping Cat Refund A C D 1 Input Area 2 Order Total Threshold s 1.000 3 Total Days Delivery Goal 10 4 Shipping Refund Rate SON 5 Dute Date Date Airport Ordered Shipped Arrived Total Days Code 7 2021-04-05 2021-04-06 2021-04-09 4 8 -2021-04-05 2021-04-07 2021-04-15 10 AUS 9 2021-06-05 2021-04-07 2021-04-16 11 LAH 10 2021-04-OS 2021-04-07 2021-04-12 7 DFW 11 2011-06-OS 2021-04-09 2011-04.16 11 AH 12 2021-04-05-2021-04-05 2021-04-12 7 OFW 13 2021-04-05 2022-04-08 2021-04-16 11 AUS 14 2921-04-05 2021-04-08 2021-04-15 9 DEW 15 2021-04-06 2021-06-06 2021-04-09 3 OFW 16 2021-04-06 2021-06-08 2021-04-19 13 AUS 17 2021-04-06 2021-04-08 2021-04-14 8 18 2021-04-06 2021-04-09 2021-04-19 10 Drw 19 202101072021-04-22 2021.04.16 9 LAN 20 2021-04-07 2021-06-09 2021-04-16 9 AUS 21 2021-04-07 2021-04-09 2021-04-14 7 ofw 22 2021-04-07 2021-04-20 2021-04:30 23 OFW 23 2021-04-07 2021-04-12 2021-04-16 9 IAN 24 2021-04-08 2021-04-09 2021-04-14 25 2021-04-08 2021-04-09 2021-04-16 6 LAH OFW 25 2021-04-08 2021-04-08 2021-04-12 OFW 27 2021 04 03 2021-04-09 2021-04-13 5 LAH 20 2021-04-08 2021-04-12 2021-04-16 AUS 20 2021-06-08 2021-04-13 2021.08.19 11 DAN 30 2021-04-OR 2021-04-12 2021-04-19 11 OFW 31 2011-04-09 2021-04-12 2021-06-19 AUS 12 2011-04-09. 2021-06-12 2021-06-16 7 VAH 39 2021-02-09 2021-04-12-2011-04-16 2 DIW 30 2021-04-09 2011-01-09 2021-06-20 11 AL 35 2011-04-09 2021-04-09 2021-04-20 11 36 37 Order Total 975 $ 1,055 $ 1,025 $ 1.000 $ 2.535 $ 1890 $ 950 5 1.485 $ 550 $ 1.250 $ 1.600 $ 2.500 $ 1.425 $ 800 5 1.000 $ 1.125 $ 1625 $ 1345 $ 2.100 $ 1,425 5 1.825 $ 2,055 $ 1,635 $ 1.000 5 2,005 $ 1,745 $ 2.100 $ 1.005 . 960 Ready Mar Lo D BIU A 1 be $ 2 3 4 LO 6 7 8. Q. W ERT Y CD BESU Formulas khosravi_Exp 19.Excel View Tell me Data Review Calibri (Body 8 ! A A SE P 36 Wrap Tout A Marge & Center E MO WY G H . OE oop 2021 Autosave OH Home Insert Draw Page Layout X Pasta A1 sx Statistics C D St AUS Totale Aws MAM Orden er 1,000 7 bed orden . 11 Date Ordered Agent 12 2011-04-05 11 AU 2021040 975 te 14 AUS 5 1055 11 15 $ 10 7 16 100 11 17 2.535 10 3 1,199 1 200100 AUS S 950 5 20 2011-06 w 5 1.45 DW 21 550 2010 11 AUS 22 2001-02 1.210 TAP 23 1021046 1600 13 ww 2.500 LAM 1.6025 AUS so 20910 2. W 5 1.000 23 Drw 201310 5 LEN OP 2010 1.05 2010 $ 3.345 DEW 201 2.100 R 2001008 DIW 5 AZS 11 08 LAH 15 5 AUS 2001040 7,055 3 1.425 2016 TE 10310400 DW 1.00 10 AU 2 11 2001 or AUS 5 1.003 11 DAN 5 Cote 10010405 2010 100 MO Sort ow SRO TE SERT HE ST 3 TE 20210.00 Web Ready LI esc BE B IV 2 AutoSave OF RESU. Home Insert Draw Page Layout Formulas Data Review View 11 * Pasta Calibr (Bodyl BTV 5x State III lile V A 19 Al X D E H 1 State 2 California Colorado Texas 5 Revenue $ 55,945 $ 30,650 $ 41.00 9 10 11 12 14 15 16 17 18 19 20 21 22 23 24 25 25 27 28 29 30 31 32 33 14 15 36 37 38 40 41 44 Wook MI Ready Loan Paste BIU Wrap Text Merge & Center v G . Interest Principal End Balance E Beg Balance $ 75,000.00 $ $ $ $ $ $ $ A1 fx Loan Information C D 1 Loan Information Payment 2 Loan (PV) $ 75,000.00 3 APR 5.00% 2 4 Monthly Rate 0.42% 3 5 Years for Loan 6 4 6 NPER 72 5 7 Monthly Payment $1.207.87 6 8 7 9 Cumulative Totalster Year 2 8 10 interest 9 21 Principal Paid 12 10 13 What if the $1.150 money payment 11 24 Month Payment 52 5 1.150.00 13 15 May Rate Needed 26 APR Need 14 15 18 16 15 17 20 18 19 20 23 21 24 23 25 5 $ $ $ $ $ 5 S 5 $ $ 5 $ 5 5 esc B @ Assignment Instructions pleted Assi 3 Copy the function to the range D8:035. Next, you want to display the city names that correspond with the city airport codes. 5 In cell F7, insert the SWITCH function to evaluate the airport code in cell E7. Include mixed cell references to the city names in the range F2:F4. Use the airport codes as text for the Value arguments. Copy the function to the range FB:F35. Now you want to display the standard shipping costs by city. 5 In cell H7, insert the IFS function to identify the shipping cost based on the airport code and the applicable shipping rates in the range G2:44. Use relative and mixed references correctly. Copy the function to the range H8:H35. Finally, you want to calculate a partial shipping refund if two conditions are met. in 5 In cell 17, insert an IF function with a nested AND function to determine shipping refunds. The AND function should ensure both conditions are met: Total Days is grater than Total Days Close Khosravi_Exp19_...zip Upload Complet Work Assignment on your Assignment Instructions refund is $0. Use mixed references as needed. Copy the function to the range 18:135 ow nt 6 The Stats worksheet contains similar data. Now you want to enter summary statistics. cell B2, insert the COUNTIF function to count the number of shipments for Austin (cell B1). Use appropriate mixed references to the range argument to keep the column letters the same. Copy the function to the range C2:02. In cell B3, insert the SUMIF function to calculate the total orders for Austin (cell B1). Use appropriate mixed references to the range argument to keep the column letters the same. Copy the function to the range C3:03. 7 8 In cell B4, insert the AVERAGEIF function to calculate the average number of days for shipments from Austin (cell B1). Use appropriate mixed references to the range argument to keep the column letters the same. Copy the function to the range C4:04. 9 Now you want to focus on shipments from Houston where the order was greater than $1,000. Close ork Assignment on your Upload Completed Assignment Instructions Now you want to focus on shipments from Houston where the order was greater than $1,000. 9 10 11 In cell C7, insert the COUNTIFS function to count the number of orders where the Airport Code is IAH (Cell D1) and the Order Total is greater than $1,000. In cell C8, insert the SUMIFS function to calculate the total orders where the Airport Code is IAH (Cell D1) and the Order Total is greater than $1,000. In cell C9, insert the MAXIFS function to return the highest order total where the Airport Code is IAH (Cell D1) and the Order Total is greater than $1,000 On the Map worksheet, insert a map for the states and revenues. Cut and paste the map in cell C1. Format the data series to show only regions with data and show all map labels. Change the map title to April 5-9 Gross Revenue. Use the Loan worksheet to complete the loan amortization table. 12 13 14 15 6 Close Upload Completed Assig Work Assignment on your Assignment Instructions X In cell 17, insert an IF function with a nested AND funcon to determine shipping refunds. The AND function should ensure both conditions are met: Total Days is grater than Total Days Delivery Goal (cell C3) and Order Total is equal to or greater than Order Total Threshold (cell C2). If both conditions are met, the refund is 50% (cell C4) of the Shipping Cost. Otherwise, the refund is $0. Use mixed references as needed. Copy the functie to the range 18:135 The Stats worksheet contains similar data. Now you want to enter summary statistics 5 In cell B2, insert the COUNTIF function to count the number of shipments for Austin (cell B1). Use appropriate mixed references to the range argument to keep the column letters the same. Copy the function to the range C2:02. In cell B3, insert the SUMIF function to calculate the total orders for Austin (cell B1). Use appropriate mixed references to the range argument to keep the column letters the same. Copy the function to the range C3:D3. In noll DA inert ihn AVEDACCIC funtion to inna tha 5 7 Close DCX Khosravi_Exp19_....zip Instructions Tanye digre U Nepie CIUITTRES S. Lupy wie function to the range C3:03, x 8 5 9 ch In cell B4, insert the AVERAGEIF function to calculate the average number of days for shipments from Austin (cell B1). Use appropriate mixed references to the range argument to keep the column letters the same. Copy the function to the range C4:04. Now you want to focus on shipments from Houston where the order was greater than $1,000. In cell C7, insert the COUNTIFS function to count the number of orders where the Airport Code is IAH (Cell D1) and the Order Total is greater than $1,000. In cell C8, insert the SUMIFS function to calculate the total orders where the Airport Code is IAH (Cell D1) and the Order Total is greater than $1,000. In cell C9, insert the MAXIFS function to return the highest order total where the Airport Code is IAH (Cell D1) and the Order Total is greater than $1,000 On the Map worksheet, insert a map for the states and revenues. 10 5 11 5 12 5 Close ex Khosravi_Exp19_...zip Upload Completed Work Assignment on your Assignment Instructions 12 Follow point On the Map worksheet, insert a map for the states and revenues. Cut and paste the map in cell C1. Format the data series to show only regions with data and show all map labels. 13 14 Change the map title to April 5-9 Gross Revenue, 15 Use the Loan worksheet to complete the loan amortization table. 16 In cell F2, insert the IPMT function to calculate the interest for the first payment. Copy the function to the range F3:F25. (The results will update after you complete the other functions and formulas.) In cell G2, insert the PPMT function to calculate the principal paid for the first payment. Copy the function to the range G3:G25. In cell H2, insert a formula to calculate the ending principal balance. Copy the formula to the range H3:H25. 17 18 Now you want to determine how much interest was paid during the first two years. ---- Close Work Assignment on your Assignment Instructions Upload Completed Assignm 11 Follow point 5 tio A wil ng 12 5 13 5 14 15 In cell C9insert the MAXIFS function to return the highest order total where the Airport Code is IAH (Cell D1) and the Order Total is greater than $1,000 On the Map worksheet, insert a map for the states and revenues. Cut and paste the map in cell C1. Format the data series to show only regions with data and show all map labels. Change the map title to April 5-9 Gross Revenue. Use the Loan worksheet to complete the loan amortization table. In cell F2, insert the IPMT function to calculate the interest for the first payment. Copy the function to the range F3:F25. (The results will update after you complete the other functions and formulas.) In cell G2, insert the PPMT function to calculate the principal pald for the first payment. Copy the function to the range G3:G25. In cell H2, insert a formula to calculate the ending principal balance. Copy the formula to the range H3:H25. Close 16 17 5 Bocx A Khosravi_Exp19.zip Work Assignment on your Assignment Instructions Upload Completed Assign 17 How int In cell H2, insert a formula to calculate the ending principal balance. Copy the formula to the range H3:H25. Now you want to determine how much interest was paid during the first two years 18 19 20 In cell B10, insert the CUMIPMT function to calculate the cumulative interest after the first two years. Make sure the result is positive In cell B11, insert the CUMPRINC function to calculate the cumulative principal paid at the end of the first two years. Make sure the result is positive You want to perform a what-if analysis to determine the rate if the monthly payment is $1,150 instead of $1,207.87. In cell B15, insert the RATE function to calculate the necessary monthly rate given the NPER, proposed monthly payment, and loan. Make sure the result is positive. Finally, you want to convert the monthly rate to an APR 5 21 5 Close Khosravi_Exp19_...zip Follow 5 point bo A will 5 ent on your Upload Completed Assignm Assignment Instructions x 19 In cell B11, insert the CUMPRINC function to calculate the cumulative principal paid at the end of the first two years. Make sure the result is positive 20 You want to perform a what-if analysis to determine the rate if the monthly payment is $1,150 instead of $1,207.87. In cell B15, insert the RATE function to calculate the necessary monthly rate given the NPER, proposed monthly payment, and loan. Make sure the result is positive. 21 Finally, you want to convert the monthly rate to an APR. In cell B16, insert a formula to calculate the APR for the monthly rate in cell B15. 22 Insert a footer on all sheets with your name on the left side, the sheet name code in the center, and the file name code on the right side Save and close Exp19_Excel_Cho7 CapAssessment_Shipping.xlsx. Exit Excel Submit the file as directed, 5 2 23 0 Close CX Khosravi_Exp19... 2.zip
