Excel 2019 in practice – ch 5 independent project 5-4
Wilson Home Entertainment Systems monitors cash flow at their individual locations separately and consolidates data. After the summary is complete, you insert hyperlinks to each of the supporting worksheets.
[Student Learning Outcomes 5.1, 5.4, 5.6, 5.7, 5.8]
Files Needed: WilsonHome-05.xlsx (Available from the Start File link.) and WHES.png (Available from the Resources link.)
Completed Project File Name: [your name]-WilsonHome-05.xlsx
Skills Covered in This Project
- Group and format worksheets.
- Create a static data consolidation with SUM.
- Insert a picture from a file.
- Insert a hyperlink.
- Copy a hyperlink.
- Encrypt a workbook with a password.
- Open the WilsonHome-05 start file. 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.
- Group all the worksheets.
- Edit and format grouped sheets.
- Select cells A1:B2 and click the Launcher in the Alignment group [Home tab]. Choose Center Across Selection from the Horizontal list and click OK.
Figure 5-76 Consolidate dialog box for cash flow - Click the Launcher in the Page Setup group [Page Layout tab] and click the Margins tab.
- Choose Horizontally from the Center on page list and click OK.
- Edit the contents of cell A10 to read Cash paid for marketing.
- Select cell A1 and ungroup the sheets.
- Select cells A1:B2 and click the Launcher in the Alignment group [Home tab]. Choose Center Across Selection from the Horizontal list and click OK.
- Select the CashFlow sheet.
Figure 5-77 Image positioned as title - Build a static data consolidation for the Cash flow from operations section.
- Select cells B4:B12.
- Use SUM to consolidate the data from the three location sheets without links. (Figure 5-76).
- Build a static data consolidation for the Cash flow from banking and investment section in cells B15:B21. Delete the references in the Consolidate dialog box and use SUM as the function.
Figure 5-78 Hyperlink text to switch to Cash Flow sheet - Build a static data consolidation for the Cash balance at the beginning of the quarter amounts in cell B24 with SUM as the function.
- Insert a picture from a file.
- Delete the contents of cell A1 on the CashFlow sheet.
- Click cell D2.
- Click the Pictures button [Insert tab, Illustrations group].
- Find and select WHES from your student data files.
- Click Insert. The picture is placed at a default size.
- Click the Height box [Picture Tools Format tab, Size group].
- Type 1.2 to replace the default height and press Enter.
- Format the height of row 1 to 86.25 (115 pixels).
- Point to the logo frame to display a move pointer.
- Drag the image to appear in cell A1 as a main label for the worksheet (Figure 5-77).
- Click cell D2 to deselect the image.
- Insert and copy a hyperlink.
- Click cell C3 on the Peoria worksheet.
- Create a hyperlink that displays Total Cash Flow and switches to cell A1 on the Cash Flow worksheet (Figure 5-78).
- Right-click cell C3 and choose Copy from the menu.
- Select the Champaign sheet tab and paste the hyperlink in cell C3.
- Select the Rockford sheet tab and paste the hyperlink in cell C3.
- Select the Peoria sheet, and press Esc to remove the copy marquee if it is still visible.
- Select cell C5 and then click the cell with the hyperlink to test it.
- Save and close the workbook (Figure 5-79).
- Upload and save your project file.
- Submit project for grading.
