Advanced Project 7-7

Advanced Project 7-7

These instructions are compatible with both Microsoft Windows and Mac operating systems.

Animal Life, NFP, has its financial statement in a workbook to be finalized as a template. You insert form controls, add a note, and unlock cells to complete the template.

[Student Learning Outcomes 7.1, 7.2, 7.4, 7.5, 7.7]

 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.

File Needed: AnimalLife-07.xlsx (Student data files are available in the Library of your SIMnet account.)

Completed Project File Names: [your initials] Excel 7-7Template.xltx and [your initials] Excel 7-7.xlsx

Skills Covered in This Project

• Save a workbook as a template.
• Insert a list box form control.
• Use CONCAT and INDEX to display data from the list box control.
• Insert a check box form control.
• Remove the Locked property.
• Create and display a note.
• Create a workbook from a template.

1. Open the AnimalLife-07 workbook from your student data files. Save the workbook as a template in your Custom Office Templates folder and name it [your initials] Excel 7-7Template.
2. Insert a list box control, display date information, and place a check box control.
   1. Display the gridlines.
   2. Remove the Locked property from cell E3.
   3. Draw a list box control to cover cells E3:E4.
   4. Format the control to have 3-D shading and to display the years on the Lists worksheet in cell E3.
   5. Turn off the Locked property for the control.
   6. Select 2022 in the list box control.
   7. Start a CONCAT function in cell A4. The function should display Year Ended December 31, followed by the year that is selected in the list box control (nested INDEX function with the linked cell).
   8. Format cells A4:B4 as bold and italic, merged and centered.
   9. Place a check box form control to the right of the word Audited in row 23.
   10. Remove the label with the check box control and position it to appear centered in cell B23.
   11. Format the check box to include 3-D shading and remove the Locked property.
3. Remove sample data, unlock cells, and set a print area.
   1. Select and delete the contents of cells B7:B11 and B15:B17.
   2. Remove the Locked property from these same cells.
   3. Select cells B7, B12, B15, B18, and B20. Apply Accounting Number format with zero decimal places; this will add a currency symbol.
   4. Click the Name box, type a1:b23, and press Enter.
   5. Set this range as a print area.
4. Create and display a note.
   1. Select cell D1.
   2. Create a note (Review tab) that displays Choose the year from the list. The list box is not included in the print area.
   3. Select cell B7.
   4. Show notes on the sheet (Review tab).
5. Protect the SFP sheet without a password (Figure 7-89).
6. Save and close the template.
7. Create a workbook from the template.
   1. Save the new workbook as [your initials] Excel 7-7 in your usual folder for saving files.
   2. Select 2023 in the list box control.
   3. Complete the data as shown in the table and in Figure 7-90. You only need to type the value because the cells are formatted.Cell referenceCell valueB725,000B812,000B98,000B10100,000B1118,000B1512,000B1610,000B1755,000
   4. Click the Audited checkbox to include a checkmark.
   5. Save and close the workbook.
8. Hide the Developer tab.
9. Move [your initials] Excel 7-7Template from the Custom Office Templates folder to your usual work folder.