Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Distributed Relational Database Architecture Connectivity Guide

Authors: Teresa Hopper

4th Edition

0133983064, 978-0133983067

More Books

Students also viewed these Databases questions