W.C. Hicks Appliances You are an account manager for WC Hicks Appliances a local appliance store that also provides financing delivery, and instal- lation. As part of your daily tasks, you create an Excel workbook that reports sales, payment plan information, and summary statistik 11. Copy the function from cell F5 down through F13 to complete column Grat 12. Apply Currency format to column Insert Current Date In order to ensure proper documentation you want to insert the current date and time. 1. Open the 21 Appliances workbook and save it as cO2c1 Appliances LastFirst 2. Issert a function in cell B2 to display the current date and format as long Date 3. Set column B's width to Autofit. Create Item Lookup Your first task is to lookup linction based on the data in the range 1.2 to determine the name of the item purchased ind the corresponding prices on the provided Sumber 4. Insert function in cells to display the lem named be on the provided intory lookup information 5. Cow the function from cell cs down through 13 to complete column 6. Set column width to 12.5 7. Inserts function in cell ES to display the item price based on the provided inventory lookup information & Copy the function from cells down through 130 complete column 9. Apply Currency Number Formatto con Calculate the Monthly Payment Your next step is to calculate the periodic payment for each cus tomer's purchase. The payments are based on the years financed in column Gand the annual interest rate in cell B26. All accounts are paid on a monthly basis 13. Insert the function in cell 5 to calculate the first customer's monthly payment using appropriate rela tive and absolute cell references, 14. Copy the formula down the column 15. Insert a function in cell H14 to calculate the total of all monthly payments in column H. 16. Apply Currency Number Format to column H. Finalize the Workbook You perform some basic statistical calculations and finalue the workbook with formatting and page setup options 17. Insert a function in cell HT to calculate the total number of order 18. Insert a function in cell 1119 to calculate the lowest monthly payment in column H. 19. Insert a function in cell H 20 to calculate the average moothly payment in column H 20. serta function in cell 1121 to calculate the highest monthly payment in column H. 21. Insert a function in cell 1122 to calculate the median moothly payment in colunan H. 22. Apply Currency format to the range 1119.H22 23.footer with your name on the left side the sheet me in the center and the file came on the schide 24. Save and close the workbook. Based on your instructor's die bruit de l Appliances List Determine Delivery Fee You will calculate the total de for each customer ander The al is the purchase price plus addiwy cha 10. Innhson in cell to calculate the total that became chosen time delivery, there solenery chargeocall cell 125 to appropriate date and blote cell Arial v 22 ' 3 Wrap Text Paste BIU DO DE V Merge & Center A1 fx A B C D E G H W.C. Hicks Appliances Price Total Due Years 3 Monthly Payment 5 3 5 3 3 4 4 1 2 Date Prepared 3 4 Client Name SKU Item Name Delivery 5 Garten 294211 Yes 6 Lett 327064 Yes 1 Vang 446741 No & Ranger 643081 No 9 Williams 294211 No 10 Davis 446741 No 11 Jackson 306862 Yes 12 Garcia 843081 No 13 Broadna 446741 Yes 14 Totals 15 16 17 SKU Description Price 18 204211 Refrigerator $1,399.00 19 306062 Dishwasher 5809.99 20 327064 Microwave $379,99 21 446229 Oven $1,099.99 22 448741 Washer 5467,99 543081 Dryer $599,99 24 25 Dubeye 575.00 26 wala 22 Months Per Year 12 5 Statisties Number of Orders Lowest Monthly Payment Average Monthly Payment Maximum Monthly Payment Median Monthly Payment 25 29 30 31 33 M 35 36 17 39 40 41 43 esc Dn W.C. Hicks Appliances Price Total Due Monthly Payment Item Name Delivery Yes Yes Years 3 5 No No No No Yes No Yes 3 5 3 3 4 4 5 1 2 Date Prepared: 3 4 Client Name SKU 5 Garten 294211 6 Leggett 327064 7 Vong 446741 8 Ranger 643081 9 Williams 294211 10 Davis 446741 11 Jackson 306862 12 Garcia 643081 13 Broadnax 446741 14 Totals 15 16 17 SKU Description 18 294211 Refrigerator 19 306862 Dishwasher 20 327064 Microwave 21 446229 Oven 22 446741 Washer 23 643081 Dryer 24 25 Delivery Fee $75.00 26 Interest Rate 3.25% 27 Months Per Year 28 29 30 31 32 33 Price $1,399.00 $809.99 $379.99 $1,099.99 $467.99 $599.99 Statistics Number of Orders Lowest Monthly Payment Average Monthly Payment Maximum Monthly Payment Median Monthly Payment 12 W.C. Hicks Appliances You are an account manager for W.C. Hicks Appliances, a local II. Copy the function from cell F5 down through F13 to appliance store that also provides financing, delivery, and instal complete column Graf lation. As part of your daily tasks, you create an Excel workbook that reports sales, payment plan information, and summary 12. Apply Currency format to column F. statistics Calculate the Monthly Payment Insert Current Date Your next step is to calculate the periodic payment for each cus- In order to ensure proper documentation you want to insert the tomer's purchase. The payments are based on the years financed current date and time in column G and the annual interest rate in cell B26. All accounts 1. Open the c02c1 Appliances workbook and save it as are paid on a monthly basis. c02c1 Appliances LastFirst. 13. Insert the function in cell 5 to calculate the first 2. Insert a function in cell B2 to display the current customer's monthly payment using appropriate rela- date and format as a Long Date. tive and absolute cell references. 3. Set column B's width to Autofit. 14. Copy the formula down the column. 15. Insert a function in cell 114 to calculate the total of Create Item Lookup all monthly payments in column H. Your first task is to use a lookup function based on the data in the 16. Apply Currency Number Format to column II. range A18:C23 to determine the name of the item purchased and the corresponding price based on the provided SKU) number. Finalize the Workbook 4. Insert a function in cell C5 to display the item named You perform some basic statistical calculations and finalize the based on the provided inventory lookup information workbook with formatting and page setup options. 5. Copy the function from cell C5 down through C13 to complete column C. 17. Insert a function in cell H18 to calculate the total number of orders. 6. Set column C's width to 12.5. 18. Insert a function in cell H19 to calculate the lowest 7. Insert a function in cell E5 to display the item price monthly payment in column H. based on the provided inventory lookup information. 19. Insert a function in cell H20 to calculate the average 8. Copy the function from cell E5 down through E13 to monthly payment in column H. complete column E. 9. Apply Currency Number Format to column E. 20. Insert a function in cell H21 to calculate the highest monthly payment in column H. 21. Insert a function in cell H22 to calculate the median Determine Delivery Fee monthly payment in column H. You will calculate the total due for each customer's order. The 22. Apply Currency format to the range H19:122 total is the purchase price plus an optional $75.00 delivery 23. Insert a footer with your name on the left side the charge sheet name in the center and the file name on the 10. Insert an IF function in cell F5 to calculate the total right side. due. If the customer has chosen home delivery, there 24. Save and close the workbook. Based on your instructor's is an additional delivery charge located in cell B25. directions, submit b2c1 Appliances_ LastFirst. Be sure to use appropriate relative and absolute cell references W.C. Hicks Appliances You are an account manager for WC Hicks Appliances a local appliance store that also provides financing delivery, and instal- lation. As part of your daily tasks, you create an Excel workbook that reports sales, payment plan information, and summary statistik 11. Copy the function from cell F5 down through F13 to complete column Grat 12. Apply Currency format to column Insert Current Date In order to ensure proper documentation you want to insert the current date and time. 1. Open the 21 Appliances workbook and save it as cO2c1 Appliances LastFirst 2. Issert a function in cell B2 to display the current date and format as long Date 3. Set column B's width to Autofit. Create Item Lookup Your first task is to lookup linction based on the data in the range 1.2 to determine the name of the item purchased ind the corresponding prices on the provided Sumber 4. Insert function in cells to display the lem named be on the provided intory lookup information 5. Cow the function from cell cs down through 13 to complete column 6. Set column width to 12.5 7. Inserts function in cell ES to display the item price based on the provided inventory lookup information & Copy the function from cells down through 130 complete column 9. Apply Currency Number Formatto con Calculate the Monthly Payment Your next step is to calculate the periodic payment for each cus tomer's purchase. The payments are based on the years financed in column Gand the annual interest rate in cell B26. All accounts are paid on a monthly basis 13. Insert the function in cell 5 to calculate the first customer's monthly payment using appropriate rela tive and absolute cell references, 14. Copy the formula down the column 15. Insert a function in cell H14 to calculate the total of all monthly payments in column H. 16. Apply Currency Number Format to column H. Finalize the Workbook You perform some basic statistical calculations and finalue the workbook with formatting and page setup options 17. Insert a function in cell HT to calculate the total number of order 18. Insert a function in cell 1119 to calculate the lowest monthly payment in column H. 19. Insert a function in cell H 20 to calculate the average moothly payment in column H 20. serta function in cell 1121 to calculate the highest monthly payment in column H. 21. Insert a function in cell 1122 to calculate the median moothly payment in colunan H. 22. Apply Currency format to the range 1119.H22 23.footer with your name on the left side the sheet me in the center and the file came on the schide 24. Save and close the workbook. Based on your instructor's die bruit de l Appliances List Determine Delivery Fee You will calculate the total de for each customer ander The al is the purchase price plus addiwy cha 10. Innhson in cell to calculate the total that became chosen time delivery, there solenery chargeocall cell 125 to appropriate date and blote cell Arial v 22 ' 3 Wrap Text Paste BIU DO DE V Merge & Center A1 fx A B C D E G H W.C. Hicks Appliances Price Total Due Years 3 Monthly Payment 5 3 5 3 3 4 4 1 2 Date Prepared 3 4 Client Name SKU Item Name Delivery 5 Garten 294211 Yes 6 Lett 327064 Yes 1 Vang 446741 No & Ranger 643081 No 9 Williams 294211 No 10 Davis 446741 No 11 Jackson 306862 Yes 12 Garcia 843081 No 13 Broadna 446741 Yes 14 Totals 15 16 17 SKU Description Price 18 204211 Refrigerator $1,399.00 19 306062 Dishwasher 5809.99 20 327064 Microwave $379,99 21 446229 Oven $1,099.99 22 448741 Washer 5467,99 543081 Dryer $599,99 24 25 Dubeye 575.00 26 wala 22 Months Per Year 12 5 Statisties Number of Orders Lowest Monthly Payment Average Monthly Payment Maximum Monthly Payment Median Monthly Payment 25 29 30 31 33 M 35 36 17 39 40 41 43 esc Dn W.C. Hicks Appliances Price Total Due Monthly Payment Item Name Delivery Yes Yes Years 3 5 No No No No Yes No Yes 3 5 3 3 4 4 5 1 2 Date Prepared: 3 4 Client Name SKU 5 Garten 294211 6 Leggett 327064 7 Vong 446741 8 Ranger 643081 9 Williams 294211 10 Davis 446741 11 Jackson 306862 12 Garcia 643081 13 Broadnax 446741 14 Totals 15 16 17 SKU Description 18 294211 Refrigerator 19 306862 Dishwasher 20 327064 Microwave 21 446229 Oven 22 446741 Washer 23 643081 Dryer 24 25 Delivery Fee $75.00 26 Interest Rate 3.25% 27 Months Per Year 28 29 30 31 32 33 Price $1,399.00 $809.99 $379.99 $1,099.99 $467.99 $599.99 Statistics Number of Orders Lowest Monthly Payment Average Monthly Payment Maximum Monthly Payment Median Monthly Payment 12 W.C. Hicks Appliances You are an account manager for W.C. Hicks Appliances, a local II. Copy the function from cell F5 down through F13 to appliance store that also provides financing, delivery, and instal complete column Graf lation. As part of your daily tasks, you create an Excel workbook that reports sales, payment plan information, and summary 12. Apply Currency format to column F. statistics Calculate the Monthly Payment Insert Current Date Your next step is to calculate the periodic payment for each cus- In order to ensure proper documentation you want to insert the tomer's purchase. The payments are based on the years financed current date and time in column G and the annual interest rate in cell B26. All accounts 1. Open the c02c1 Appliances workbook and save it as are paid on a monthly basis. c02c1 Appliances LastFirst. 13. Insert the function in cell 5 to calculate the first 2. Insert a function in cell B2 to display the current customer's monthly payment using appropriate rela- date and format as a Long Date. tive and absolute cell references. 3. Set column B's width to Autofit. 14. Copy the formula down the column. 15. Insert a function in cell 114 to calculate the total of Create Item Lookup all monthly payments in column H. Your first task is to use a lookup function based on the data in the 16. Apply Currency Number Format to column II. range A18:C23 to determine the name of the item purchased and the corresponding price based on the provided SKU) number. Finalize the Workbook 4. Insert a function in cell C5 to display the item named You perform some basic statistical calculations and finalize the based on the provided inventory lookup information workbook with formatting and page setup options. 5. Copy the function from cell C5 down through C13 to complete column C. 17. Insert a function in cell H18 to calculate the total number of orders. 6. Set column C's width to 12.5. 18. Insert a function in cell H19 to calculate the lowest 7. Insert a function in cell E5 to display the item price monthly payment in column H. based on the provided inventory lookup information. 19. Insert a function in cell H20 to calculate the average 8. Copy the function from cell E5 down through E13 to monthly payment in column H. complete column E. 9. Apply Currency Number Format to column E. 20. Insert a function in cell H21 to calculate the highest monthly payment in column H. 21. Insert a function in cell H22 to calculate the median Determine Delivery Fee monthly payment in column H. You will calculate the total due for each customer's order. The 22. Apply Currency format to the range H19:122 total is the purchase price plus an optional $75.00 delivery 23. Insert a footer with your name on the left side the charge sheet name in the center and the file name on the 10. Insert an IF function in cell F5 to calculate the total right side. due. If the customer has chosen home delivery, there 24. Save and close the workbook. Based on your instructor's is an additional delivery charge located in cell B25. directions, submit b2c1 Appliances_ LastFirst. Be sure to use appropriate relative and absolute cell references