Independent Project 5-4

Wilson Home Entertainment Systems monitors cash flow at each location and consolidates data. After the summary is complete, you insert hyperlinks to each of the supporting worksheets, insert an image, and then work on the vendor list.

[Student Learning Outcomes 5.1, 5.4, 5.5, 5.6, 5.7, 5.8]

 DO NOT use the files below if you are completing an autograded SIMnet Project. Using the linked files below will prevent you from uploading and submitting your work in SIMnet. The files for all autograded SIMnet Projects MUST be downloaded from your class assignment.

Files Needed: WilsonHome-05.xlsx and Wilson.png (IP-E5-4) (Student data files are available in the Library of your SIMnet account.)

Completed Project File Name: [your initials] Excel 5-4.xlsx

Skills Covered in This Project

• Group and format worksheets.
• Format negative values.
• Create a static data consolidation with Sum.
• Insert a picture from a file.
• Insert a hyperlink.
• Copy a hyperlink.
• Assign the Stocks data type.
• Insert data from the Stocks data type.
• Encrypt a workbook with a password.

1. Open the WilsonHome-05 workbook from your student data files and save it as [your initials] Excel 5-4.
2. Group, edit, and format sheets.
   1. Click the Rockford sheet tab, press Shift, and click the CashFlow tab. The Rockford sheet is visible so that you will be able to see format changes. The Vendors sheet is not part of the group.
   2. Click the launcher in the Page Setup group [Page Layout tab] and click the Margins tab.
   3. Choose Horizontally from the Center on page list and click OK.
   4. Edit the contents of cell A10 to read Cash paid for marketing.
3. Format values for negative number display.
   1. Select cells B4:B25 and click the Number launcher [Home tab, Number group].
   2. Select Currency as the Category.
   3. Choose 0 decimal places.
   4. Confirm or select $ as the Symbol.
   5. Select the option to display negative numbers in red with parentheses.
   6. Click OK.
4. Insert formulas on grouped sheets.
   1. Select cell B13 and click the AutoSum button [Home tab, Editing group].
   2. Reselect the range B4:B12 and press Enter.
   3. Select cell B22 and sum cells B15:B21.
   4. Select cell B23. The formula adds cell B13 and B22.
   5. Select cell B24. This value is manually entered on each location sheet.
   6. Select cell B25. The formula adds cell B23 and B24.
   7. Select cell A1 and ungroup the sheets.
5. Build three static data consolidations for the Cash Flow sheet.
   1. Click the CashFlow sheet and select cells B4:B12.
   2. Use Sum to consolidate the data from the Peoria, Champaign, and Rockford sheets.
   3. Select cells B15:B21 and consolidate data from the location sheets. For this command, you will need to delete the existing references in the Consolidate dialog box.
   4. Select cell B24 and consolidate the cash balances from the location sheets.
6. Insert a picture from a file.
   1. Delete the contents of cell A1 on the CashFlow sheet.
   2. Set the row height for row 2 at 35.
   3. Click cell D2 and insert the Wilson picture file from your student data files.
   4. Click the Height box [Picture Format tab, Size group].
   5. Change the height to .75″.
   6. Position the image to cover cells B1:B2 (Figure 5-90).

1. Insert and copy a hyperlink.
   1. Click cell C3 on the Peoria worksheet.
   2. Create a hyperlink that displays Total Cash Flow and switches to cell A1 on the Cash Flow worksheet (Figure 5-91).
   3. Right-click cell C3 (the hyperlink) and choose Copy from the menu.
   4. Select the Champaign sheet tab and paste the hyperlink in cell C3.
   5. Select the Rockford sheet tab and paste the hyperlink in cell C3.
   6. Select the Peoria sheet, and press Esc to remove the copy marquee if it is still visible.
   7. Select cell C5 and then click the cell to test the hyperlink.

Linked data types are not included in licensed versions of Office 2021. To complete this project, you can copy a worksheet from the WilsonHome-05Supplement workbook from your student data files to your solution file. Complete steps 1-7 and steps 10-11 as listed. Complete steps 8-9 as detailed here.

8. Assign the Stocks data type.
   1. Click the Vendors sheet tab.
   2. Select cells A4:A9.
   3. Click the Stocks button [Data tab, Data Types group]. The company names are located and linked to online data. Capitalization is not consistent.
   ALTERNATE INSTRUCTION: Delete and copy a worksheet.
   1. Select and delete the Vendors worksheet.
   2. Open the WilsonHome-05Supplement workbook from your student data files .
   3. Copy the Vendors worksheet in WilsonHome-05Supplement to be the rightmost tab in [your initials] Excel 5-4 .
   4. Close the WilsonHome-05Supplement workbook without saving it.
   5. Select the Vendors sheet in [your initials] Excel 5-4 . The data was sourced from linked data types and does not display current prices. Capitalization is not consistent but reflects data type origination.
9. Format data as a table and add columns.
   1. Select cells A4:A9 and format the cells as a table with White, Table Style Medium 1. The data does not have headers.
   2. Select cell A5 for AMD and click its Add Column button. The Insert Data button becomes the Add Column button when the data is formatted as a table.
   3. Select the High field to insert a column.
   4. Click the Insert Column button again and choose Low for the next column.
   5. Insert the Price field as the next column.
   6. Edit the label in cell A4 to Name, Exchange, and Ticker.
   7. AutoFit column A (Figure 5-92). Data types extract current information, so your results will not match the figure.
   ALTERNATE INSTRUCTION: Format data as a table and edit labels.
   1. Select cells A4:D9 and format the cells as a table with White, Table Style Medium 1. The data does not have headers.
   2. Edit the label in cell A4 to Name, Exchange, and Ticker.
   3. Edit the labels in cell B4 to D4 to High, Low, and Price.
   4. Format values as Currency with two decimals.
   5. AutoFit all columns (Figure 5-92). Your values will not match the figure.
10. Encrypt the workbook with the password abc.
11. Save and close the workbook (Figure 5-93).