PLease provide your answer with your full explanation by using Excel and GOAL SEEK.
The CPI is 1.6%
Spreadsheet project task Today is 1 January 2020. Jake just used $400,000 ($200,000 is from Jake's own investment and $200,000 is raised by taking a 5-year loan) to purchase a cafe fran- chise. To operate this business, Jake needs to pay rent, maintenance costs, labour costs and loan repayments. 0 Jake took a 5year $200,000 loan from MQ bank. Jake needs to make 60 monthly repayment with an amount of $3,800. The loan repayment will be paid by the end of each month. This package has an annual fee of $200. The package fee is paid on 30 June of each year during the following ve-year period. The rst one will be paid on 30 June 2020. This loan will be fully repaid by the end of 5 years. 0 Rent will be paid by the end of each quarter with an amount of $12,000. a Labour cost will be paid by the end of each month with an amount of $10,000. 0 Maintenance cost will be paid by the end of each half year with an amount of $15,000. 0 Jake predicts that this cafe franchise initially can have monthly revenue of $24,000. Assume that Jake can obtain this amount by the end of each month. Jake forecasts that this revenue amount will increase at the rate of y% p.a. The revenue increase will only happen at the beginning of each year. For example, this cafe franchise initially has monthly revenue of $24,000 in 2020. Then the revenue amount will become $24,000(1+y%) per month in 2021 and $24, 000(1 + y%)2 per month in 2021 2022. Jake assumes the value of y is the same as the Australian CPI rate for 2019. You need to use FactSet to nd the Australian CPI rate for 2019. o The franchise has a term of 5 years. If Jake chooses not to renew the franchise, he needs to terminate this business by 31 December 2024. d. Jake predicts incidents, such as, theft and re, might happen in next ve years which cause a loss to his cafe. Assume that Jake chooses not to renew 1Interut-only repayment means your repayments only cover the interest on the amount you have borrowed, during the interest-only period. For example, if you borrow $1,000 through a ve-year mortgage on 1 January 2020 with a one year interest-only period at 3512 = 6% during the rst year (1 January 202031 December 2020), your monthly repayment is $1,000 X 6%/ 12 = $5 per month. On 1 January 2021, you need to use the remaining four years to repay the borrowed $1,000. The present value on 1 January 2021 of all payments in the remaining four years should be equal to $1,000. L Financial Modelling Spreadsheet Project Task S2 2020 the franchise, recalculate J ake's effective yearly return rate (3'1) over this ve- year investment period by considering following scenarios. Label this sheet as "Part d". 0 Scenario 1: an incident happens in July 2023. J ake's cafe loses all revenue of that month. Assume that all the other costs remain same. 0 Scenario 2: an incident happens in January 2024. Jake's cafe loses 50% of revenue from January 2024 to February 2024. Assume that all other costs remain same. e. Jake can purchase insurance to avoid the potential loss from the incident. Assume that Jake needs to pay $370 by the end of each month for the insurance premium to purchase this insurance product. Based on the scenarios from part d, should Jake purchase this insurance product? Label this sheet as "Part e
