Income & Expenses sheet: 1. Calculate Januarys Utilities expenses (B10) by multiplying the Sales Income (B6) by the Utilities % (B18) under the assumptions heading. Remember to use Absolute and Relative referencing. Copy this function across row 10 for February through December. (If you end up with any 0s, you didnt use the correct absolute and relative referencing in your formula). 2. Calculate Januarys Rent expenses (B11) using the above directions except using the Rent % (B19) under assumptions and copy across row 11 through December. 3. Calculate Januarys Staff expenses (B12) using the above directions except using the Staff % (B20) under assumptions and copy across row 12 through December. 4. To calculate Januarys Staff Bonuses (B13), use the IF function to compare Januarys Income (B6) with the Target for Bonus value in assumptions (B21). If they have met or exceed the target, then they will get a bonus (multiplying the Bonus % under assumptions (B22) with the Income (B6). If they dont meet or exceed the target, they get $0. Be sure to use absolute and relative referencing. Copy this function across through December. Note, you will get some cells with 0s.

Payment Calculator sheet 1. In cell D7, use the function which will calculate the payment the customer will owe each month (display the payment as a positive value). 2. Complete the two variable data table in cells F5-K20 which shows the payment based upon the number of months by interest rate (Note, you must use the Data Table tool in Excel there will be no duplicated values in the Data Table. If there are then your data table is incorrect. You can highlight cells G6 K20 and press the Delete key to try it again. If it isnt responding, press the Esc key once or twice, then try again). 3. In cell D7, perform Goal Seeking to keep the monthly payment at $3,800 by changing the Sales Price. Accept the goal seeking changes. Copy and paste the new Sales Price in cell B5 to cell B12 (to store for later). 4. Change the Sales Price (B5) back to $987,000. (Note, when you do this, cell B12 should not change to $987,000)

Hermes Estate Sales Projected Income and Expenses January 1, 2020 to December 31, 2020 Hermes Estate Sales Hermes Estate Sales Projected Income and Expenses January 1, 2020 to December 31, 2020 Hermes Estate Sales