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.
- 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.
Name the workbook as Yourlastname_Yourfirstname_Excel_Practice_6
- On the Data Tab, in cell A14 Type Projected Growth. Select the range A14:F14 and merge and center the cells.
- Apply the cell style Heading3 to the cell A14.
- In cell A16 type Estimated Growth Rate and press tab. In cell B16 type .16.
- Select cell B16 and format it as a percent with zero decimal places.
- In cell A18 type Year and press tab. In cell B18 type 2021 and press tab. In cell C18 type 2022.
- With the range B18:C18 selected, use the fill handle to fill the range D18:F18 with the years 2023-2025.
- In cell B19, enter 15000000 and format it as Accounting Number Format.
- Apply Bold and Italics to cell B16. Use the format painter to apply the same format to cell A18.
- 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)
- 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.
- Select the columns B:F and autofit the contents.
- 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.
- Select the range B18:F19. On the Insert Tab, in the charts group, choose Recommended Charts. Choose the first option which is Line Chart.
- Move the chart so that the upper left corner is inside cell A21.
- With the chart selected, change the Chart Title to Projected Growth. Change the font color to Blue Accent1.
- With the entire chart selected, right-click any of the dollar values and choose Format Axis.
- In the Format Axis pane, under Bounds, change the Minimum to 5000000. The number will change to scientific notation. Close the Format Axis Pane.
- 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.
- With the chart still selected, on the