Question
Project Description: You carefully tracked your income and expenses for three months using one worksheet per month. The worksheets contain the same expense categories. You
Project Description:
You carefully tracked your income and expenses for three months using one worksheet per month. The worksheets contain the same expense categories. You used the Miscellaneous category to include a variety of expenses, including a vacation in June. For each month, you calculated the difference between your income and expenses as you were saving for your vacation in June. Now you want to create a three-month summary to analyze your spending habits.
Steps to Perform:
Step | Instructions | Points Possible |
1 | Start Excel. Download and open the file named Exp19_Excel_Ch09_ML1_Expenses.xlsx. Grader has automatically added your last name to the beginning of the filename. | 0 |
2 | You want to enter a title and apply a cell style to three worksheets at the same time. Group the April, May, and June worksheets. Type Savings, Income, & Expenses in cell A1. Select the range A1:C1 and apply the Heading 1 cell style. | 9 |
3 | With the worksheets grouped, you want to calculate the Ending Savings Balance. In cell C6, add the Beginning Savings Balance to the Monthly Savings Added. | 9 |
4 | With the worksheets grouped, you want to unlock a range so that it can be edited. Unlock cell C9 and the range B12:B20 in the grouped worksheets. Then, ungroup the worksheets. | 9 |
5 | Three worksheets should have 3-D references to other worksheets. Display the May worksheet. In cell C4, create a formula with a 3-D reference to cell C6 (the Ending Savings Balance) in the April worksheet. Display the June worksheet. In cell C4, create a formula with a 3-D reference to cell C6 (the Ending Savings Balance) in the May worksheet. Display the Qtr 2 worksheet. In cell C4, create a formula with a 3-D reference to cell C6 (the Ending Savings Balance) in the June worksheet. | 10 |
6 | You are ready to build functions with 3-D references to sum data from all three months on the Qtr 2 worksheet. With the Qtr 2 worksheet active, click cell C9 and insert the SUM function with a 3-D reference to total the salary amounts for all three months. Copy the function in cell C9 to cells C21 and C23. | 10 |
7 | Next, you want to insert a SUM function with a 3-D reference on the Qtr 2 worksheet. Click cell B12 and insert the SUM function with a 3-D reference to total the rent amount for all three months. Copy the function in cell B12 to the range B13:B20. | 10 |
8 | To ensure consistency among worksheets, you will group worksheets and apply formatting. Display the April worksheet, group all four worksheets, and select the range A8:C23. Fill the formats only across the grouped worksheets to copy the font formatting, indents, and number formatting. Ungroup the worksheets. | 4 |
9 | The Qtr 2 sheet contains cells containing April, May, and June text. You will insert hyperlinks to the respective worksheet data. Select cell E2 containing April, insert a hyperlink to cell C23 in the April worksheet, and include the ScreenTip with the text April balance. Select cell E3 containing May, insert a hyperlink to cell C23 in the May worksheet, and include the ScreenTip with the text May balance. Select cell E4 containing June, insert a hyperlink to cell C23 in the June worksheet, and include the ScreenTip with the text June balance. Click each cell to test the hyperlinks and correct any errors. | 11 |
10 | Use the data in the four worksheets to enter data in the range E7:E12 in the Analysis section to provide either a text or a number that relates to the labels in the range F7:F12. | 6 |
11 | You want to create a data validation rule to restrict the type of data the user enters. Display the April worksheet and create a data validation rule in cell B20. Allow decimal values that are less than or equal to $100. Create the input message title Miscellaneous Expense and input message The maximum miscellaneous expense is $100. (including the period). Create an error alert with the Stop style, error title Invalid Data, and error message You must enter a value less than or equal to $100. (including the period). Test the rule by trying to enter 500. The rule should prevent you from entering that value. Click Cancel to revert to the original $100 value. | 10 |
12 | Create a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side of all worksheets. | 4 |
13 | Protect all four worksheets without a password to enforce the locked cells. | 8 |
14 | You want to indicate that you finalized the workbook. Mark the workbook as final. Note: Mark as Final is not available in Excel for Mac. Instead, use Always Open Read-Only on the Review tab. | 0 |
15 | Save and close Exp19_Excel_Ch09_ML1_Expenses.xlsx. Exit Excel. Submit the file as directed. | 0 |
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started