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

Subject:MS ExcelPrice:19.87 Bought3

Share With

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

Elizabeth Allen

1. 

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.

2. In cell A1, apply the formatting options described below:

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

b. Change the font color to Turquoise, Accent 3, Darker 50% (7th column, 6th

row in the Theme Colors palette).

c. Change the fill color of the cell to Turquoise, Accent 3, Lighter 80% (7th

column, 2nd row in the Theme Colors palette).

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

4. Enter the contents in bold shown in Table 1 in the Assignment file into the range

C4:F5.

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

decimal places.

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

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

8. Use the January label in cell B8 to fill the range C8:M8 with the months of the

year.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

20. Check the Spelling in the workbook to identify and correct any spelling errors.

(Hint: You should find and correct at least 2 spelling errors.)

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

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

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

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

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

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

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

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