Select Page

Below I have attached Excel sheet and intructions. Thanks.

Don't use plagiarized sources. Get Your Custom Essay on
GO16 MyITLab City Of Pacifica Bay Summary Of Annual Expenses
Just from \$10/Page

Unformatted Attachment Preview

Office 2016 – myitlab:grader – Instructions
Excel Project
Project Description:
In the following project, you will edit a worksheet that will be used to summarize the operations costs for the
Public Works Department.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step
Points
Possible
Instructions
1
0.000
2
In the Expenses worksheet, calculate row totals for each Expense item in the range F5:F9.
Calculate column totals for each quarter and for the Annual Total in the range B10:F10.
8.000
3
In cell G5, construct a formula to calculate the Percent of Total by dividing the Annual Total
for Salaries and Benefits by the Annual Total for Totals by Quarter. Use absolute cell
references as necessary, format the result in Percent Style, and then Center. Fill the formula
down through cell G9.
12.000
4
Use a 3-D Pie chart to chart the Annual Total for each item. Move the chart to a new sheet
and then name the sheet Annual Expenses Chart.
8.000
5
For the Chart Title, type Summary of Annual Expenses and format the chart title using
WordArt Style Fill – Blue, Accent 1, Shadow. Change the Chart Title font size to 28.
6.000
6
Remove the Legend from the chart and then add Data Labels formatted so that only the
Category Name and Percentage display positioned in the Center. Change the Data Labels font
size to 12, and apply Bold and Italic.
8.000
7
8
Format the Data Series using a 3-D Format effect. Change the Top bevel and Bottom bevel to
Circle. Set the Top bevel Width and Height to 50 pt and then set the Bottom bevel Width and
Height to 256 pt. Change the Material to the Standard Effect Metal.
Note, the bevel name may be Round, depending on the version of Office used.
Display the Series Options, and then set the Angle of first slice to 125 so that the Salaries and
Benefits slice is in the front of the pie. Select the Salaries and Benefits slice, and then explode
the slice 10%. Change the Fill Color of the Salaries and Benefits slice to a Solid fill using
Green, Accent 6, Lighter 40%.
4.000
4.000
9
Accent 4 (fourth column, first row). Format the Border of the Chart Area by adding a Solid line
border using Gold, Accent 4 and a 5 pt Width.
6.000
10
Display the Page Setup dialog box, and then for this chart sheet, insert a custom footer in the
left section with the file name.
4.000
Updated: 10/09/2017
1
Current_Instruction.docx
Office 2016 – myitlab:grader – Instructions
Step
11
12
Excel Project
Points
Possible
Instructions
Display the Expenses worksheet, and then by using the Quarter names and the Totals by
Quarter, insert a Line with Markers chart in the worksheet. Move the chart so that its upper
left corner is positioned slightly inside the upper left corner of cell A12. Drag the center-right
sizing handle so that the chart extends to slightly inside the right border of column G. As the
Chart Title type City of Pacifica Bay Annual Expense Summary.
Format the Bounds of the Vertical (Value) Axis so that the Minimum is 2100000 and the
Major unit is at 50000. Format the Fill of the Chart Area with a Gradient fill by applying the
Preset, Light Gradient – Accent 3 (third column, first row). Format the Plot Area with a Solid fill
using White, Background 1.
10.000
10.000
13
Copy the Annual Total in cell F10 and then use Paste Special to paste Values & Number
Formatting in cell B35. In cell C35, construct a formula to calculate the Projected Expenses
after the forecasted increase in cell B31 is applied. Fill the formula through cell F35, and then
use Format Painter to copy the formatting from cell B35 to the range C35:F35.
10.000
14
Change the Orientation of this worksheet to Landscape, and then use the Scale to Fit options
to fit the Height to 1 page. From the Page Setup dialog box, center the worksheet
Horizontally, and insert a custom footer in the left section with the file name.
10.000
15
Ensure that the worksheets are correctly named and placed in the following order in the
workbook: Annual Expenses Chart, Expenses. Save the workbook and exit Excel. Submit the
file as directed.
0.000
Total Points
Updated: 10/09/2017
2
100.000
Current_Instruction.docx
City of Pacifica Bay
Summary of Annual Expenses
Quarter 1
Salaries and Benefits
Machinery and Equipment
Materials and Supplies
Professional Services
Miscellaneous
Totals by Quarter
\$
721,216 \$
325,723
445,420
563,243
227,126
Quarter 2
810,451 \$
289,430
398,675
689,243
224,876
Quarter 3
785,960 \$
254,638
348,642
542,683
228,238
Quarter 4
Annual Total
788,657
336,354
254,233
976,245
223,827
Expenses 5-Year Forecast
Forecasted Increase
Year
Projected Expenses
3.5%
2016
2017
2018
2019
2020
Percent of
Total
Office 2016 – myitlab:grader – Instructions
Excel Project
Project Description:
In the following project, you will edit a worksheet that will be used to summarize the departmental administration
expenses for the City of Orange Blossom Beach.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step
Points
Possible
Instructions
1
0.000
2
In the Expenses worksheet, use the SUM function to calculate row totals for each Expense
item in the range F5:F9. Format F6:F9 with Comma Style, zero decimal places. Calculate
column totals (using SUM) for each quarter and for the Annual Total in the range B10:F10.
4.000
3
In cell G5, construct a formula to calculate the Percent of Total by dividing the Annual Total
for City Manager by the Annual Total for Totals by Quarter. Use absolute cell references as
necessary, format the result in Percent Style, and then Center. Fill the formula down through
cell G9.
4.000
4
Create a 3-D Pie chart to chart the Annual Total for each item using the ranges A5:A9 and
F5:F9. Move the chart to a new sheet and then name the sheet Administration Costs
Chart.
8.000
5
For the chart title, type Summary of Administration Costs and format the chart title using
WordArt Style Fill – Dark Red, Accent 1, Shadow—in the first row, the second style. Change
the chart title font size to 28.
10.000
6
Remove the Legend from the chart and then add Data Labels formatted so that only the
Category Name and Percentage display positioned in the Center. Change the data labels Font
Size to 12, and apply Bold and Italic.
6.000
7
Format the Data Series using a 3-D Format effect. Change the Top bevel and Bottom bevel to
Circle. Set the Top bevel Width and Height to 50 pt and then set the Bottom bevel Width and
Height to 256 pt. Change the Material to the fourth Standard Effect—Metal.
5.000
8
Display the Series Options, and then set the Angle of first slice to 140 so that the City
Manager slice is in the front of the pie. Select the City Manager slice, and then explode the
slice 10%. Change the Fill Color of the City Manager slice to a Solid fill using Dark Red, Accent
1, Lighter 40%—in the fifth column, the fourth color.
5.000
9
Accent 4. Format the Border of the Chart Area by adding a Solid line border using Brown,
Accent 4 and a 5 pt Width.
6.000
Display the Page Setup dialog box, and then for this chart sheet, insert a custom footer in the
left section with the File Name element.
4.000
10
Updated: 02/24/2017
1
Current_Instruction.docx
Office 2016 – myitlab:grader – Instructions
Step
Excel Project
Points
Possible
Instructions
11
Display the Expenses worksheet, and then by using the Quarter names and the Totals by
Quarter, insert a Line with Markers chart in the worksheet. Move the chart so that its upper
left corner is positioned slightly inside the upper left corner of cell B12. Type Annual
Administration Expenses by Quarter as the Chart Title.
8.000
12
Format the Bounds of the Vertical (Value) Axis so that the Minimum is 1085000 and the
Major unit is at 10000. Format the Fill of the Chart Area with a Gradient fill by applying the
Area with a Solid fill using White, Background 1—in the first column, the first color.
6.000
13
Copy the Annual Total in cell F10 and then use Paste Special to paste Values & Number
Formatting in cell B35. In cell C35, construct a formula to calculate the Projected Expenses
after the forecasted increase of 3.5% in cell B31 is applied. Fill the formula through cell F35,
and then use Format Painter to copy the formatting from cell B35 to the range C35:F35.
10.000
14
Change the Orientation of this worksheet to Landscape, and then use the Scale to Fit options
to fit the Height to 1 page. From the Page Setup dialog box, center the worksheet
Horizontally, and insert a custom footer in the left section with the File Name element.
10.000
15
Display the Projected Decrease worksheet. In cell C5, calculate the Percent of Total for the
first department, apply Percent Style (zero decimal places), and then copy the formula down
for the remaining expenses.
4.000
16
Copy cell B5, and then use Paste Special to paste the Values & Number Formatting to cell B13.
Copy and Paste cell C5 to cell C13. With cell C13 selected, use Goal Seek to determine the
goal amount of City Manager expenses in cell B13 if the value in C13 is set to 15%.
6.000
17
From the Page Setup dialog box, center this worksheet Horizontally, and insert a custom
footer in the left section with the File Name element.
4.000
18
Ensure that the worksheets are correctly named and placed in the following order in the
workbook: Administration Costs Chart, Expenses, Projected Decrease. Save and close the
workbook. Exit Excel. Submit the file as directed.
0.000
Total Points
Updated: 02/24/2017
2
100.000
Current_Instruction.docx
City of Orange Blossom Beach
Summary of Annual Administration Expenses by Department
Quarter 1
City Manager
Community Services
Public Works
Finance
Fire and Safety
Totals by Quarter
\$
188,632 \$
154,025
405,421
121,500
227,126
Quarter 2
185,492 \$
158,964
408,675
109,362
224,876
Quarter 3
181,365 \$
162,475
403,442
115,468
228,238
Quarter 4
Annual Total
184,733
160,333
414,423
116,877
223,827
Expenses 5-Year Forecast
Forecasted Increase
Year
Projected Expenses
3.5%
2016
2017
2018
2019
2020
Percent of
Total
City of Orange Blossom Beach
Projected Decrease in City Manager Expenses
Quarter 1 Total
City Manager
Community Services
Public Works
Finance
Fire and Safety
Totals by Quarter
\$
\$
Percent of
Total

Goal: Decrease City Manager Expenses to 15%
Goal Amount
Office 2016 – myitlab:grader – Instructions
Excel Project
Project Description:
In this project, you will use a PMT function to calculate a value and then use it in a two-variable data table. You
will create named ranges and use them in formulas.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step
1
2
Points
Possible
Instructions
0.000
Display the Condo Purchase worksheet. In cell B5, insert the PMT function using the data from
the range B2:B5—be sure to divide the interest rate by 12 multiply the years by 12 and
display the payment as a positive number. The result, \$6,598.44, is larger than the maximum
desired payment of \$6,250.
10.000
Use Goal Seek to change the amount of the loan so that the payment is \$6,250. Then, in cell
A7, type Option #1 Reduce the Loan
3
4
5
To cell A1, apply the Title cell style, and then Copy the format from cell A1 to cell A7. Copy the
range A2:B5, and then Paste the Values & Number Formatting to cell A8. In cell B2, type
615000 to restore the original loan amount.
Use Goal Seek to change the period of the loan (of the original data) so that the payment is
\$6,250. Then, in cell A13, type Option #2 Increase Years
Format the cell the same as cell A7. Copy the range A2:B5, and then Paste the Values &
Number Formatting to cell A14. Display the value in cell B15 with two decimal places, and
then in cell B3, type 10 to restore the original value. Save your workbook.
Display the Payment Table worksheet, in the range A2:B4, enter the following row titles and
data.
7.000
4.000
5.000
Amount of Loan
\$615,000
Period (months)
120
Interest Rate (per year) 5.25%
6
In cell C8, type 60—the number of months in a 5-year loan. In D8, type 120—the number of
months in a 10-year loan. Fill the series through cell H8. Apply Bold and Center to the range
C8:H8.
10.000
7
Beginning in cell B9, enter varying interest rates in decrements of 0.5% beginning with
7.5% and ending with 4.0% If necessary, format all the interest rates with two decimal
places, and then apply Bold and Center to the range B9:B16.
10.000
8
In cell B8, enter a PMT function using the information in cells B2:B4. Be sure that you convert
the interest rate to a monthly rate and that the result displays as a positive number.
5.000
9
Create a Data table in the range B8:H16 using the information in cells B2:B4 in which the Row
input cell is the Period and the Column input cell is the Interest rate. Copy the format from cell
B8 to the results in the data table. Format cell D16 with the Note cell style as the payment
option that is close to but less than \$6,250 per month.
10.000
Updated: 09/25/2017
1
Current_Instruction.docx
Office 2016 – myitlab:grader – Instructions
Excel Project
Step
Instructions
Points
Possible
10
Display the Advertising Costs by Quarter worksheet, and then apply the Currency [0] cell style
to the range B6:E6 and to the range B18:E18. Apply the Comma [0] cell style to the range
B7:E17, and then apply the Total cell style to the range B18:E18.
9.000
11
Name the following ranges: B6:E10 Newspaper_Costs B11:E14 Digital_Costs B15:E16
Magazine_Costs B17:E17 Billboard_Costs
7.000
Insert a new row 15. In cell A15, type Business Podcasts In cell B15, type 12500 In cell
C15, type 11525 In cell D15, type 14455 In cell E15, type 13009
12
13
14
15
16
Display the Name Manager, click Digital_Costs, and then change cell E14 to E15. Select the
Billboard_Costs and then Edit the name to Outdoor_Costs Save your workbook.
Display the Annual Advertising Costs sheet. In cell B5, type =sum(N and sum the values
using the appropriate range name in the displayed list of functions. Repeat for the other
named ranges. From the Formulas tab, in the Function Library group, use AutoSum to sum all
the costs. Apply Currency [0], Comma [0], and Total cell styles to the appropriate cells.
In the sheet tab row, right-click any sheet tab, and then click Select All Sheets. Display the
Page Setup dialog box, click the Margins tab, and then under Center on page, click
Horizontally. Click the Header/Footer tab, click Custom Footer, and then in the Left section,
insert the File Name. Click OK two times. Ungroup the sheets, display the Payment Table
sheet. Set the Orientation of this sheet to Landscape and set the Scale to 85%.
Display Backstage view, click Show All Properties. On the list of Properties, in the Tags box,
type payment table, advertising costs In the Subject box, type your course name and
section #. Under Related People, be sure that your name displays—edit if necessary. On the
left, click Print. Under Settings, click the Print Active Sheets arrow, and then click Print Entire
Workbook. At the bottom of the window, click Next Page to scroll through the worksheets. On
the left, click Save.
Ensure that the worksheets are correctly named and placed in the following order in the
Costs. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as
directed.
Total Points
Updated: 09/25/2017
2
7.000
10.000
5.000
1.000
0.000
100.000
Current_Instruction.docx