Microsoft Excel Assessment Tom is evaluating his monthly bills to determine the effect of changes he could potentially make to his monthly expenses to help him pay off his loan from his rich Uncle. Tom works two jobs; his primary full time job is a customer service representative with Hewlett Packard (HP) and works weekends at the local Best Buy. Tom brings home $2,500 from HP and $500 from Best Buy after taxes monthly. The following are Tom's required monthly living expenses. Tom spends approximately $600 on rent, $150 on utilities, S70 on car insurance and $300 on his car payment. Tom's grocery bill averages $200 per month, and he pays approximately $200 in gas. Tom is currently paying his Uncle $200 per month on his original loan of $10,000 which he received when he was attending Austin Peay State University. The balance of the loan from Tom's Uncle is $9,600. Tom is also paying the minimum payment of $50 per month on his credit card with Capital One. Tom spends the remainder of his pay on the following items. Tom enjoys the thrill of online gambling and regularly expends a minimum of $300 per month. Tom also has a thriving social life and spends approximately $400 on going to the movies, eating out with friends, and attending parties. To stay fit Tom has a membership at a gym for $50 where he also pays for personal training sessions for an extra $150 per month. As a reward for staying in shape, Tom goes shopping once a month and spends $100 on frivolous purchases. Finally, to maintain networking connections Tom pays for a membership at a Golf Club in town for $150 per month Prepare the worksheets described below in the Excel template provided. Only one Excel file should be used to complete this assessment. Worksheet 1 - 1) Rename 'Sheet l'as "Original Expenses" and insert a centered Header with the worksheet name. Hint: The header worksheet name should change as the tab is renamed. 2) In the template the data is organized into two sections, income and expenses 3) Use the Sum function to total the income and expenses. Use a calculation to determine the "Cash Left Over" after Tom pays all of his monthly expenses. 4) Create a column next to the expense values and name it % of Income." Use a calculation within the cell to calculate the percentage of total income for each expense item. Format the values using percentage style. 5) Below the income, expense and left over cash rows create a SUMIF function to sum all the expense amounts that exceed 11% of the total income. 6) Use the proper number formatting for the values calculated. Worksheet 2 - 1) Rename 'Sheet 2' as "Adjusted Expenses and insert a centered Header with the worksheet name. Hint: The header worksheet name Worksheet 2 - 1) Rename 'Sheet 2' as Adjusted Expenses and insert a centered Header with the worksheet name. Hint: The header worksheet name should change as the tab is renamed. 2) Use linking to bring the income and expense values from the Original Expenses worksheet into the Adjusted Expenses worksheet. a. For example the formula used for the rent dollar amount would be: 'Original Expenses"!B7 Where B7 is the cell on the Original Expenses worksheet that will be pulled into the Adjusted expenses worksheet. b. Apply the expense cuts calculations/formulas to the values linked from the Original Expenses worksheet to determine the new amount of the payments Tom will be making. c. Tom cut gambling expenses by 25%, Personal Training by 50% and all other non-required monthly expenses by 30%. (Non- required expenses include Entertainment, Gym Membership, Clothing and Golf Club Membership.) (Use a basic calculation in the cell that returns the adjusted amount, be sure to let Excel do the calculations for you. Do not hard key" the adjusted amounts into the cells, they should be created with a calculation.) 3) Repeat steps 3, 4 & 5 from Worksheet 1 instructions. 4) Create a schedule to help Tom determine how long it will take to repay the debt to his Uncle based on the original expenses and the adjusted expenses. a. Include the balance of the loan the payment amount and the number of months it will take to pay off the loan in the schedule. For example, principal/monthly payment = number of months to pay off. b. Assume that any cash left over each month after the adjusted expenses will be included in the payment to the Uncle. Use referencing in the cells for the payment amounts and the left over cash amounts to be included in the total payment on the loan. c. Calculate the number of months it will take to pay off the loan using a formula. 5) Use the proper number formatting for the values calculated. Worksheet 3 - 1) Add a third worksheet and rename it "Pie Charts and insert a centered Header with the worksheet name. 2) Create two pie charts with a Title and a Legend for: a. Percent of Original Expenses b. Percent of Adjusted Expenses using a formula. 5) Use the proper number formatting for the values calculated. Worksheet 3 - 1) Add a third worksheet and rename it "Pie Charts" and insert a centered Header with the worksheet name. 2) Create two pie charts with a Title and a Legend for: a. Percent of Original Expenses b. Percent of Adjusted Expenses - Use the formatting features and labels to clearly indicate what information is being presented. - Print Preview the entire workbook, select "Fit to one Page" and print gridlines in the printing options for each worksheet. Change the orientation of the worksheets to landscape. Confirm that all the worksheets will print efficiently. - Save the file as "YOUR NAME Excel Project" and upload to D2L. Rubric Excel Assessment Original Expenses Worksheet 1 Sum function used to total income and expenses 2 Formula used to determine "Cash Left Over 3 Formula used to determine% of Total Income for each expense item 4 SUMIF function used to sum all of the expenses that exceed 11% Adjusted Expenses Worksheet 5 Values for income and expenses are linked from Original Expenses Worksheet 6 Formula used to calculate expenses after reductions 7 Repayment schedule has references within the table 8 Apply a sum formula to calculate total payment and cash left over 9 Apply a formula to calculate repayment in months Pie Chart 10 Pie Charts have titles "Percent of Original Expenses" and Percent of Adjusted Expenses 11 Values formatted with Style 12 Charts readable 13 Correct data was used to create chart Other 14 Centered Header with the worksheet name on each worksheet 15 Case completed Sheet1 Sheet2 Balance on Loan from Uncle Credit Card Balance They Total Income Payment to Uncle Chand Online Gambling Fees Gym Membership Personal Training sessions Clothing Golf Club Membership Total Expenses Cash Left Over Sum of Expenses that Feed 11 Tom's Microsoft Excel Assessment Tom is evaluating his monthly bills to determine the effect of changes he could potentially make to his monthly expenses to help him pay off his loan from his rich Uncle. Tom works two jobs; his primary full time job is a customer service representative with Hewlett Packard (HP) and works weekends at the local Best Buy. Tom brings home $2,500 from HP and $500 from Best Buy after taxes monthly. The following are Tom's required monthly living expenses. Tom spends approximately $600 on rent, $150 on utilities, S70 on car insurance and $300 on his car payment. Tom's grocery bill averages $200 per month, and he pays approximately $200 in gas. Tom is currently paying his Uncle $200 per month on his original loan of $10,000 which he received when he was attending Austin Peay State University. The balance of the loan from Tom's Uncle is $9,600. Tom is also paying the minimum payment of $50 per month on his credit card with Capital One. Tom spends the remainder of his pay on the following items. Tom enjoys the thrill of online gambling and regularly expends a minimum of $300 per month. Tom also has a thriving social life and spends approximately $400 on going to the movies, eating out with friends, and attending parties. To stay fit Tom has a membership at a gym for $50 where he also pays for personal training sessions for an extra $150 per month. As a reward for staying in shape, Tom goes shopping once a month and spends $100 on frivolous purchases. Finally, to maintain networking connections Tom pays for a membership at a Golf Club in town for $150 per month Prepare the worksheets described below in the Excel template provided. Only one Excel file should be used to complete this assessment. Worksheet 1 - 1) Rename 'Sheet l'as "Original Expenses" and insert a centered Header with the worksheet name. Hint: The header worksheet name should change as the tab is renamed. 2) In the template the data is organized into two sections, income and expenses 3) Use the Sum function to total the income and expenses. Use a calculation to determine the "Cash Left Over" after Tom pays all of his monthly expenses. 4) Create a column next to the expense values and name it % of Income." Use a calculation within the cell to calculate the percentage of total income for each expense item. Format the values using percentage style. 5) Below the income, expense and left over cash rows create a SUMIF function to sum all the expense amounts that exceed 11% of the total income. 6) Use the proper number formatting for the values calculated. Worksheet 2 - 1) Rename 'Sheet 2' as "Adjusted Expenses and insert a centered Header with the worksheet name. Hint: The header worksheet name Worksheet 2 - 1) Rename 'Sheet 2' as Adjusted Expenses and insert a centered Header with the worksheet name. Hint: The header worksheet name should change as the tab is renamed. 2) Use linking to bring the income and expense values from the Original Expenses worksheet into the Adjusted Expenses worksheet. a. For example the formula used for the rent dollar amount would be: 'Original Expenses"!B7 Where B7 is the cell on the Original Expenses worksheet that will be pulled into the Adjusted expenses worksheet. b. Apply the expense cuts calculations/formulas to the values linked from the Original Expenses worksheet to determine the new amount of the payments Tom will be making. c. Tom cut gambling expenses by 25%, Personal Training by 50% and all other non-required monthly expenses by 30%. (Non- required expenses include Entertainment, Gym Membership, Clothing and Golf Club Membership.) (Use a basic calculation in the cell that returns the adjusted amount, be sure to let Excel do the calculations for you. Do not hard key" the adjusted amounts into the cells, they should be created with a calculation.) 3) Repeat steps 3, 4 & 5 from Worksheet 1 instructions. 4) Create a schedule to help Tom determine how long it will take to repay the debt to his Uncle based on the original expenses and the adjusted expenses. a. Include the balance of the loan the payment amount and the number of months it will take to pay off the loan in the schedule. For example, principal/monthly payment = number of months to pay off. b. Assume that any cash left over each month after the adjusted expenses will be included in the payment to the Uncle. Use referencing in the cells for the payment amounts and the left over cash amounts to be included in the total payment on the loan. c. Calculate the number of months it will take to pay off the loan using a formula. 5) Use the proper number formatting for the values calculated. Worksheet 3 - 1) Add a third worksheet and rename it "Pie Charts and insert a centered Header with the worksheet name. 2) Create two pie charts with a Title and a Legend for: a. Percent of Original Expenses b. Percent of Adjusted Expenses using a formula. 5) Use the proper number formatting for the values calculated. Worksheet 3 - 1) Add a third worksheet and rename it "Pie Charts" and insert a centered Header with the worksheet name. 2) Create two pie charts with a Title and a Legend for: a. Percent of Original Expenses b. Percent of Adjusted Expenses - Use the formatting features and labels to clearly indicate what information is being presented. - Print Preview the entire workbook, select "Fit to one Page" and print gridlines in the printing options for each worksheet. Change the orientation of the worksheets to landscape. Confirm that all the worksheets will print efficiently. - Save the file as "YOUR NAME Excel Project" and upload to D2L. Rubric Excel Assessment Original Expenses Worksheet 1 Sum function used to total income and expenses 2 Formula used to determine "Cash Left Over 3 Formula used to determine% of Total Income for each expense item 4 SUMIF function used to sum all of the expenses that exceed 11% Adjusted Expenses Worksheet 5 Values for income and expenses are linked from Original Expenses Worksheet 6 Formula used to calculate expenses after reductions 7 Repayment schedule has references within the table 8 Apply a sum formula to calculate total payment and cash left over 9 Apply a formula to calculate repayment in months Pie Chart 10 Pie Charts have titles "Percent of Original Expenses" and Percent of Adjusted Expenses 11 Values formatted with Style 12 Charts readable 13 Correct data was used to create chart Other 14 Centered Header with the worksheet name on each worksheet 15 Case completed Sheet1 Sheet2 Balance on Loan from Uncle Credit Card Balance They Total Income Payment to Uncle Chand Online Gambling Fees Gym Membership Personal Training sessions Clothing Golf Club Membership Total Expenses Cash Left Over Sum of Expenses that Feed 11 Tom's