Question

1. Click cell H10, and enter an AVERAGEIFS function to determine the average salary of full-time employees with at least one dependent. Format the results in Accounting Number Format.

2. Use Advanced Filtering to restrict the data to only display full-time employees with at least one dependent. Place the results in cell A37. Use the criteria in the range H24:M25 to complete the function.
3. Ensure that the Facilities worksheet is active. Use Goal Seek to reduce the monthly payment in cell B6 to the optimal value of $6000. Complete this task by changing the Loan amount in cell E6.
4. Create the following three scenarios using Scenario Manager. The scenarios should change the cells B7, B8, and E6.
Good
B7 = .0325
B8 = 5
E6 = 275000
Most Likely
B7 = .057
B8 = 5
E6 = 312227.32
Bad
B7 = .0700
B8 = 3
E6 = 350000
Create a Scenario Summary Report based on the value in cell B6. Format the new report appropriately.
5. Ensure that the Facilities worksheet is active. Enter a reference to the beginning loan balance in cell B12 and enter a reference to the payment amount in cell C12.
6. Enter a function in cell D12, based on the payment and loan details, that calculates the amount of interest paid on the first payment. Be sure to use the appropriate absolute, relative, or mixed cell references.
7. Enter a function in cell E12, based on the payment and loan details, that calculates the amount of principal paid on the first payment. Be sure to use the appropriate absolute, relative, or mixed cell references.
8. Enter a formula in cell F12 to calculate the remaining balance after the current payment. The remaining balance is calculated by subtracting the principal payment from the balance in column B.

Answer 1

To determine Peter's monthly expenses and evaluate if he can meet them based on different salaries, and provide advice if he wants to move out.

Step-by-step explanation:

1. Calculate Peter's monthly expenses by adding up all his budgeted expenses for the month and record the answer in row 5 of the table.
2. To determine if Peter can meet his expenses with each salary, compare his monthly expenses to the monthly income for each job position. If the monthly income is greater than or equal to the monthly expenses, Peter will be able to meet his expenses.
3. If Peter wants to move out of his parent's house, my advice would be to carefully consider his financial situation and create a realistic budget. He should also save up an emergency fund and make sure he is prepared for additional expenses like rent, utilities, and groceries.