Example 1



Example 1.8-3– Chapter Review – Discount PricingSales!57150128905Sales!338137538735Data!As the owner of a small convenience store you have started to keep track of store sales including recording of the discounts which apply to each sale. Your pricing is such that sales are discounted based on the sales amount, as given in table data. The only exception to this is where a senior discount is applied (for those eligible customers over the age of 65). Seniors will receive greater of either the sales discount or the senior discount. Fill in the formulas as required to complete your sales recording system.Write an Excel formula in cell SalesE2, which can be copied down the column, to determine the value of the senior discount. Only seniors are eligible for this discount. Senior status is indicated in column C on sheet sales!. The senior discount percentage in given on sheet data, non seniors receive no discount. (Note the ‘-‘ display is the currency format for the value $0). =IF(C2,data!B$6*sales!B2,0)Write an Excel formula in cell Sales!F2, which can be copied down the column, to determine the value of the sales discount. As indicated on sheet data, sales of less than $100 receive no discount, sales of at least $100 but less than $200 receive a 10% discount of the sale amount, and sales of $200 or more receive a 15% discount of the sale amount. =IF(B2<100,data!B$9,IF(B2<200,data!B$10*B2,data!B$11*B2)) Write an Excel formula in cell Sales!G2, which can be copied down the column, to determine the discount amount used. The discounted value used will be the greater of the two different discounts (senior discount or sales discount). (**challenge – try to do this without an IF).. =MAX(E2:F2)Write an Excel formula in cell Sales!H2, which can be copied down the column, to determine total sale value after the discount. =B2-G2Write an Excel formula in cell Sales!I2, which can be copied down the column, to determine the sales tax on this item. Sales tax rates are listed by category on sheet data. Categories for each sale are listed in column D. =IF(D2="f",data!C$2,data!C$3*sales!H2)Write an Excel formula in cell J2, which can be copied down the column, to determine the value of the sale including tax rounded to the nearest cent. =ROUND(H2+I2,2) ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download