Question
With the Data File KT1SP23DE.xlsx: 1. In the Documentation worksheet, enter your name in cell B3 and the date in cell B4 (.66 points) 2.
With the Data File KT1SP23DE.xlsx: 1. In the Documentation worksheet, enter your name in cell B3 and the date in cell B4 (.66 points) 2. On the Budget worksheet, format the data in cell B5 to be accounting by using the options Number area on the Home tab on the ribbon. Be sure to keep the data negative. (10 pts) 3. In the range D12:O12, calculate the total income for each month from Tom and Marias combined monthly salaries. (10 pts) 4. In the range D19:O19, use an absolute reference to insert the monthly mortgage payment from in cell B5. (10 pts) 5. In the range D21:O21, calculate Tom and Marias total expenses per month. (10 pts) (hint: this should be kept a negative number!) 6. In the range D22:O22, calculate the couples monthly net income by adding their total income and their total expenses. (13 pts) 7. In cell B26, calculate the average of Marias income from the 12-month budget. Copy the formula down into the cell range B27:B38 to get the averages of Toms income and the expenses. Use the fill without formatting option. (13 pts) 8. In cell range D29:D36, use the VLOOKUP function to determine who uses the expense in that row the most. This can be found in the last column of the table range C13:P20. The point of this question is to have you demonstrate your ability to use VLOOKUP not your efficiency. (15 pts) 9. Create the appropriate rule so that the cells with a net income in D22:O22 that falls below the average will be highlighted with light red fill and blue text. (10 pts) 10. On the Budget sheet set the following print Settings: (10 pts) Add a header that displays the page number in the left section and worksheet name on the right. Have your name appear on the right side of the footer. Set the Budget sheet to print on a single page in landscape orientation. Center the sheet on the page horizontally. 11. In cell range B26:B38, add solid orange data bars to the values (negative numbers will be red). (11 pts) 12. In the range C26:C38, insert line sparklines using the values from the range D10:O22 to show how the different budget entries change throughout the year. Add the high point and low point data markers. Make the high point and the low point data markers the Green color. (13 pts) 13. Create a 3D pie chart of the income values in range A26:C27 to compare Tom and Marias income. Move the chart to the Graphs worksheet. Resize the chart to cover the range A4:E17. Change the chart title to Income, use Data Callout data labels and apply the Style3 chart style to the chart. (13 pts). 14. Create a pie chart of the expenses values in range A29:B36 to show the breakdown of the expenses. Move the chart to the Graphs worksheet. Resize the chart to cover the range H4:N17. Change the chart title to Expenses. Put the legend on the right. Add data labels. Use the best fit option. Remove the values and use percentages for the labels. (13 pts) 15. Tom and Maria were hoping to have a net income of $2250 at the end of January. In order to do this, they knew that they would need to find a home with a particular monthly rent payment. Using Goal Seek, determine what monthly payment they would need to have in order to have a $2250 net income. After using Goal Seek, click OK and leave the values. This will change some of your other answers that is okay. (15 pts) When done, upload the file to Blackboard Great job!!
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