Answered You can hire a professional tutor to get the answer.
Table 1: Excel spreadsheet. :- Judy's initial $30, 000 Financial Before-tax Tax amount Tax monthly -_ Fund yield 20202021 $60, 000.00 $11,047.00 rate...
Today is 1 January 2018. Judy is 20 years old today and she is planning to purchase a car priced at $80,000 on 1 July 2022. Judy believes that, at the time of purchasing the car, she should have savings to cover 40% of the car price (i.e., $32,000) and she can borrow the remaining 60% of the car price (i.e., $48,000) through a 7-year loan.
To save the 40% of the car price (i.e., $32,000), Judy plans to deposit x% of her monthly after-tax salary into a fund at the end of each month from July 2020 to June 2022.
Judy assumes that she can successfully graduate from university and find a job on 1 July 2020. Judy estimates that her initial before-tax salary will be $60,000 p.a. payable monthly. All her salary will be paid at the end of each month. Judy forecasts that her salary will grow at the rate of 2% p.a. The salary adjustment will only be conducted at the beginning of July in each year. For example, it is assumed that her salary is $60,000 p.a. payable monthly from July 2020 to June 2021, and then her salary is $60, 000(1 + 2%) p.a. payable monthly from July 2021 to June 2022.
The deposit fund yield is estimated to be 5.5% p.a. payable monthly (i.e., j12 = 5.5% p.a.) from July 2020 to June 2022.
Assume that all her salary is taxable. Progressive marginal individual income tax rates are as shown in table 1 (A5:C10). Based on the projected income and tax rate from table 1, we can calculate the income tax for a financial year1 . Then we can calculate the tax instalment and after-tax salary for each income salary payment (for simplicity, we assume that there is no Medicare Levy.). For example, if a person's annual income is $50,000. Her annual income tax is $3, 572 + ($50, 000 − $37, 000) × 0.325 = $7, 797. For each income payment, her monthly tax instalment is $7,797/12=$649.75 and after-tax monthly income is $50, 000/12−$649.75 = $3, 516.92.
Judy calculates her future income, income tax and after-tax income from July 2020 to June 2022. She then sets a formula in F8 and uses Goal Seek to find the value of x.
**1Note that a financial year is defined as a period starting on 1 July and ending on the next 30 June in Australia.
Table 1: Excel spreadsheet. ———:-— Judy’s initial $30, 000 Financial Before-tax Tax amount Tax monthly-_—Fund yield 2020—2021 $60, 000.00 $11,047.00rate 3‘12 Income 2020—2022 $61, 200.00 $11,437.00growth rate p.a.. Income Minimum 2020 202 1threshold tax payment J uly—2 021 J uly—2022J u.ne June 0 — $18, 200 0 After-tax $4,079.41? 34.146317income permonth $18, 200 —$37, 000 $37, 000 — 32.5% $3,572 Total $32,000.00$87, 000 accumulateddeposit $87, 000 — 37% $19,822 Deposit rate 30.75%$ 180, 000 X 10 Above 45% $54,2323 180, 000 a. [2 marks] Give the Excel cell formulae you would use to calculate the valueof cells F2 and F3. b. [2 mark] Give the Excel cell formulae you would use to calculate the value ofcells F6 and GS. c. [3 mark] Give the Excel cell formula you would use to calculate the value ofcell F8. (1. [4 marks] Describe the steps Judy used to generate her result for 33% in cellF9 using Goal Seek.