Homework answers / question archive / BA250 Analytical Techniques in Bus Review AssignmentsData Files needed for the Review Assignments: NP_eX_2_2

BA250 Analytical Techniques in Bus Review AssignmentsData Files needed for the Review Assignments: NP_eX_2_2

MS Excel

Share With

---

BA250 Analytical Techniques in Bus

Review AssignmentsData Files needed for the Review Assignments:

NP_eX_2_2.xlsx, support_eX_2_texture.jpg

Stefan has another workbook for you to work on. This report will also deal with home furnishing sales in the Northwest region; but rather than breaking down the sales figures by store, Stefan wants to ana-lyze the data by product to determine whether specific product categories have seen an increase or decrease in sales from the previous year. Stefan already compiled the data in a workbook but needs you to develop a finished report that will be distributed among the sales team. Complete the following:

1. Open the NP_eX_2_2.xlsx workbook located in the Excel2 > Review folder included with your Data Files, and then save the workbook as NP_eX_2_Products in the location specified by your instructor.

2. In the Documentation sheet, enter your name in cell B4 and the date in cell B5. Format the date in cell B5 with the Long Date cell format and left-align the date in the cell.

3. Set the background of the Documentation sheet to the support_eX_2_texture.jpg image file located in the Excel2 > Review folder included with your Data Files, and then change the fill color of the range B4:B6 to white.

4. In the Product Sales worksheet, enter formulas to make the following calculations:

a. In cell B6, use the SUM function to calculate the total of the Decorative sales for the current year using the values in the range C31:N35.

b. In cell B7, use the SUM function to calculate the sum of the Living sales using the values in the range C36:N40.

c. In cell B8, calculate the sum of the Utility sales using the values in the range C41:N44.

d. In cell B9, calculate the total sales from all three categories in the current year by adding the values in the range B6:B8.

e. In cell C9, add the total sales from all three categories in the previous year.

f. In the range D6:D9, calculate the net change in sales by subtracting the previous year values in column C from the current year values in column B for all three product categories and overall.

g. In the range E6:E9, calculate the percent change in sales for the three categories and total sales by dividing the net change values in column D by the previous year’s sales values in column C.

5. Apply the following formats to the data:

a. Format the range B6:D9 with Accounting style and no decimal places.

b. Format the range E6:E9 in Percent style showing two decimal places.

c. Format cell A4 with the Title cell style.

d. Format the range A5:E5 with the Heading 3 cell style.

e. Format the range A9:E9 with the Total cell style.

6. Complete the following calculations for the Sales by Product Type data using the monthly sales data for each product:

a. In cell B13, use the SUM function to calculate the total sales for Home Decorations from the range C31:N31, and then copy that formula into the range B14:B26.

b. In cell B27, use the SUM function to calculate the sum of the values in the range B13:B26. Copy your formula and paste it into cell C27. Verify that the values in the range B27:C27 equal the values in the range B9:C9.

c. In cell D13, calculate the difference between the value in cell B13 and cell C13. Copy the formula to the range D14:D27.

d. In cell E13, divide the value in cell D13 by the value in cell C13. Copy the formula to the range E14:E27.

7. Use Format Painter to do the following:

a. Copy the formatting from cell A4 to cell A11.

b. Copy the formatting from the range A5:E5 to the range A12:E12.

c. Copy the formatting from the range A6:E6 to the range A13:E26.

d. Copy the formatting from the range A9:E9 to the range A27:E27.

8. Add conditional formatting that displays negative values in the nonadjacent range D6:E9,D13:E27 in a red font on a light red background to highlight sales categories and product types that show a decrease in sales from the previous year.

9. In the range C45:N45, use the SUM function to calculate the sum of monthly sales across all product types.  

10. In the range O31:O45, use the SUM function to calculate the sum of sales for each product  category and across all product types. Verify that the value in cell O45 equals the value in cells B9 and B27.11. Format the monthly sales data as follows:

a. In the range C31:N44, format the sales data in Comma style with no decimal places.

b. In the nonadjacent range C45:N45,O31:O45, format the calculated values in Accounting format with no decimal places.

c. Format cell A29 with the Title cell style.

d. Format the range A30:O30 with the 60%-Accent 6 cell style.

e. Format the range A31:A45 with the 60%-Accent 3 cell style.

f. Format the range B31:B45 with the 40%-Accent 3 cell style.

g. Format B45:O45 using the Total cell style.

12. Add a left border to the monthly averages in the range O31:O45.

13. Merge and center the cells in the range A31:A35. In the merged cell, rotate the text up, and then middle-align the text in the cell. Bold the text and increase the font size to 14 points. Repeat for the ranges A36:A40 and A41:A44. Reduce the width of column A to 16 characters.

14. Add thick outside borders around the ranges A31:O35, A36:O40, and A41:O44.

15. Do the following to highlight the top-selling product types in the report:

a. In the range O31:O44, use conditional formatting to highlight the top 3 selling product types in dark green text on a green fill.

b. In cell O29, enter the text top 3 sellers and use the Text That Contains conditional format to change the format of this cell to a dark green text on a green fill to match the conditional formatting you added to the top three product types.

16. Change the theme of the workbook to Wisp.

17. Change the tab color of the Documentation sheet tab to the Brown, Accent 3 theme color. Change the tab color of the Product Sales sheet tab to the Olive Green, Accent 5 theme color.

18. Make the following format changes to the printed version of the Product Sales worksheet:

a. Set the print area to the nonadjacent range A1:E28,A29:O45.

b. Insert page breaks below rows 9 and 28 and to the right of columns H and N. Remove any automatic page breaks that were added to the sheet.

c. Set the print titles to repeat rows 1 and 2 and columns A and B on every page.

d. Set the size of the left margin to 1 inch.

e. Scale the printout to 60% of its normal size.

f. Add a different first page for headers and footers. On the first page header, display your name in the left section, display the file name in center, and display the date in the right section.

g. For the first page footer and subsequent page footers, enter the code Page &[Page] of &[Pages] in the center section.

19. Preview the workbook. The printout should have only five pages. If you are instructed to print, print the entire workbook.

20. Save the workbook, and then close it.