This is my homework for a class that requires you to work with excel. Ignore the data in the customer receipt and calculations although the data in model inputs is accurate. I need help finding how to calculate the loyalty discount and amount as well as the transaction total. I have been stuck on this problem for 2 hours already.

SUM x x L M A Howard has implemented a customer loyalty program to increase sales. This program has two elements, int customer is entitled to discount for each transaction. The discount amount depends on their loyalty status (silver or cold and the amount of the transaction. The details of the loyalty program are included in the puts section of the Helpt worksheet Second, customer cars loyalty cash that can be applied toward a purchase. A customer carns 5001 for every dollar they spend. The customer loyalty program has made catevating the amount of money owed for a given transaction difficult to calculate Complete the calculations section of the worksheet to determine the transaction totals, any discounts the customer cars, if loyalty cash is used toward the transaction, and the remaining loyalty cach balance for the customer. Notice that this information is summarised in the Customer Receipt. The customer receiptis already completed for you 10 Calculations Customer Receipt Quicktove, LLC 10/7/21 1509 Sale Worksheet Number of items Purchased Total Price of items Model Inputs Transaction information hems Purchased 3 Total Price of Items 517523 Sales Tax Rate 6.75% Customer information Loyatly Status Silver Loyalty Cash Available 51454 Use Loyalty Cath? Payment Method cath $175 23 Sale Summary of items Purchased Hem Total Sales Tax Transaction Total 3 $175.23 $11.59 $116.82 Loyalty Discount Loyalty Discounts $3.50 Discount 2% Discounted Subtotal Sales Tax Transaction Total $3.50 $11:59 518312 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 Discounts and Orlits Aplied Loyalty Discount $350 Cash Discount $0.00 Loyatly Cash Applied 51454 Payment Applied $168.78 Discounts and Payments $186.30 Silver Sale Total 0 50 100 200 Loyalty Program Discount Rates Gold Discount Sale Total 25 3X 25 4% 50 5 300 200 5% 10% Payment Worksheet Transaction Total LoyaltyCash Applied Balance After Loyalty Cash Cath Discount $183 32 514.54 $168.20 50.00 Loyalty Cash Remaining 51.62 $168.73 Total Due from Customer Thank you for shopping at Quito Credit Worksheet Beginning Loyalty cash Loyalty Cash Used Loyalty Cash Carned Ending Loyalty Cash $1454 $14.54 $1.69 $1.69 35 CRICICLISTOMICILIONDJUTITTCNTCUCCUSCUITICI Receipt. The customer receipt is already completed for you. Task # Points Task Description 1 1 2 Input the number of items purchased in cell F13 by referencing the appropriate cell in the model inputs. 2 N Input the total price of items purchased in cell F14 by referencing the appropriate cell in the model inputs. 3 8 Calculate the Loyalty Discount % in cell F16. Notice that the discount rates depend both on the loyalty status of the customer (gold or silver) and the total price of the items purchased (cell F14). Construct your formula so that it will correctly calculate the loyalty percentage regardless of the Loyalty Status and Total Price of Items in the Model Inputs. Calculate the Loyalty Discount $ in cell F17. The loyalty discount $ is the total price of the items purchased (cell F14) times the loyalty discount percent (cell F16). Calculate the Discounted Subtotal in cell F19. The discounted subtotal is the difference between the total price of the items purchased (cell F14) and the loyalty discount $ (cell F17). 4 3 5 3 6 3 Calculate the Sales Tax for the transaction in cell F20. The sales tax is the discounted subtotal (cell F19) times the sales tax rate in the model inputs. 73 Calculate the Transaction Total in cell F21. The transaction total is the sum of the discounted subtotal (cell F19) and the sales tax (cell F20). SUM x x L M A Howard has implemented a customer loyalty program to increase sales. This program has two elements, int customer is entitled to discount for each transaction. The discount amount depends on their loyalty status (silver or cold and the amount of the transaction. The details of the loyalty program are included in the puts section of the Helpt worksheet Second, customer cars loyalty cash that can be applied toward a purchase. A customer carns 5001 for every dollar they spend. The customer loyalty program has made catevating the amount of money owed for a given transaction difficult to calculate Complete the calculations section of the worksheet to determine the transaction totals, any discounts the customer cars, if loyalty cash is used toward the transaction, and the remaining loyalty cach balance for the customer. Notice that this information is summarised in the Customer Receipt. The customer receiptis already completed for you 10 Calculations Customer Receipt Quicktove, LLC 10/7/21 1509 Sale Worksheet Number of items Purchased Total Price of items Model Inputs Transaction information hems Purchased 3 Total Price of Items 517523 Sales Tax Rate 6.75% Customer information Loyatly Status Silver Loyalty Cash Available 51454 Use Loyalty Cath? Payment Method cath $175 23 Sale Summary of items Purchased Hem Total Sales Tax Transaction Total 3 $175.23 $11.59 $116.82 Loyalty Discount Loyalty Discounts $3.50 Discount 2% Discounted Subtotal Sales Tax Transaction Total $3.50 $11:59 518312 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 Discounts and Orlits Aplied Loyalty Discount $350 Cash Discount $0.00 Loyatly Cash Applied 51454 Payment Applied $168.78 Discounts and Payments $186.30 Silver Sale Total 0 50 100 200 Loyalty Program Discount Rates Gold Discount Sale Total 25 3X 25 4% 50 5 300 200 5% 10% Payment Worksheet Transaction Total LoyaltyCash Applied Balance After Loyalty Cash Cath Discount $183 32 514.54 $168.20 50.00 Loyalty Cash Remaining 51.62 $168.73 Total Due from Customer Thank you for shopping at Quito Credit Worksheet Beginning Loyalty cash Loyalty Cash Used Loyalty Cash Carned Ending Loyalty Cash $1454 $14.54 $1.69 $1.69 35 CRICICLISTOMICILIONDJUTITTCNTCUCCUSCUITICI Receipt. The customer receipt is already completed for you. Task # Points Task Description 1 1 2 Input the number of items purchased in cell F13 by referencing the appropriate cell in the model inputs. 2 N Input the total price of items purchased in cell F14 by referencing the appropriate cell in the model inputs. 3 8 Calculate the Loyalty Discount % in cell F16. Notice that the discount rates depend both on the loyalty status of the customer (gold or silver) and the total price of the items purchased (cell F14). Construct your formula so that it will correctly calculate the loyalty percentage regardless of the Loyalty Status and Total Price of Items in the Model Inputs. Calculate the Loyalty Discount $ in cell F17. The loyalty discount $ is the total price of the items purchased (cell F14) times the loyalty discount percent (cell F16). Calculate the Discounted Subtotal in cell F19. The discounted subtotal is the difference between the total price of the items purchased (cell F14) and the loyalty discount $ (cell F17). 4 3 5 3 6 3 Calculate the Sales Tax for the transaction in cell F20. The sales tax is the discounted subtotal (cell F19) times the sales tax rate in the model inputs. 73 Calculate the Transaction Total in cell F21. The transaction total is the sum of the discounted subtotal (cell F19) and the sales tax (cell F20)