Spreadsheet Functions to Organize Data >> Introduction to Data Analysis Using Excel
1. Download the spreadsheet “Store Sales 2012.xlsx” below. Use the data in this spreadsheet for the remainder of the assignment.
Store-Sales-2012.xlsxThe formula in cell H2 contains a referencing error that must be fixed before copying the formula down the column. If the formula were to be copied down the column without making any corrections, which of the following would be true?
- The formula would always return “Small”
- The formula would always return “Medium”
- The formula would always return “Large”
- The formula would only return “Large” or “Small”
2. Assuming you want the formula in H2 to always reference the cell directly to its left, correct the formula. Once the formula is fixed, copy the formula down the column. What is the “Order Type” of Order ID 11428, placed on 1/21/2012?
- Small
- Medium
- Large
- Extra Large
3. The boss decides that using only 3 levels—small, medium, and large—does not give the shipping department enough information. She wants to use the 10 levels shown in Lookup Table 1 (A3:B12). Write a formula in the “Expanded Order Type” column to assign the correct type to each order by “Order Quantity.” Copy the formula down to all the rows. Based on the layout of Lookup Table 1, should you use a VLOOKUP function or an HLOOKUP function?
- VLOOKUP
- HLOOKUP
4. 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.
275870.24
5. 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 ### Large, and returns 0 otherwise. You can accomplish this by using a (select all that apply):
- VLOOKUP formula
- HLOOKUP formula
- Nested IF formula
- IF formula
6. 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.
664
7. 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)?
16924.28
8. 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, Double Large, or Triple 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)?
371204.65
9. How much money would the company have saved in 2012 if it had offered free shipping instead of the 1% discount on Extra Large, Double Large, or Triple Large orders (rounded to 2 decimal places)?
9008.97
10. 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)?
3706856.39