PROJECT STEPS

1. Felicia Narvaez works for Youve Got Style, a clothing company that sends boxed sets of curated clothing to its subscribers. She keeps track of the companys sales and inventory, as well as relevant product information, in an Excel worksheet that is updated and distributed weekly.

Switch to the Orders worksheet. In cell A2, insert the NOW function to record the current date and time.

2. The data section called Transaction Growth shows how many clothing sets were sold each month. Felicia entered a formula into the range O6:O10 to calculate the highest amount of each set sold over the course of the year. She notices that all of the values in the range are 2,146, even though more clothing sets than this were sold in some months.

Edit the formula in cell O6 so that it uses relative references instead of absolute references for the range B6:M6, and then copy the formula from cell O6 to fill the range O7:O10 without formatting.

3. In cell P6, create a formula using the MIN function to find the lowest number of the Bizzy Caz clothing set ordered (the range B6:M6). Copy the formula from cell P6 to fill the range P7:P10 without formatting.

4. The worksheet will also show the average number of orders fulfilled for each column in the section above.

In cell B12, create a nested formula using the AVERAGE and ROUND functions to first find the average number of clothing sets sold in January (the range B6:B9), and then round the answer to 0 digits.

Copy the formula from cell B12 to the range C12:N12.

5. The data section called Inventory Tracker shows how many of each clothing set component has been ordered by the company each month. Use AutoFill with the values in the range B15:D15 to extend the series of months to the range E15:M15.

6. In cell O16, create a formula using the MAX function to find the peak inventory number for the Shirt/Blouse component (the range B16:M16). Copy the formula from cell O16 to fill the range O17:O23 without formatting.

7. Switch to the Product Lookup worksheet. This worksheet shows information on the clothing sets and the components that come in each set.

The last person to work on the workbook mistakenly pasted four into cell B10, which created an error in cell B17. Change the value in cell B10 from four to 4 to eliminate the error message in cell B17.

8. The data section called Clothing Set Information is incomplete.

In cell B15, create a formula using the SUM function to find the total item counts in the High Society clothing set by totaling the item counts from the Dress/Skirt (cell B7) and Accessories (cell B11) components.

9. In cell C14, create a formula using the COUNTA function to count the number of components in the Bizzy Caz clothing set (the range B20:F20). Copy the formula from cell C14 to fill the range C15:C17 without formatting to count the number of components for the rest of the clothing sets.

10. The data section called Product Information Lookup uses a mix of user input cells, output cells, and calculated cells to display various information for a single clothing set. Format the section as follows:

Apply the Input cell style to cell I5.

Apply the Output cell style to the range I6:I8.

Apply the Calculation cell style to cell I9.

11. In cell I6, create a formula using the VLOOKUP function to display the number of items in the selected clothing set. Use the clothing set listed in cell I5 as the lookup value, the range A14:D17 as the table array, column number 2 as the column index number, and FALSE as the range lookup value. Use absolute references for cell I5 and the range A14:D17.

12. In cell I9, create a formula without using a function that first adds the selected boxed sets net weight (cell I8) to the packing weight (cell F5), and then divides the value by 16 to show the shipping weight in pounds.

13. The delivery service used by the company charges a higher rate for packages weighing over 40 lbs.

In cell I10, create a formula that uses the IF function to determine if a packaged clothing set is overweight by checking if the shipping weight (cell I9) is greater than 40. If this condition is true, the cell should display Yes, and if it is false, the cell should display No. (Hint: Use Yes for the value if true, and No for the value if false.)

14. The company can make more profit by keeping shipping costs to a minimum. Felicia has been put in charge of the packaging redesign project, and she needs to figure out what weight of packaging can keep even the heaviest clothing set from being overweight.

Perform a Goal Seek Analysis to find a packing weight that will bring the shipping weight of the Retro Fit clothing set down to 39.75 lbs. Use cell I9 as the cell to set, 39.75 as the target value, and cell F5 as the changing cell. Keep the values generated by the Goal Seek Analysis as the value for cells F5, I9, and I10.

Your workbook should look like the Final Figures below. (The value in cell F5 generated by the Goal Seek Analysis has been intentionally blurred out in Final Figure 1.) Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.

Final Figure 1: Product Lookup Worksheet

Final Figure 2: Orders Worksheet

New Perspectives Excel 2016 | Module 3: SAM Project 1b

Youve Got Style

PERFORMING CALCULATIONS WITH FORMULAS AND FUNCTIONs

GETTING STARTED

Open the file NP_EX16_3b_FirstLastName_1.xlsx, available for download from the SAM website.

Save the file as NP_EX16_3b_FirstLastName_2.xlsx by changing the 1 to a 2.

If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.

With the file NP_EX16_3b_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.

If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

PROJECT STEPS

15. Felicia Narvaez works for Youve Got Style, a clothing company that sends boxed sets of curated clothing to its subscribers. She keeps track of the companys sales and inventory, as well as relevant product information, in an Excel worksheet that is updated and distributed weekly.

Switch to the Orders worksheet. In cell A2, insert the NOW function to record the current date and time.

16. The data section called Transaction Growth shows how many clothing sets were sold each month. Felicia entered a formula into the range O6:O10 to calculate the highest amount of each set sold over the course of the year. She notices that all of the values in the range are 2,146, even though more clothing sets than this were sold in some months.

Edit the formula in cell O6 so that it uses relative references instead of absolute references for the range B6:M6, and then copy the formula from cell O6 to fill the range O7:O10 without formatting.

17. In cell P6, create a formula using the MIN function to find the lowest number of the Bizzy Caz clothing set ordered (the range B6:M6). Copy the formula from cell P6 to fill the range P7:P10 without formatting.

18. The worksheet will also show the average number of orders fulfilled for each column in the section above.

In cell B12, create a nested formula using the AVERAGE and ROUND functions to first find the average number of clothing sets sold in January (the range B6:B9), and then round the answer to 0 digits.

Copy the formula from cell B12 to the range C12:N12.

19. The data section called Inventory Tracker shows how many of each clothing set component has been ordered by the company each month. Use AutoFill with the values in the range B15:D15 to extend the series of months to the range E15:M15.

20. In cell O16, create a formula using the MAX function to find the peak inventory number for the Shirt/Blouse component (the range B16:M16). Copy the formula from cell O16 to fill the range O17:O23 without formatting.

21. Switch to the Product Lookup worksheet. This worksheet shows information on the clothing sets and the components that come in each set.

The last person to work on the workbook mistakenly pasted four into cell B10, which created an error in cell B17. Change the value in cell B10 from four to 4 to eliminate the error message in cell B17.

22. The data section called Clothing Set Information is incomplete.

In cell B15, create a formula using the SUM function to find the total item counts in the High Society clothing set by totaling the item counts from the Dress/Skirt (cell B7) and Accessories (cell B11) components.

23. In cell C14, create a formula using the COUNTA function to count the number of components in the Bizzy Caz clothing set (the range B20:F20). Copy the formula from cell C14 to fill the range C15:C17 without formatting to count the number of components for the rest of the clothing sets.

24. The data section called Product Information Lookup uses a mix of user input cells, output cells, and calculated cells to display various information for a single clothing set. Format the section as follows:

Apply the Input cell style to cell I5.

Apply the Output cell style to the range I6:I8.

Apply the Calculation cell style to cell I9.

25. In cell I6, create a formula using the VLOOKUP function to display the number of items in the selected clothing set. Use the clothing set listed in cell I5 as the lookup value, the range A14:D17 as the table array, column number 2 as the column index number, and FALSE as the range lookup value. Use absolute references for cell I5 and the range A14:D17.

26. In cell I9, create a formula without using a function that first adds the selected boxed sets net weight (cell I8) to the packing weight (cell F5), and then divides the value by 16 to show the shipping weight in pounds.

27. The delivery service used by the company charges a higher rate for packages weighing over 40 lbs.

In cell I10, create a formula that uses the IF function to determine if a packaged clothing set is overweight by checking if the shipping weight (cell I9) is greater than 40. If this condition is true, the cell should display Yes, and if it is false, the cell should display No. (Hint: Use Yes for the value if true, and No for the value if false.)

28. The company can make more profit by keeping shipping costs to a minimum. Felicia has been put in charge of the packaging redesign project, and she needs to figure out what weight of packaging can keep even the heaviest clothing set from being overweight.

Perform a Goal Seek Analysis to find a packing weight that will bring the shipping weight of the Retro Fit clothing set down to 39.75 lbs. Use cell I9 as the cell to set, 39.75 as the target value, and cell F5 as the changing cell. Keep the values generated by the Goal Seek Analysis as the value for cells F5, I9, and I10.

Your workbook should look like the Final Figures below. (The value in cell F5 generated by the Goal Seek Analysis has been intentionally blurred out in Final Figure 1.) Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.