The Jupiter Candy Company wants to calculate (1) discounted order costs for its Halloween promotional discounts and (2) summary statistics on the number of boxes ordered. 1. The discount percentage is based on (1) customer status either Ruby or Pearl and (2) the number of boxes ordered. The discount schedule is: Customer Status Boxes Ordered Discount Ruby 1999 or less 6% Ruby 2000 or more 8% Pearl Pearl 2999 or less 5% 3000 or more 7% This information is also given in the Discount Schedule table on the worksheet. Use logical functions in cells H4:H340 to calculate each customer's order cost after the discount has been applied. For example, if a customer is Ruby status, ordered 4,000 boxes, they would thus receive a discount of 8%. If the original order cost was $4,760 the discounted order cost would be 4, 760 (1-0.08) = 4, 760 0.92 = $4,379.20. 2. In the Boxes Ordered: Summary Statistics, use functions to retrieve the requested information. For example, cell K5 should give the total number of Bitterfinger boxes ordered by Pearl customers. Cell P10 should give the total number of Three Corn-husketeers orders (not boxes) by Ruby customers. Customer Customer ID Status Candy Brand C100 Ruby Bitterfinger Halloween Promotion Orders Boxes Discounted Ordered Price Order Cost Order Cost 4224 $1.19 $5,026.56 Boxes Ordered: Summary Statistics Pearl Customers Ruby Customers Total Boxes Candy Brand Ordered Average Boxes Ordered Number of Total Boxes Orders Average Boxes Number of Ordered Ordered Orders C101 Pearl Dorks C102 Pearl Spittles 1337 $1.64 $2,192.68 1397 $1.33 $1,858.01 Bitterfinger Dorks C103 Ruby Dorks 2033 $1.64 $3,334.12 Spittles C104 Ruby Dorks 462 $1.64 $757.68 Angry Rancher C105 Pearl Angry Rancher 273 $1.62 $442.26 Drees' Almond Butter Cups C106 Pearl Drees' Almond Butter Cups 4066 $1.98 $8,050.68 Three Corn-husketeers C107 Pearl Drees' Almond Butter Cups 2818 $1.98 $5,579.64 C108 Ruby Bitterfinger 4943 $1.19 $5.882.17 Discount Schedule C109 Pearl Drees' Almond Butter Cups 1775 $1.98 $3,514.50 Boxes Ordered C110 Ruby Three Corn-husketeers 3956 $1.34 $5,301.04 Customer Status C111 Ruby Three Corn-husketeers 51 $1.34 $68.34 Ruby Low 0 High Discount 1999 0.06 C112 Ruby Three Corn-husketeers 4566 $1.34 $6,118.44 Ruby 2000 Infinity 0.08 C113 Pearl Dorks 1994 $1.64 $3,270.16 Pearl 0 2999 0.05 C114 Ruby Three Corn-husketeers 2954 $1.34 $3,958.36 Pearl 3000 Infinity 0.07 C115 Pearl Dorks 1656 $1.64 $2,715.84 C116 Ruby Drees' Almond Butter Cups 3594 $1.98 $7,116.12 C117 Pearl Bitterfinger 3565 $1.19 $4,242.35 C118 Ruby Three Corn-husketeers 538 $1.34 $720.92 C119 Pearl Spittles 215 $1.33 $285.95 C120 Pearl Bitterfinger 4548 $1.19 $5,412.12 C121 Ruby Drees' Almond Butter Cups 3563 $1.98 $7,054.74 C122 Ruby Dorks 4101 $1.64 $6,725.64 C123 Ruby Dorks 1795 $1.64 $2,943.80 C124 Pearl Dorks 2653 $1.64 $4,350.92 C125 Ruby Bitterfinger 4056 $1.19 $4,826.64 C126 Ruby Bitterfinger 4735 $1.19 $5,634.65 C127 Pearl Angry Rancher 4839 $1.62 $7,839.18 C128 Ruby Drees' Almond Butter Cups 3588 $1.98 $7,104.24