Gaining Proficiency Instructions
Grader - Instructions Excel 2019 Project
Excel_3F_Streets
Project Description:
In the following project, you will edit a worksheet that will be used to prepare a five-year forecast of the costs associated with street maintenance.
Steps to Perform:
Step
Instructions
Points Possible
1
Open the Excel workbook Student_Excel_3F_Streets.xlsx downloaded with this project.
0
2
Change the Theme Colors to Blue Green. On the Housing Revenue sheet, in the range B4:F4, fill the year range with the values 2016 through 2020.
8
3
In cell C6, construct a formula to calculate the percent of increase in annual street maintenance costs from 2016 to 2017. Format the result with the Percent Style and then fill the formula through cell F6 to calculate the percent of increase in each year.
8
4
In the range B12:F12, use the fill handle to enter the years 2020 through 2024. Use Format Painter to apply the format from cell B4 to the range B12:F12.
10
5
Copy the value in cell F5 to cell B13. In cell B10, type 6% which is the projected increase estimated by the City financial analysts. To the range A10:B10, apply Bold and Italic.
4
6
In cell C13, construct a formula to calculate the annual projected street maintenance costs for the year 2021 after the projected increase of 6% is applied. Use absolute cell references as necessary. Fill the formula through cell F13, and then use Format Painter to copy the formatting from cell B13 to the range C13:F13.
8
7
Use Format Painter to copy the format from cell A8 to cell A15. Copy the range B12:F12, and then Paste the selection to B17:F17.
6
8
Copy the range B13:F13 and then paste the Values & Number Formatting to the range B18:F18. Complete the Projected Estimates section of the worksheet by changing the Projected Increase in B10 to 4% and then copying and pasting the Values & Number Formatting to the appropriate range in the worksheet. Save your workbook.
12
9
Select rows 8:24, and then insert the same number of blank rows as you selected. Clear Formatting from the inserted rows. By using the data in A5:F5, insert a Line with Markers chart in the worksheet. Move the chart so that its upper left corner is positioned in cell A9 and visually centered under the data above.
12
10
Display the Select Data Source dialog box, and then edit the Horizontal (Category) Axis Labels using the range that contains the years in B4:F4. Format the Bounds of the Vertical (Value) Axis so that the Minimum is XXXXXXXXXXand the Major unit is at 200000.
10
11
Format the Chart Area with a Border by applying a Solid line. In the fifth column, click the last color. Change the Width of the border to 2 pt.
6
12
Click cell A1 to deselect the chart. Center the worksheet Horizontally, and then insert a Custom Footer in the left section with the file name.
4
13
Display the Permit Analysis worksheet. Select the range A4:B7 and then insert a Funnel chart that depicts the number of bids in each stage of the bid process. Change the chart title to 2020 Contract Bids and then drag the chart so that the upper left corner aligns with the upper left corner of cell D1. Change the chart Height to 2 and the Width to 4. Deselect the chart and then change the Orientation to Landscape. Center the worksheet Horizontally, and then insert a Custom Footer in the left section with the file name.
10
14
Display the workbook Properties. As the Tags, type street maintenance costs and as the Subject, type your course name and section number. Be sure your name displays as the Author.
2
15
Save and close the file, and then submit for grading.
0
Total Points
100
Created On: 07/11/2019 1 GO19_XL_CH03_GRADER_3F_HW - Streets 1.0
Expenses
City of Pacifica Bay
Recreation Department Summary of Annual Expenses
Quarter 1 Quarter 2 Quarter 3 Quarter 4 Annual Total Percent of Total
Salaries and Benefits $ 721,216 $ 810,451 $ 785,960 $ 788,657
Machinery and Equipment 325,723 289,430 254,638 336,354
Materials and Supplies 445,420 398,675 348,642 254,233
Professional Services 563,243 689,243 542,683 976,245
Miscellaneous 227,126 224,876 228,238 223,827
Totals by Quarte
Expenses 5-Year Forecast
Forecasted Increase 3.5%
Year 2016 2017 2018 2019 2020
Projected Expenses
Enrollment
Recreation Department
Workshop Annual Enrollment Analysis
Enrolled Residents 482
Submitted Payment 395
Attended 356
County Partnership
Recreation Department
City/County Partnership Analysis
County Number of
Partnered Cities
Kern 10
San Benito 3
Humboldt 18
San Mateo 12
Inyo 1
San Bernardino 20
Gaining Proficiency Instructions
Grader - Instructions Excel 2019 Project
Excel_3F_Streets
Project Description:
In the following project, you will edit a worksheet that will be used to prepare a five-year forecast of the costs associated with street maintenance.
Steps to Perform:
Step
Instructions
Points Possible
1
Open the Excel workbook Student_Excel_3F_Streets.xlsx downloaded with this project.
0
2
Change the Theme Colors to Blue Green. On the Housing Revenue sheet, in the range B4:F4, fill the year range with the values 2016 through 2020.
8
3
In cell C6, construct a formula to calculate the percent of increase in annual street maintenance costs from 2016 to 2017. Format the result with the Percent Style and then fill the formula through cell F6 to calculate the percent of increase in each year.
8
4
In the range B12:F12, use the fill handle to enter the years 2020 through 2024. Use Format Painter to apply the format from cell B4 to the range B12:F12.
10
5
Copy the value in cell F5 to cell B13. In cell B10, type 6% which is the projected increase estimated by the City financial analysts. To the range A10:B10, apply Bold and Italic.
4
6
In cell C13, construct a formula to calculate the annual projected street maintenance costs for the year 2021 after the projected increase of 6% is applied. Use absolute cell references as necessary. Fill the formula through cell F13, and then use Format Painter to copy the formatting from cell B13 to the range C13:F13.
8
7
Use Format Painter to copy the format from cell A8 to cell A15. Copy the range B12:F12, and then Paste the selection to B17:F17.
6
8
Copy the range B13:F13 and then paste the Values & Number Formatting to the range B18:F18. Complete the Projected Estimates section of the worksheet by changing the Projected Increase in B10 to 4% and then copying and pasting the Values & Number Formatting to the appropriate range in the worksheet. Save your workbook.
12
9
Select rows 8:24, and then insert the same number of blank rows as you selected. Clear Formatting from the inserted rows. By using the data in A5:F5, insert a Line with Markers chart in the worksheet. Move the chart so that its upper left corner is positioned in cell A9 and visually centered under the data above.
12
10
Display the Select Data Source dialog box, and then edit the Horizontal (Category) Axis Labels using the range that contains the years in B4:F4. Format the Bounds of the Vertical (Value) Axis so that the Minimum is XXXXXXXXXXand the Major unit is at 200000.
10
11
Format the Chart Area with a Border by applying a Solid line. In the fifth column, click the last color. Change the Width of the border to 2 pt.
6
12
Click cell A1 to deselect the chart. Center the worksheet Horizontally, and then insert a Custom Footer in the left section with the file name.
4
13
Display the Permit Analysis worksheet. Select the range A4:B7 and then insert a Funnel chart that depicts the number of bids in each stage of the bid process. Change the chart title to 2020 Contract Bids and then drag the chart so that the upper left corner aligns with the upper left corner of cell D1. Change the chart Height to 2 and the Width to 4. Deselect the chart and then change the Orientation to Landscape. Center the worksheet Horizontally, and then insert a Custom Footer in the left section with the file name.
10
14
Display the workbook Properties. As the Tags, type street maintenance costs and as the Subject, type your course name and section number. Be sure your name displays as the Author.
2
15
Save and close the file, and then submit for grading.
0
Total Points
100
Created On: 07/11/2019 1 GO19_XL_CH03_GRADER_3F_HW - Streets 1.0
Housing Revenue
Pacifica Bay
Street Maintenance Costs 2016 to 2020
Annual Street Costs $ 2,194,374 $ 2,438,156 $ 2,497,489 $ 2,614,252 $ 2,716,008
Percent Increase
Projected Street Maintenance Costs
Projected Increase
Yea
Projected Costs
Projected Estimates 2020 to 2024
Yea
6% Increase
4% Increase
Permit Analysis
2020 Contract Analysis
Contract Bid Process--All Street Contracts
Contract Stage Total
Bids Submitted 112
Bids Eligible 75
Contracts Approved 22
Contracts Accepted 15