Excel Practice 6

Prefer to watch and learn? Check out this video tutorial:

Complete the following Practice Activity and submit your completed project.

For Excel Practice 6, we will continue to use Excel to manage projected Revenue for Paradise Beach City, where you have just been hired as a Financial Analyst. Key skills in this practice are working with formulas with more than one operator, line graphs and map charts.

1. Start Excel. Click Open, then Browse to where your data files are saved. We will continue working on the same spreadsheet as Excel Practice 5.
2. With the Excel Practice 5 open, Select File, Save As, Browse, and then navigate to your Excel folder on your flash drive or other location where you save your files. Name the workbook as Yourlastname_Yourfirstname_Excel_Practice_6
3. On the Data Tab, in cell A14 Type Projected Growth. Select the range A14:F14 and merge and center the cells.
4. Apply the cell style Heading3 to the cell A14.
5. In cell A16 type Estimated Growth Rate and press tab. In cell B16 type .16.
6. Select cell B16 and format it as a percent with zero decimal places.
7. In cell A18 type Year and press tab. In cell B18 type 2021 and press tab. In cell C18 type 2022.
8. With the range B18:C18 selected, use the fill handle to fill the range D18:F18 with the years 2023-2025.
9. In cell B19, enter 15000000 and format it as Accounting Number Format.
10. Apply Bold and Italics to cell B16. Use the format painter to apply the same format to cell A18.
11. In cell C19, we will write a formula with multiple operators that calculates the projected growth in expenses with a growth rate of 16% for the years 2022-2015. Parentheses are used to determine the order of operations since there is more than one operator. An absolute reference is used on cell B16. The formula will look like this =B19*(100%+$B$16)
12. Since an absolute reference was used on cell B16, we can use the fill handle to fill this formula to cells D19:F19. With Cell C19 selected, use the fill handle to copy the formula to cells D19:F19.
13. Select the columns B:F and autofit the contents.
14. Apply the accounting number format to the range C18:F18 and ensure there are only no decimal places. Do not worry if this worksheet is more than one page. We will adjust that at the end.
15. Select the range B18:F19. On the Insert Tab, in the charts group, choose Recommended Charts. Choose the first option which is Line Chart.
16. Move the chart so that the upper left corner is inside cell A21.
17. With the chart selected, change the Chart Title to Projected Growth. Change the font color to Blue Accent1.
18. With the entire chart selected, right-click any of the dollar values and choose Format Axis.
19. In the Format Axis pane, under Bounds, change the Minimum to 5000000. The number will change to scientific notation. Close the Format Axis Pane.
20. With the entire chart selected (not just the axis), on the Chart Tools, Format Tab, select the arrow next to shape styles. Select shape Fill, and then picture. On the Insert Pictures window, choose from File. Browse to where you store your data files, and select the Beach.jpg picture.
21. With the chart still selected, on the Chart Tools Design tab, choose Quick Styles. Select any Quick Style, or leave the picture as the background.
22. Add a new sheet to the workbook and name it Map Chart.
23. In cell A1, type Comparable Cities and press enter.
24. In cell A2 type By State and press Enter.
25. In cell A3 type State and press tab. In cell B3 type Amount and press enter.
26. In cell A4 type Florida and then press Tab and type 15500000 in cell B4 and press Enter.
27. In cell A5 type California and then press Tab and type 20000000 in cell B5 and press Enter.
28. In cell A6 type Washington and then press Tab and type 50000000 in cell B6 and press Enter.
29. In cell A7 type Virginia and then press Tab and type 10000000 in cell B7 and press Enter.
30. In cell A8 type Oregon and then press Tab and type 30000000 in cell B8 and press Enter.
31. Autofit all cell contents.
32. Select the range A1:B1 and merge and center. Apply cell style Heading 1.
33. Select the range A2:B2 and merge and center. Apply cell style Heading 2.
34. Select the range B4:B8 and apply Accounting number format with zero decimal places.
35. Select the range A4:B8. On the Insert Tab, in the Tours grouping choose the arrow next to 3D Maps and choose Open 3D Maps. If necessary, enable to the Maps, and select New Tour. If the 3-D maps are not an option, proceed to step  41.
36. In the 3D Maps window, in the Maps group, choose Map Labels.
37. In the Scene group, select Themes, and then choose any Theme.
38. In the Tour group, choose Capture Screen.
39. Return to the Map Chart tab and right click in cell D3 and select paste.
40. With the Map selected, resize and move the map so that the upper left hand corner is in cell A10 and the lower right hand corner is in cell E26.
41. Group All Sheets.
42. Press Ctrl + F2 to display the Print Preview. Examine all three pages of the workbook, launch the Page Setup Dialog box. On the Page tab, ensure all pages Fit to 1 page.
43. On the Margins tab, center on page horizontally and vertically.
44. On the Header/Footer, ensure Show the file name shows in the left section of the footer in all sheets and click OK to exit the page setup dialog box.
45. In Backstage view select Info, and show the advanced properties. Add the following:
    • Title: Paradise Beach City Analysis with Comparable States
    • Subject: BPC110 and Section #
    • Author: Your First and Last Name
    • Keywords: Line Charts, Maps and Multiple Operators
    • If necessary, change the Inventory Sheet to Landscape Orientation.
46. Run a spelling and grammar check, compare your file to the image below and make all necessary corrections. Ungroup the worksheets.
47. Submit as instructed by your instructor.