MODIFY A SERVICE REPORT AND CREATE A Chart

computer science 7

Shelly Cashman Excel 2013 | Chapters 1-3: SAM Capstone Project 1a

Shelly Cashman Excel 2013

Chapters 1-3: SAM Capstone Project 1a

Island Recycling

MODIFY A SERVICE REPORT AND CREATE A Chart

Project Goal

M Project Name

Project Goal

PROJECT DESCRIPTION

The small island community of Fir Island off the Washington coast, is justifiably proud of its recycling facility. Built entirely through the efforts of a band of dedicated volunteers, Island Recycling is committed to providing a convenient and welcoming location on Fir Island for the drop-off and distribution of recycled materials. Island Recycling is run by a non-profit society (Fir Island Recycling Society or F.I.R.S.) that manages the recycling facility, schedules volunteers, liaises with the local municipality, and sells recycled materials to companies located on the mainland. All revenues produced from the sale of recycled materials are invested back into Island Recycling. You are on the Board of the Fir Island Recycling Society and have volunteered to put together a spreadsheet to analyze the past year of activities at F.I.R.S. You have already inputted data about the recycling activities into Excel. Now you need to format the worksheet, use formulas and functions to analyze the data, and create charts to display selected data.

GETTING STARTED

· Download the following file from the SAM website:

· SC_Excel2013_CS_C1-3_P1a_FirstLastName_1.xlsx

· Open the file you just downloaded and save it with the name:

· SC_Excel2013_CS_C1-3_P1a_FirstLastName_2.xlsx

· Hint: If you do not see the .xlsx file extension in the Save file dialog box, do not type it. Excel will add the file extension for you automatically.

· With the file SC_Excel2013_CS_C1-3_P1a_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet. If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

PROJECT STEPS

Open the Statistics worksheet. Modify the column widths and row heights as described below:

a. Use AutoFit Column Width to change the width of column A to make all the contents visible.

b. Change the row height of Row 1 to 48.00 points.

c. Change the widths of columns B through M to 10.00 characters.

In cell A1, apply the formatting options described below:

d. Change the font to Arial Black font and a font size of 24 pt.

e. Change the font color to Turquoise, Accent 3, Darker 50% (7th column, 6th row in the Theme Colors palette).

f. 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 DateNumber format.

Enter the contents in bold shown in Table 1 below into the range C4:F5.

Table 1: Values for Range C4:F5

© 2014 Cengage Learning.

Cell Value
C4 Bottles/Cans
D4 Plastics
E4 Mixed Paper
F4 Glass
C5 25
D5 80
E5 50
F5 15

Format the cells in the range C5:F5 with the Currency Number format with 2 decimal places.

Apply the following formatting options, as described below:

a. Bold and Center the content of cell B4.

b. Use Format Painter to apply the formatting of cell B4 to the range C4:F4.

c. Merge the contents of cells B4:B5.

d. 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:

a. Merge and center the cells E7:G7

b. Apply 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.

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

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

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

a. If this condition is true, the cell value should be set to Good. (Tip: For the value if true, use “Good”)

b. 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:

a. Change the orientation to Landscape.

b. Change the worksheet margins to Narrow.

c. Insert a header in the Center section with the text Island Recycling 2016 Statistics.

d. Set the print area as the range A4:M26.

e. 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:

a. Change the chart title to Revenue by Month.

b. Select the Chart Style 6.

c. Add data labels using the Data Callout positioning option.

d. 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:

a. Change the chart title to Bins collected in Quarter 1.

b. Add Number of Bins as the Primary Vertical Axis Title

c. Add Months in Quarter 1 as the Primary Horizontal Axis Title

Go to the Volunteer Educators worksheet and make the following formatting changes:

a. Rotate the labels in the range B3:B14 by 45 degrees.

b. Use the entries in the range A3:A4 to fill the range A5:A14 with the number series from 3 to 12.

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

d. 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. Follow the directions on the SAM website to submit your completed project.

Final Figure 1: Revenue by Month Chart Worksheet

Microsoft product screenshot reprinted with permission from Microsoft Incorporated. Copyright © 2014 Cengage Learning. All Rights Reserved.

Final Figure 2: Quarter 1 Chart Worksheet

Copyright © 2014 Cengage Learning. All Rights Reserved.

Final Figure 3: Statistics Worksheet

Copyright © 2014 Cengage Learning. All Rights Reserved.

 Final Figure 4: Volunteer Educators Worksheet

Copyright © 2014 Cengage Learning. All Rights Reserved.

 Final Figure 5: Recycling Companies Worksheet

Copyright © 2014 Cengage Learning. All Rights Reserved.

2

"Get 15% discount on your first 3 orders with us"
Use the following coupon
FIRST15

Order Now