all directions are listed below you must use the excel documents provided
Independent Project 3-4
For this project, you create a column chart to illustrate April–September revenue for Classic Gardens and Landscapes. You also build a pie chart sheet to graph the proportion that each category contributes to total revenue.
[Student Learning Outcomes 3.1, 3.2, 3.3, 3.4, 3.5, 3.6]
File Needed:
ClassicGardens-03.xlsx
(Available from the Start File link.)
Completed Project File Name:
[your name]-ClassicGardens-03.xlsx
Skills Covered in This Project
· Create a chart object.
· Size and position a chart object.
· Edit and format chart elements.
· Edit the source data for a chart.
· Build a pie chart sheet.
· Use texture as fill.
· Add and format data labels in a chart.
This image appears when a project instruction has changed to accommodate an update to Microsoft 365 Apps. If the instruction does not match your version of Office, try using the alternate instruction instead.
1. Open the
ClassicGardens-03.xlsx start file. If the workbook opens in Protected View, click the Enable Editing button so you can modify it. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it.
2. Create a Clustered Column chart object for cells A4:G9.
3. Move the chart object so that its top-left corner is at cell A12. Size the bottom of the chart to reach cell H30.
4. Edit the chart title to display CGL Major Sales Revenue and press Enter to start a new line. On the second line, type Second and Third Quarters.
5. Apply chart Style 14 to the chart.
6. Format the first line and the second line of the chart title to a font size of 20 pt.
7. Apply a 1 pt Black, Text 1 (second column) outline to the chart area.
8. Verify that the chart is still selected. In the highlighted range in the worksheet, drag the resize pointer to remove the Design Consulting data series from the chart (Figure 3-71). Figure 3-71 Resize the chart by dragging the resize pointer
9. Create a 3-D Pie chart sheet for cells A4:A9 and cells H4:H9. Move the chart to its own sheet named Revenue Breakdown. Hint: Use the Move Chart button [Chart Design tab, Location group].
Create a 3-D Pie chart sheet for cells A4:A9 and cells H4:H9. Move the chart to its own sheet named Revenue Breakdown. Hint: Use the Move Chart button [Chart Tools Design tab, Location group].
10. Edit the chart title to display Revenue by Category. Change the font size to 32.
11. Select the legend and change the font size to 12.
12. Apply the Woven mat texture fill (first row, fourth column) to the Patio and Furniture slice.
13. Select the pie shape and add data labels to display in the center of each slice.
1. Display the Format Data Labels task pane, choose the Accounting format [Label Options button, Number group], and set 0 decimal places.
2. Change the data label font size to 14 pt and apply bold [Home tab, Font group].
14. Deselect the chart.
15. Save and close the workbook (Figure 3-72). Figure 3-72 Excel 3-4 completed worksheet and chart
16. Upload and save your project file.
17. Submit project for grading.
Independent Project 4-4
Eller Software Services has received contract revenue information in a text file. You import, sort, and filter the data. You also create a PivotTable, prepare a worksheet with subtotals, and format related data as an Excel table.
[Student Learning Outcomes 4.1, 4.3, 4.4, 4.5, 4.6, 4.8]
Files Needed: EllerSoftware-04.xlsx (Available from the Start File link.) and EllerSoftwareText-04.txt (Available from the Resources link.)
Completed Project File Name:
[your name]-EllerSoftware-04.xlsx
Skills Covered in This Project
· Import a text file.
· Use AutoFilters.
· Sort data by multiple columns.
· Create a PivotTable.
· Format fields in a PivotTable.
· Use the Subtotal command.
· Format data in an Excel table.
· Sort data in an Excel table.
This image appears when a project instruction has changed to accommodate an update to Microsoft 365 Apps. If the instruction does not match your version of Office, try using the alternate instruction instead.
1. Open the
EllerSoftware-04.xlsx
start file. Click the Enable Editing button. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it.
2. Import the
EllerSoftwareText-04.txt
file downloaded from the Resources link and load it to begin in cell A4. The text file is tab-delimited.
3. Format the values in column H as Currency with zero decimal places.
4. Click cell G4 and use the AutoFilter arrow to sort by date oldest to newest.
5. Click cell F4 and use the AutoFilter arrow to sort by product/service name in ascending order.
6. Filter the Date column to show only contracts for September using the All Dates in the Period option.
7. Edit the label in cell A2 to display Contract Amounts for September.
8. Select cells A1:H2 and press Ctrl+1 to open the Format Cells dialog box. On the Alignment tab, choose Center Across Selection.
9. Change the font size for cells A1:H2 to 20 pt (Figure 4-102) and close the Queries & Connections pane. Figure 4-102 Imported data sorted and filtered
10. Copy the Contracts sheet to the end and name the copy Data.
11. Clear the date filter.
12. Select cell A5 and click the PivotTable button [Insert tab, Tables group]. The range is identified as the EllerSoftwareText file.
13. Verify that New Worksheet is selected, deselect the Add this data to the Data Model button, and click OK.
14. Name the sheet PivotTable. Close the Queries & Connections pane.
15. Show the Product/Service and Contract fields in the PivotTable.
16. Drag the Contract field from the Choose fields to add to report area below the Sum of Contract field in the Values area so that it appears twice in the report layout and the pane (Figure 4-103). Figure 4-103 “Contract” field appears twice in the report
17. Select cell C4 and click the Field Settings button [PivotTable Analyze tab, Active Field group]. Type Average Contract as the Custom Name, choose Average as the calculation, and set the Number Format to Currency with zero decimal places.
Select cell C4 and click the Field Settings button [PivotTable Tools Analyze tab, Active Field group]. Type Average Contract as the Custom Name, choose Average as the calculation, and set the Number Format to Currency with zero decimal places.
18. Select cell B4 and set its Custom Name to Total Contracts and the number format to Currency with zero decimal places.
19. Apply Brown, Pivot Style Dark 3.
20. Select the Data sheet tab and copy cells A1:A2. Paste them in cell A1 on the PivotTable sheet.
21. Select Align Left for cells A1:A2 and 16 pt. as the font size. Edit the label in cell A2 to display Contract Amounts, September through December (Figure 4-104). Figure 4-104 Completed PivotTable
22. Copy the Data sheet to the end and name the copy Subtotals.
23. Select cell D5 and sort by City in A to Z order.
24. Select cell A5 and convert the table to a range. Select cells A5:H31 and apply No Fill [Home tab, Font group].
25. Use the Subtotal command to show a SUM for the contract amounts for each city.
26. Edit the label in cell A2 to display Contract Amounts by City.
27. Format the sheet to Landscape orientation, center the page vertically, and scale it to fit one page.
28. Click the Billable Hours sheet tab and select cell A4.
29. Click the Format as Table button [Home tab, Styles group], use Orange, Table Style Medium 10, and remove the data connections.
30. Type 5% Add On in cell E4 and press Enter.
31. Build a formula in cell E5 to multiply cell D5 by 105% and press Enter to copy the formula.
32. Select cells A1:A2 and left align them. Then select cells A1:E2, click the Launcher for the Alignment group [Home tab], and select Center Across Selection from the Horizontal list.
33. Use the AutoFilter arrows to sort by date in oldest to newest order.
34. Save and close the workbook (Figure 4-105). Figure 4-105 Excel 4-4 completed
35. Upload and save your project file.
36. Submit project for grading.
C
ontracts
E
ller Software Services
Contract
A
mounts, September through
D
ecember
B
illable Hours
Billable
Hours
Billable
1
0/
7
/1
9
3
.
2
5
12/1
8
/19
4
.50
Training
Accounting Software
5.00
Training
Technical Support
6
.00
ERP: Enterprise Resource Planning
Accounting Software
4.00
Technical Support 10/1/19 8.00
Accounting Software 9/1/19 5.00
Training
5.00
Accounting Software
5.00
POS: Point of Sale Software
4.00
4.00
Technical Support
6.00
POS: Point of Sale Software 11/15/19 4.00
ERP: Enterprise Resource Planning 4/5/20 6.00
Hilary Marschke Training 11/15/19 5.00
5.00
POS: Point of Sale Software
3.50
4.00
Accounting Software 9/15/19 5.00
5.00
Paula Valentine Training 10/15/19 4.00
Michelle Cronin POS: Point of Sale Software 12/15/19 4.00
Training 12/15/19 4.00
POS: Point of Sale Software
4.50
Training 11/15/19 4.00
Technical Support 10/1/19 3.50
Technical Support
POS: Point of Sale Software 12/15/19
1
2
3
4
5
6
7
8
9
A
B
C
D
E
Eller Software Services
Q
2
&Q
3
C
lassic Gardens and Landscapes (CGL)
A
pril
$
7
,
6
60.
1
3 $7,7
4
1.13 $7,
8
86.48 $7,6
5
0.23
1
2
3
4
5
6
7
8
A
B
C
Revenue Categories
May
Lawn Maintenance
Tree & Shrubbery
$11,819.93
$11,909.48
Patio and Furniture
$15,192.23
$13,070.48
Fountains and Irrigation
$4,478.18
$4,708.13
Revenue for Quarters 2 and 3
Contracts
| Eller Software Services | |
| Contract Amounts, September through December |
Billable
Hours
| Client Name | Product/Service | Date | |||||||||||||||||||
| Heather Guyan | Training | 10/7/19 | 3.25 | ||||||||||||||||||
| Karen Talon | Accounting Software | 10/1/19 | 5.50 | ||||||||||||||||||
| Mike Gunderson | Technical Support | 12/18/19 | 4.50 | ||||||||||||||||||
| Thomas Larson | 12/5/19 | 5.00 | |||||||||||||||||||
| Wade Whitworth | 11/14/19 | ||||||||||||||||||||
| Paula Valentine | 11/15/19 | 4.00 | |||||||||||||||||||
| Richard Malinowski | 1/5/20 | 3.00 | |||||||||||||||||||
| Robert Gneiss | ERP: Enterprise Resource Planning | 9/1/19 | 6.00 | ||||||||||||||||||
| Susan Charlotte | 3/5/20 | 8.00 | |||||||||||||||||||
| Martin Green | 3/7/20 | ||||||||||||||||||||
| 2/3/20 | |||||||||||||||||||||
| Craig Brand | |||||||||||||||||||||
| Glenn Ladewig | |||||||||||||||||||||
| Terri Olander | POS: Point of Sale Software | 10/15/19 | 6.50 | ||||||||||||||||||
| 3.50 | |||||||||||||||||||||
| Hilary Marschke | 3/8/20 | ||||||||||||||||||||
| Shelly Vlcko | 9/15/19 | ||||||||||||||||||||
| 2/2/20 | |||||||||||||||||||||
| Michelle Cronin | 12/15/19 | ||||||||||||||||||||
| 4/5/20 | |||||||||||||||||||||
| Arthur Weston | 5/5/20 | ||||||||||||||||||||
| Christine Barton | |||||||||||||||||||||
| Charlie Lindberg | |||||||||||||||||||||
| 11/16/19 | |||||||||||||||||||||
| 10/28/19 | 4.75 | ||||||||||||||||||||
| Jeremie Midboe | 11/1/19 | ||||||||||||||||||||
| 3/18/20 | |||||||||||||||||||||
| Arnold Robert | |||||||||||||||||||||
| 5/17/20 | |||||||||||||||||||||
| Amir Atef | |||||||||||||||||||||
| 2.00 | |||||||||||||||||||||
| 2/5/20 | 7.00 | ||||||||||||||||||||
| Jerri Salzman | 3/9/20 | ||||||||||||||||||||
| Juan Garcia | |||||||||||||||||||||
| Elizabeth Jones | |||||||||||||||||||||
| Robert Lawlor | 1/8/20 | 2.75 | |||||||||||||||||||
| Patricia Glunz |
| Client Name | Address | Phone Number | City | Zip | Product/Service | Date | Contract | |||||||||||||||||||||
| Heather Guyan | 124 East Street | 218-555-2313 | Bemidji | 56601 | Training | 10/7/2019 | 1500 | |||||||||||||||||||||
| Craig Brand | 554 2nd Street | 320-555-4433 | Saint Cloud | 56301 | Technical | 10/1/2019 | 1050 | |||||||||||||||||||||
| Wade Whitworth | 1822 Highway 2 | 218-555-4211 | Cass Lake | 56633 | Accounting Software | 9/21/2019 | 5200 | |||||||||||||||||||||
| Terri Olander | 459 10th Avenue | 218-555-8977 | Brainerd | 56401 | POS: Point of Sale Software | 10/15/2019 | 6525 | |||||||||||||||||||||
| Charlie Lindberg | 345 Lyndale Avenue | 612-555-2156 | Minneapolis | 55401 | ERP: Enterprise Resource Planning | 9/1/2019 | 12000 | |||||||||||||||||||||
| Hilary Marschke | 245 West 3rd Avenue | 320-555-5443 | 11/15/2019 | 750 | ||||||||||||||||||||||||
| Shelly Vlcko | 402 2nd Avenue SE | 218-555-2456 | Deer River | 56636 | 9/15/2019 | 4500 | ||||||||||||||||||||||
| Mike Gunderson | 304 Irvine Avenue | 218-555-9021 | Technical Support | 10/28/2019 | 990 | |||||||||||||||||||||||
| Jeremie Midboe | Pilot Knob Road | 651-555-2789 | Eagan | 55121 | 11/1/2019 | 7500 | ||||||||||||||||||||||
| Arthur Weston | 554 3rd Street | |||||||||||||||||||||||||||
| Susan Charlotte | 507 Lyndale Avenue | 612-555-2177 | 15000 | |||||||||||||||||||||||||
| Thomas Larson | 1044 Highway 2 | 218-555-1234 | 2500 | |||||||||||||||||||||||||
| Arnold Robert | 500 2nd Avenue SW | 218-555-2556 | 3000 | |||||||||||||||||||||||||
| Robert Gneiss | 245 Western Avenue | 612-555-2356 | 10000 | |||||||||||||||||||||||||
| Paula Valentine | 127 Oak Street | 950 | ||||||||||||||||||||||||||
| Michelle Cronin | 800 | 12/15/2019 | ||||||||||||||||||||||||||
| Amir Atef | 300 South Avenue | |||||||||||||||||||||||||||
| Richard Malinowski | 309 Fifth Street | |||||||||||||||||||||||||||
| Glenn Ladewig | 1600 Highway 2 | |||||||||||||||||||||||||||
| Christine Barton | 902 Clinton Street | 5500 | ||||||||||||||||||||||||||
| Robert Lawlor | 612-555-2376 | 1400 | ||||||||||||||||||||||||||
| Martin Green | 218-555-2353 | |||||||||||||||||||||||||||
| Jerri Salzman | 800 Lake Avenue | 218-555-8927 | 7000 | |||||||||||||||||||||||||
| Juan Garcia | 400 South Avenue | 320-555-5448 | ||||||||||||||||||||||||||
| Elizabeth Jones | 450 Fifth Street | 320-555-4422 | ||||||||||||||||||||||||||
| Karen Talon | 1600 Highway 3 | 218-555-4287 | 4000 | |||||||||||||||||||||||||
| Patricia Glunz | 902 Gunter Street | 218-555-8900 | 3200 |
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
