Download the spreadsheet "Store Sales 2012.xlsx" below. Use the data in this spreadsheet for the remainder of the assignment.

Store-Sales-2012.xlsx

1.Using your newly created “Expanded Order Type” column, calculate the total “Sales” for all orders of “Medium” type (rounded to 2 decimal places). Hint: The SUMIF() function is useful here.

2. The company gives a 1% discount on any Extra Large or larger orders. In the “Discount” column, create a formula that returns 0.01 if the “Expanded Order Type” is Extra Large, XX Large, or XXX Large, and returns 0 otherwise. You can accomplish this by using a (select all that apply):

a.VLOOKUP formula

b. HLOOKUP formula

c. Nested IF formula

d. IF formula

 3. Create the formula from the previous question and copy the formula down to all the rows. How many orders will have a discount applied?

Hint: The COUNTIF() function is useful here.

4. The “Sales with Discount” can be calculated by applying the discount to the original “Sales” entry:Sales with Discount = Sales – (Sales * Discount) or Sales with Discount = Sales * (1 – Discount) Create a formula in the “Sales with Discount” column and copy it down to all the rows. What is the impact of the discount on total sales in 2012? In other words, what is the difference between the sum of the “Sales” and the sum of the “Sales with Discount” (rounded to 2 decimal places)?

5. Currently, customers are responsible for paying the shipping costs. The sales team suggests that customers really dislike paying shipping costs, and that offering “free shipping” instead of the 1% discount would likely increase sales. Create a formula for the “Sales with Free Shipping” column that subtracts the “Shipping Cost” from the “Sales” only if the “Expanded Order Type” is Extra Large, XX Large, or XXX Large. Copy the formula down to all the rows. What would total 2012 sales have been if the company had offered free shipping instead of the 1% discount (rounded to 2 decimal places)?

6.How much money would the company have saved in 2012 if it had offered free shipping instead of the 1% discount on Extra Large, XX Large, or XXX Large orders (rounded to 2 decimal places)?

7. What would 2012 total “Sales” have been if the company had offered free shipping on any order shipped by Delivery Truck, and no additional discounts (rounded to 2 decimal places)?

• 1503124723-Excel2.xlsx

AccountingQueen

(3)

Status NEW Posted 19 Aug 2017 06:08 AM My Price 10.00

Hel-----------lo -----------Sir-----------/Ma-----------dam----------- T-----------han-----------k y-----------ou -----------for----------- us-----------ing----------- ou-----------r w-----------ebs-----------ite----------- an-----------d a-----------cqu-----------isi-----------tio-----------n o-----------f m-----------y p-----------ost-----------ed -----------sol-----------uti-----------on.----------- Pl-----------eas-----------e p-----------ing----------- me----------- on----------- ch-----------at -----------I a-----------m o-----------nli-----------ne -----------or -----------inb-----------ox -----------me -----------a m-----------ess-----------age----------- I -----------wil-----------l

Not Rated(0)

• Buy Answer