Darker 25 Apply Follow Instructions

Darker 25 Apply Follow Instructions

Darker 25 Apply Follow Instructions

Excel Unit Exam

Project Description:

Unit 4 Excel Exam

Instructions:

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

Step

Instructions

Points Possible

1

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

0

2

On Sheet1, change the Theme to Droplet

4

3

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 Total Sales and in cell H3, type Trend

5

4

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 theHeading 4 cell style to the range A4:A7.

9

5

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 Facials 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.

10

6

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.

4

7

In cell G4, using absolute cell references, construct a formula to calculate the % of Total Sales for Facials. Do this 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.

4

8

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).

2

9

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.

2

10

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.

3

11

Center the worksheet Horizontally and scale the
Width to fit to 1 page.

2

12

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 Stylewith zero decimal places to the result.

5

13

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

4

14

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.

6

15

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.

5

16

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.

4

17

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.

5

18

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 Stockcolumn. Record the result in cell B10, and apply Comma Style with zero decimal places.

3

19

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

2

20

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%.

8

21

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.

5

22

Adjust column widths. Scale the sheet to fit on 1 page, and center the sheet Horizontally.
**You will only have the headings, no text in the middle.

2

23

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.

4

24

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

2

25

Save your workbook and submit using the grader project link.

0

Total Points

100