Reference no: EM131322208

Project

Instructions:

For the purpose of grading the project you are required to perform the following tasks:

Instructions

1 Open the start file Day Spa that you recently downloaded then Save the workbook as Lastname_Firstname_U4_Day_Spa

2 On Sheet1, in cell A2, type 2017 Sales Comparison by Service and then change the Theme to Droplet

3 In cell B3, type Quarter 1 and then use the fill handle to enter Quarter 2, Quarter 3, and Quarter 4 in the range C3:E3. In cell F3, type Total. In cell G3, type % of TotalSales and in cell H3, type Trend

4 In the range A4:A7, type the following row titles:
Facials
Body Wraps and Scrubs
Massages
Total Sales

5 Widen column A to 160 pixels. Widen columns B:H to 105 pixels. Merge & Center the title across the range A1:H1, apply the Title cell style, and then change the font size to 26. Merge & Center the subtitle across the range A2:H2, and then apply the Heading 1 cell style. Center the column titles in row 3, and then apply the Heading 3 cell style. Apply the Heading 4 cell style to the range A4:A7.

6 In the range B4:E6, enter the sales values for each quarter as shown in the table:

                                          Qtr1            Qtr2            Qtr 3      Qtr4
Facials                               9516.40       6439.50      5155.82  6684.75
Body Wraps and Scrubs     6966.30        6245.13      5906.22  4114.63
Massages                          13471.32      11794.76    9794.61  14098.34

7 Use Quick Analysis to Sum the Quarter 1 sales, and then copy the formula across for the remaining quarters. By using Quick Analysis, Sum the sales for Facials, and then copy the formula down through cell F7. Apply Accounting Number Format to the first row of sales figures and to the total row, and Comma Style to the remaining sales figures. Format the totals in row 7 with the Total cell style.

8 Insert a new row 6 with the row title Manicures and Pedicures and the following sales figures for each quarter:

7691.16 and 9774.38 and 9648.75 and 2622.73

Fill the formula in cell F5 down to cell F6 to sum the new row, and then remove the bold formatting from the range F4:F7.

9 In cell G4, using absolute cell references, construct a formula to calculate the % of Total Sales for Facials by dividing the total for Facials sales by the total sales for all quarters. Fill the formula down for the remaining services. To the range G4:G7, apply Percent Style with two decimal places, and then Center the percentages.

10 In the range H4:H7, insert Line sparklines that compare the quarterly data. Do not include the totals in the sparkline range. Show the Markers, and apply the first style in the third row-Sparkline Style Accent 1 (no dark or light).

11 Use Recommended Charts to insert a Clustered Column chart to compare the quarterly sales of each service, with the services-not the quarters-on the Category axis. Do not include the totals in the data range.

12 Position the upper right corner of the chart in the upper right corner of cell F10. Apply Style 14 to the chart. Change the chart title to 2017 Sales Comparison by Service and then change the title font size to 14.

13 Center the worksheet Horizontally and scale the

Width to fit to 1 page.

14 Display the Sheet2 worksheet. Scale the Width to fit on 1 page, centered Horizontally. In cell B9, enter a function to sum the Quantity in Stock data, and then apply Comma Style with zero decimal places to the result.

15 In cell B11, enter a function to calculate the average price, and then apply the Accounting Number Format. Autofit column B.

16 In cell B4, enter a COUNTIF function to determine how many different types of Facials products are in stock by using the same technique, in B5:B7, enter a COUNTIF function to determine the number of types in each of the other categories.

17 In cell G14, enter an IF function to determine the items that must be ordered. If the Quantity in Stock is less than 65 the Value_if_true is Order Otherwise, the Value_if_false is OK Fill the formula down through all the rows.

18 Apply Conditional Formatting to the Stock Level column so that cells that contain the text Order are formatted with Bold Italic with a font color of Green, Accent 2, Darker 25%. Apply Gradient Fill Green Data Bars to the Quantity in Stock column.

19 Move the range A4:B7 to the range beginning in cell D4. In cell C6, type Categories and then merge cells C4:C7, and rotate the text 30 degrees. Change the font size to 18 pt, apply Bold, and Align Right.

20 Format the range A13:G37 as a table with headers, and apply Table Style Light 9. Insert a Total row,filter by Category for Body Scrubs, and then Sum the Quantity in Stock column. Record the result in cell B10, and apply Comma Style with zero decimal places.

21 Clear the filter. Sort the table on the Item # column from Smallest to Largest, and then remove the Total row.

22 Using the range D4:E7, insert a 3-D pie chart on a new sheet named Inventory Chart as the chart title type Inventory by Category Change the font size to 28 pt and change the font color to Blue-Gray, Text 2-in the fourth column, the first color. Remove the Legend. Format the Data Labels so that they are centered and display only the Category Name and Percentage. With the data labels selected, apply Bold and change the font size to 14. Explode the Facials section by 15%.

23 Display Sheet3. Merge & Center the title across the range A1:F1 and apply the Title cell style. To the range A2:F2, apply the Heading 2 style and then Center the text. Apply the Heading 4 style to the text in the range A3:A6.

24 Adjust column widths and wrap text as necessary. Scale the sheet to fit on 1 page, and center the sheet Horizontally.

25 Rename Sheet1 as Sales rename Sheet2 as Inventory and rename Sheet3 as Backups Move the Inventory Chart sheet to the right of the Inventory worksheet.

26 On each sheet, insert a footer to display the file name in the left section and the sheet name in the right section.

27 Save your workbook and submit using the grader project link.

Attachment:- madison_u4_day_spa.xlsx