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.

Transcribed Image Text:

Calculations Model Inputs Transaction Information Customer Information Loyatly Status Loyalty Cash Available $14.54 Silver # Items Purchased Total Price of Items $175.23 Sales Tax Rate Sale Worksheet 3. Number of Items Purchased Total Price of Items 3 Use Loyalty Cash? Payment Method $175.23 6.75% Yes Cash Loyalty Discount % Loyalty Discount $ Loyalty Program Discount Rates Silver Gold Sale Total Discount Sale Total Discount Discounted Subtotal Sales Tax 2% 2% Transaction Total 50 3% 25 3% 100 4% 50 4% Payment Worksheet 200 5% 100 5% Transaction Total $175.23 200 10% Loyalty Cash Applilied Balance After Loyalty Cash Cash Discount Total Due from Customer Credit Worksheet: Beginning Loyalty Cash Loyalty Cash Used Loyalty Cash Eamed Ending Loyalty Cash $0.00 Calculations Model Inputs Transaction Information Customer Information Loyatly Status Loyalty Cash Available $14.54 Silver # Items Purchased Total Price of Items $175.23 Sales Tax Rate Sale Worksheet 3. Number of Items Purchased Total Price of Items 3 Use Loyalty Cash? Payment Method $175.23 6.75% Yes Cash Loyalty Discount % Loyalty Discount $ Loyalty Program Discount Rates Silver Gold Sale Total Discount Sale Total Discount Discounted Subtotal Sales Tax 2% 2% Transaction Total 50 3% 25 3% 100 4% 50 4% Payment Worksheet 200 5% 100 5% Transaction Total $175.23 200 10% Loyalty Cash Applilied Balance After Loyalty Cash Cash Discount Total Due from Customer Credit Worksheet: Beginning Loyalty Cash Loyalty Cash Used Loyalty Cash Eamed Ending Loyalty Cash $0.00

Answer rating: 100% (QA)

The formula for loyalty discount is LOOKUP with nested IF func... View the full answer