Project 3F Streets

1. Start Excel and display a new Blank workbook.

Save the file in your Excel Chapter 3 folder as Lastname_Firstname_3F_Streets.

2. Change the Theme Colors to Paper. In cell A1, type the title City of Pacifica Bay and then Merge & Center the title across columns A:F. Apply the Title cell style.

3. In cell A2 type the subtitle Street Maintenance Costs 2012 to 2016 and then Merge & Center the subtitle across columns A:F. Apply the Heading 1 cell style.

4. In the range B4:F4, fill the year range with the values 2012 through 2016. In cell A5 type Annual Street Costs and in cell A6 type Percent Increase.

5. Change the width of column A to 145 pixels, and then change the width of columns B:F to 82 pixels. In the range B5:F5 type 2194374 and 2438156 and 2497489 and 2614252 and 2716008.

6. Select B5:F5, display the Cell Styles gallery, and then apply the Currency [0] cell style to the values in B5:F5. Apply the Heading 1 cell style to the years, and apply the Heading 4 cell style to the range A5:A6.

7. In cell C6, construct a formula to calculate the percent of increase in annual street maintenance costs from 2012 to 2013.

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. In cell A8, type Projected Street Maintenance Costs and then use Format Painter to copy the formatting from cell A2 to cell A8.

In cell A10, type Projected Increase and then in cell A12, type Year 9 In cell A13, type Projected Costs and then in the range B12:F12, use the fill handle to enter the years 2016 through 2020. Apply Bold to the years.

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.

10. In cell C13, construct a formula to calculate the annual projected street maintenance costs for the year 2017 after the projected increase of 6% is applied.

Fill the formula through cell F13, and then use Format Painter to copy the formatting from cell B13 to the range C13:F13.

11. In cell A15, type Projected Estimates 2016 to 2020 and then use Format Painter to copy the format from cell A8 to cell A15. In cells A17:A19, type the following row titles: Year 6% Increase 4% Increase.

12. Copy the range B12:F12, and then Paste the selection to B17:F17. 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.

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

14. Format the Bounds of the Vertical (Value) Axis so that the Minimum is 2000000 and the Major unit is 200000.

15. Format the Fill of the Chart Area with a Texture fill by applying the Parchment texture—in the third row, the fifth texture.

Format the Chart Area with a Border by applying a Solid line using Olive Green, Accent 1, Darker 50%—in the fifth column, the last color. Change the Width of the border to 2 pt. Format the Plot Area with a Solid fill using White, Background 1—in the first column, the first color.

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

17. Show all the properties, and then as the Tags, type street maintenance costs as the Subject, type your course name and section number. Be sure your name displays as the Author.

Save your workbook. If directed by your instructor to do so, submit your paper printout, your electronic image of your document that looks like a printed document, or your original Excel file.

If required by your instructor, print or create an electronic version of your worksheet with formulas displayed. In the upper right corner of the Excel window, click Close.