Input the total price of items purchased in cell F14 by referencing the appropriate cell in the model inputs. 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). 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. 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). Calculate Loyalty Cash Applied in cell F25. If the customer wishes to use loyalty cash (cell L14 of the model inputs), loyalty cash applied is equal to the total loyalty cash available if the transaction total exceeds the loyalty cash available. If the loyalty cash available exceeds the transaction total, then the loyalty cash applied is equal to the transaction total. Loyalty cash applied is ziyo if the customer does not wish to use the loyalty cash. For this task, reference the transaction total in cell F24 rather than the value you calculated in cell F21. Calculate the Balance Alter Loyalty Cash in cell F26. The balance after loyalty case is the difference between the mention total (Cell F24) and any loyalty cash applied (cell F25). Input the total price of items purchased in cell F14 by referencing the appropriate cell in the model inputs. 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). 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. 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). Calculate Loyalty Cash Applied in cell F25. If the customer wishes to use loyalty cash (cell L14 of the model inputs), loyalty cash applied is equal to the total loyalty cash available if the transaction total exceeds the loyalty cash available. If the loyalty cash available exceeds the transaction total, then the loyalty cash applied is equal to the transaction total. Loyalty cash applied is ziyo if the customer does not wish to use the loyalty cash. For this task, reference the transaction total in cell F24 rather than the value you calculated in cell F21. Calculate the Balance Alter Loyalty Cash in cell F26. The balance after loyalty case is the difference between the mention total (Cell F24) and any loyalty cash applied (cell F25)
