Information Tools For Business/EXCEL

YO16XLCH04GRADERPCHW_-_Spa_Sales_14_Instructions.docx

Office 2016 – myitlab:grader – Instructions Excel Project

YO16_XL_CH04_GRADER_PC_HW - Spa Sales 1.4

Project Description: The Turquoise Oasis Spa managers, Irene Kai and Meda Rodate, are pleased with your work and would like to see you continue to improve the spa spreadsheets. They want to use charts to learn more about the spa. Meda has given you a spreadsheet with some data and would like you to develop some charts. Visualizing the data with charts will provide knowledge about the spa for decision-making purposes.

Instructions: For the purpose of grading the project you are required to perform the following tasks: Step Instructions Points Possible 1 Start Excel. Open the file named e02ch04_grader_pc_SpaSales.xlsx. Save the file with the name e02ch04_grader_pc_SpaSales_LastFirst, replacing LastFirst with your last and first name. 0.000 2 On the TableUse worksheet, add data labels to the pie chart to show only the percentage of each section. Apply Chart Style 7 to the chart. 3.000 3 On the ProductData worksheet, create a Clustered Column chart using the data in A2:B12. Modify the Chart Area property so that the chart will move but not resize with cells. Move the chart to a new Chart Sheet, named TotalNumberOfMassages. 5.000 4 Modify the color of the TotalNumberOfMassages worksheet tab to Blue, Accent 1. 3.000 5 On the Revenue worksheet, create a Pie chart using the data in A1:E2. Move and resize the chart so that the top left corner is in cell G4 the bottom right corner is in cell M17. 5.000 6 On the Revenue worksheet, create a Line chart using the data in A4:E34. Move and resize the chart so that the top left corner in cell G20 and the bottom right corner is in cell M34. Edit the chart title to read Revenue by Massage Type for June. 6.000 7 On the RedesignProject worksheet, create a Stacked Bar chart using the data in A3:A8 and C3:D8. Move the chart so the top left corner is in cell A10. Edit the chart title to read Project Redesign Status. 5.000 8 On the Survey worksheet, create a Scatter chart using the data in A2:B53. Move the chart so that the top left corner is in cell E2. Modify the minimum bound of the vertical axis to 65. Edit the chart title to read Relationship Between Age and Temperature. 5.000 9 On the ProductData worksheet, create a Stacked Area chart using the data in A16:I19. Move and resize the chart so that the top left corner is in cell D2 and the bottom right corner is in cell I14. Edit the chart title to read Massage Types Over 8 Week Period. 5.000 10 On the SpaSales worksheet, create a Clustered Column - Line Combo chart using the data in A2:C13. Create a secondary axis for Profit. Move the chart so that the top left corner is in cell E1. Edit the chart title to read, Quantity Sold and Profit. 6.000 11 On the SpaSales worksheet, in cell A14, type December, in cell B14, type 165, and in C14, type 1701. 3.000 12 On the SpaSales worksheet, adjust the data used to create the chart to include the new row of data. Change the chart type of the Profit data series to an Area chart. Apply Style 6 Chart Style and Layout 9. 3.000 13 Edit the value in cell B3 to be 125 and then edit the value in cell C3 to be 650. 2.000 14 On the TotalNumberOfMassages chart worksheet, apply the Chart Layout 4 to the chart. Insert the e02ch04TurquoiseOasis.jpg picture. Adjust the shape height of the picture to 0.9. Apply the Subtle Effect – Orange, Accent 2 to the columns in the chart. 5.000 15 Insert a Rounded Rectangle shape onto the TotalNumberOfMassages chart worksheet, just under the logo, with the text Number of Massage Services by Type. Adjust the height to 0.8 and the width to 2.2. Apply the Subtle Effect – Orange, Accent 2 Shape Style to the shape. Adjust the font size of the text inside the shape to 16. Note, depending upon the version of Office being used, the shape name may be Rectangle Rounded Corners. 4.000 16 On the RedesignProject worksheet tab, edit the chart title to be a cell reference to A1. 2.000 17 On the SpaSales worksheet, delete the horizontal axis title box. Edit the vertical axis title to read, Quantity. Add a secondary vertical axis title with the text Profit. 3.000 18 On the Revenue worksheet, edit the line chart so that the legend appears to the right. On the Pie chart, add Data Callout style data labels and remove the legend. 3.000 19 On the RedesignProject worksheet, modify the bounds of the horizontal axis to have a minimum of 20. 3.000 20 On the Revenue worksheet, add Primary Major Vertical gridlines to the Line chart. 3.000 21 On the Survey worksheet, add a Linear Trendline to the Scatter chart. Modify the trendline to be a solid line rather than a dashed line. 3.000 22 On the Revenue worksheet, change the colors of the Pie chart to be Color 4. Change the Shape Fill of the chart area to Gold, Accent 4, Lighter 60%. Note, depending upon the version of Office used, the color name may be Colorful Palette 4. 3.000 23 On the Revenue worksheet, increase the font size of the Pie chart title to 16 and apply the Bold style. Apply the WordArt Style, Pattern Fill – White, Text 2, Dark Upward Diagonal, Shadow to the title. Note, depending upon the version of Office used, the WordArt Style name may be Fill: White; Outline: Orange, Accent color 2; Hard Shadow: Orange, Accent color 2. 4.000 24 On the Revenue worksheet, modify the Pie chart so that the Hot Stone slice of the pie is exploded away from the center, to show emphasis. 0.000 25 On the Revenue worksheet, change the Pie chart to be a 3-D Pie chart. Rotate the chart by changing the Y Rotation to 50 and the Perspective to 30. Apply a Cool Slant Top Bevel 3-D Format effect to the chart area. 4.000 26 On the HairProducts worksheet, create Line Sparklines into A3:A7, using the data in cells C3:N7. Apply the Sparkline Style Accent 2, Darker 50% to the Sparklines. Note, depending upon the version of Office being used, the Sparkline Style name may be Brown, Sparkline Style Accent 2, Darker 50%. 5.000 27 On the HairProducts worksheet, apply Conditional Formatting, in the form of Gradient Fill, Green Data Bars to the range C10:C14. 3.000 28 On the HairProducts worksheet, modify the Clustered Bar chart by changing the chart type to a Line chart. Switch the row/column so that the time data is on the x-axis. Modify the angle of the Horizontal axis to have a custom angle -45 degrees. Edit the chart title to read Hair Products Sold Over the Last 12 Months. Filter the chart so that only Shampoo and Conditioner items are visible. 4.000 29 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 0.000 Total Points 100.000

Updated: 01/03/2017 1 Current_Instruction.docx