PROJECT STEPS - Hazen Business Education



PROJECT STEPSOpen the Statistics worksheet. Modify the column widths and row heights as described below:Use AutoFit Column Width to change the width of column A to make all the contents visible. Change the row height of Row 1 to 48.00 points.Change the widths of columns B through M to 10.00 characters.In cell A1, apply the formatting options described below:Change the font to Arial Black font and a font size of 24 pt.Change the font color to Turquoise, Accent 3, Darker 50% (7th column, 6th row in the Theme Colors palette).Change the fill color of the cell to Turquoise, Accent 3, Lighter 80% (7th column, 2nd row in the Theme Colors palette).Merge and center the contents of cells A2:M2 and then apply the Heading 2 cell style to the merged cells. Format the merged cells with the Long Date Number format.Enter the contents in bold shown in Table 1 below into the range C4:F5.Table 1: Values for Range C4:F54018531986967? 2014 Cengage Learning.400000? 2014 Cengage Learning.CellValueC4Bottles/CansD4PlasticsE4Mixed PaperF4GlassC525D580E550F515Format the cells in the range C5:F5 with the Currency Number format with 2 decimal places. Apply the following formatting options, as described below:Bold and Center the content of cell B4. Use Format Painter to apply the formatting of cell B4 to the range C4:F4. Merge the contents of cells B4:B5. Apply the All Borders border style to the range B4:F5.Move the content of cell E6 to cell E7 and then apply the formatting options described below:Merge and center the cells E7:G7Apply the fill color Blue, Accent 2, Lighter 60% (6th column, 3rd row in the Theme Colors palette) to the merged cells.Use the January label in cell B8 to fill the range C8:M8 with the months of the year.In cell B13, use the SUM function to total the values in the range B9:B12. Use the Fill handle to copy the formula from B13 into the range C13:M13.Perform a Goal Seek Analysis to determine the number of bins of Plastic containers/bags (cell B10) needed in January to change the value in cell B13 to 100. (Hint: Cell B10 will be the changing cell.) Keep the results of the Goal Seek Analysis as the new value for cell B10. In cell B14, use the keyboard to enter a formula that multiplies the value in cell B9 (the number of returnable bottles) by the value in cell C5 (the value of each returnable bottle) Use an absolute cell reference to cell C5 and a relative reference to cell B9. Copy the formula from cell B14 to the range C14:M14.Calculate the revenue for the remaining three recyclables (Plastics, Mixed Paper, and Glass) as described below. Remember to use absolute references to the cells in the range D5:F5 in the formulas. In cell B15, enter a formula that multiples the value in cell B10 by the value in cell D5. Copy the formula from B15 to the range C15:M15.In cell B16, enter a formula that multiples the value in cell B11 by the value in cell E5. Copy the formula from B16 to the range C16:M16.In cell B17, enter a formula that multiples the value in cell B12 by the value in cell F5. Copy that formula from B17 to the range C17:M17.Format the values in the range B9:M13 with the Comma Style Number format, decrease the number of decimal places to 1. Format the range B14:M18 with the Accounting Number format and 2 decimal places. Apply a new conditional formatting rule to the range B18:M18. The rule should format cells with a values greater than $4,000 with Standard Light Green fill color (5th column of the Standard Colors palette) and Standard Dark Blue font color (9th column of the Standard Colors palette). In cell B21, use the AVERAGE function to calculate the average monthly revenue generated for the range B14:B17. Copy the formula from cell B21 to the range C21:M21.In cell B22, use the MAX function to calculate which value in the range B14:B17 is the largest. Copy the formula from cell B22 to the range C22:M22.In cell B23, use the MIN function to calculate which value in the range B14:B17 is the smallest. Copy the formula from cell B23 to the range C23:M23.In cell B24, use the IF function to check whether the value of cell B21 is greater than 1000.If this condition is true, the cell value should be set to Good. (Tip: For the value if true, use “Good”)If this condition is false, the cell value should be set to Poor. (Tip: For the value if false, use “Poor”)Copy the formula created in cell B24 to the range C24:M24 In cell B26, create Column Sparklines from the data in the range H12:J12, and then change the style of the Sparklines to Sparkline Style Accent 3, (no dark or light) (3rd column, 3rd row of the Sparkline Style gallery).Check the Spelling in the workbook to identify and correct any spelling errors. (Hint: You should find and correct at least 2 spelling errors.)Format the worksheet for printing as described below:Change the orientation to Landscape.Change the worksheet margins to Narrow.Insert a header in the Center section with the text Island Recycling 2016 Statistics.Set the print area as the range A4:M26.Scale the worksheet so that it prints on one page.Create a 3-D pie chart from the non-adjacent ranges B8:M8 and B18:M18. Move the chart you just created to its own chart sheet. Use Revenue by Month Chart as the name of the new chart sheet. Format the 3-D Pie chart with the following options:Change the chart title to Revenue by Month.Select the Chart Style 6.Add data labels using the Data Callout positioning option.Remove the legend from the chart.Switch back to Statistics. Use the Recommended Chart tool to create a Clustered Column chart based on the range A8:D12. Move the chart to its own chart sheet. Use the name Quarter 1 Chart as the name of the new chart sheet. Format the Clustered Column chart with the following options: Change the chart title to Bins collected in Quarter 1.Add Number of Bins as the Primary Vertical Axis TitleAdd Months in Quarter 1 as the Primary Horizontal Axis TitleGo to the Volunteer Educators worksheet and make the following formatting changes:Rotate the labels in the range B3:B14 by 45 degrees.Use the entries in the range A3:A4 to fill the range A5:A14 with the number series from 3 to 12. Copy the range C3:C5, then paste it into the ranges C6:C8 and C12:C14. Use the paste option that pastes the values, but not the cell formatting. Remove the fill color from the range C3:C5.In the Recycling Companies worksheet, select cell B3 and use the Freeze pane option to freeze the rows and columns to the left and above cell B3, respectively. Zoom out on the Recycling Companies worksheet to 60%. Select Statistics, Recycling Companies, and Volunteer Educators worksheets and then change the color of the sheet tabs to Turquoise, Accent 3 (7th column, 1st row in the Theme Colors palette). Your workbook should look like the Final Figures below and on the following page. Save your changes, close the workbook, and exit Excel. Save the the Project 8 folder in the Excel Projects file on the Public Drive. Save as Period_LastFirstName_Project8. 16148054975225Microsoft product screenshot reprinted with permission from Microsoft Incorporated. Copyright ? 2014 Cengage Learning. All Rights Reserved.00Microsoft product screenshot reprinted with permission from Microsoft Incorporated. Copyright ? 2014 Cengage Learning. All Rights Reserved.35560740294Final Figure 1: Revenue by Month Chart Worksheet Final Figure 2: Quarter 1 Chart Worksheet19735804716145Copyright ? 2014 Cengage Learning. All Rights Reserved.020000Copyright ? 2014 Cengage Learning. All Rights Reserved.Final Figure 3: Statistics Worksheet19348453280085Copyright ? 2014 Cengage Learning. All Rights Reserved.020000Copyright ? 2014 Cengage Learning. All Rights Reserved.18148304740910Copyright ? 2014 Cengage Learning. All Rights Reserved.020000Copyright ? 2014 Cengage Learning. All Rights Reserved.0625656Final Figure 4: Volunteer Educators Worksheet17564103342640Copyright ? 2014 Cengage Learning. All Rights Reserved.020000Copyright ? 2014 Cengage Learning. All Rights Reserved.0629153Final Figure 5: Recycling Companies Worksheet ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download