Help with the following questions

CMIS2250

Introduction Carol and Dan have had enough of the city and would like to move their family to an acreage In Alberta's capital region. With all that's happened during the COVID-19 pandemic, they have found that they can no longer tolerate the noise and confinement of urban living, and that having space to safely enjoy the outdoors has become something they value greatly. They have sold their single-family home In Edmonton and are currently renting a three-bedroom duplex In St. Albert while they plan for a new home build. With all the volatility in both the global and local economies, Carol and Dan are unsure of what the near future has in store. After hearing that you have some experience in spreadsheet modelling, they've come to ask you to help them with their plan to escape to the country. Specifically, they would like to know whether they will be able to afford the home they are dreaming of while not sacrificing their current lifestyle. Family and Finances Carol and Dan are 40 and 44, respectively, and have three children: Alice (18 year), Charles (9 years), and Dunn (5 years). Carol works as a shift manager at a local coffee shop, where she's been for the last 4 years, and Dan is employed as a senior software engineer at a large IT firm, where he's been for the last 12 years. Carol earns $78,000 annual salary, and Dan earns $114,750 annual salary. In addition to his salary, Dan also does contract work throughout the year for which he typically earns an additional $18,000 to $30,000 depending on the amount of work he can find. Carol receives an annual COLA of 1.15% and Dan also receives an annual COLA of 2.19%. Dan pays 9% of his annual salary into a defined benefit pension plan (his employer matches the contribution amount), and if he vests within the plan (age 65), he will receive 61% of his highest three years' average salary in retirement. Dan is also currently contributing 5% of his annual salary into an RRSP, which is invested in long-term medium-risk ETFs that are a combination of equity and fixed income securities (70/30 split, respectively). Dan presently has $78,000 saved in his RRSP with average annual return of 7.75%. Carol does not have a pension or any retirement savings plan. They currently have no outstanding debts (e.g. loans, credit cards, etc.) and have saved $18,000 in TFSA accounts for emergencies. Impact of COVID-19 on Finances Because of the COVID-19 pandemic, Dan has been working remotely from home for the past 26 months. The couple sold their second car as a result and are currently enjoying the monthly savings that not having a second car has allowed (the primary vehicle is a 2016 midsize SUV that is fully paid for). However, it is highly likely that Dan may have to return to work in the office sometime soon, so they will have to purchase a second car when that decision is made. Progress Thus Far This past April (2022), the couple found and purchased a 2-acre lot they really liked. They purchased the privately for $327,000. Because the and purchase is separate from their home purchase, they asked Dan's parents for a bridge loan to cover the cost until they build and move into the new home. Dan's father let them use his personal line of credit to cover the land purchase and asked that they make monthly payments to at least cover the cost of the Interest, which is charged at a rate of 3.99% annually. Once they move into their new home, the couple Intend on paying out the loan to Dan's parents with a line of credit on the newly constructed house (HELOC). In the meantime, Dan is prepared to pay $1,100 per month to cover the Interest and a little bit of principal on the loan. Immediately after purchasing the lot, Carol and Dan Ilsted and sold their single-family home In May for $564,900, and then moved into a duplex rental June 1st. After closing their existing mortgage (along will all fees and penalties) and paying off some smaller debts, the couple were left with $149,000, which they plan to use for the down payment on their new home. The couple have chosen a bullder to work with, and after speaking with them about their plans, the builder has assured that they could complete a new home bulld within the next 14 months (before the end of August 2023). The builder will also allow the couple to finance the project with a completion mortgage If they pay up front 20% of the total build price (GST inclusive). Carol and Dan then met with their banker and have been pre-approved for a 25-year mortgage for a maximum of $775,000. The pre- approved mortgage is currently held at the builder rate of 3.99% for the next 12 months. If Interest rates rise during this time, they will keep the "4% rate, but if rates are lower anytime within three months before the house will be completed, then the couple will be able to use the lower rate. Both Carol and Dan feel that they will need the maximum mortgage amount (or close to it) to build their dream home, though they may be willing to compromise on some things if necessary. Additional Information Other pieces of information that Carol and Dan have provided for your analysis include: Additional monthly expenses in the duplex rental: 1 Rent: $1,650 Utilities (gas, electricity, and water): $550.00 (average) Food & Household items: $2,500.00 Telecom/internet/streaming TV: $180.00 . Entertainment: $250.00 - - Travel/Vacations: $ 600.00 e . e Insurance (auto, home, & life): $560.00 Maintenance & Repairs: $150.00 (home & autos) Fuel: $200.00 (1 auto) Kids activities: $500.00 Dan RRSP contribution: $500.00 Property taxes (for the purchased lot): $183.00 Miscellaneous: $300.00 Estimated monthly expense changes/updates anticipated in the new home: Utilities (gas, electricity, and water). $800.00 (average) Maintenance & Repairs: $450.00 (home & autos) e Estimated property taxes. $660.00 Carol's estimated tax rate applied: 21.5% Dan's estimated tax rate applied. 25% Problem Solving for Modeling Begin to translate the given situation (separate download) into a model: 1. List the key inputs (you must determine which values are important) that can be extracted from the provided information; you will also have to perform some research to obtain a few additional input values 2. 3. 4. Generate a problem statement Create an influence chart State any assumptions that you needed to make if information can be readily obtained (e.g., from the Internet), then do so Spreadsheet Engineering Create a decision support system (DSS) spreadsheet to help calculate the maximum new home purchase price. While constructing the spreadsheet, ensure that you follow the guidelines presented in the content (e.g., isolate inputs and calculations, organize data clearly, make the results clear, etc.). Your sheet can be structured as you please as long as it is clear and effective the data and calculations may dictate that you construct your calculations following a different format than the walkthrough example; not all models will be built the same way! Note: There is ambiguity here, that is part of the assignment. Determine, as accurately as you can, the most likely scenario (i.e., the base case) for how much the couple can spend on their dream home. You will have incomplete information and you will have superfluous information; sorting through the information, evaluating importance, and filling in gaps is part of the challenge. Scenarios Using the scenario manager, or manually, create three additional scenarios that can be compared to the base case that you've already created (choose appropriate names for each of the additional scenarios): Decrease mortgage rates by 0.5%, increase mortgage term to 30 years 2. Increase mortgage rates by 1.5%, remove Dan's contract earnings 3. Increase mortgage term to 30 years, increase Dan's contract earnings to max Questions From your analysis, your team must answer the following questions (provide as much information as necessary to provide a meaningful answer to the couple): 1. What is the maximum amount they will be able to spend on a new home build, while maintaining their desired lifestyle? 4. 5. 2. How will interest rates affect their purchasing power? 3. Will Dan have to adjust his RRSP contributions to get the dream home? What would the impact be if one of them were to lose their job or suffer a pay cut? Will they be able to go on a travel vacation with their family at least once a year? Introduction Carol and Dan have had enough of the city and would like to move their family to an acreage In Alberta's capital region. With all that's happened during the COVID-19 pandemic, they have found that they can no longer tolerate the noise and confinement of urban living, and that having space to safely enjoy the outdoors has become something they value greatly. They have sold their single-family home In Edmonton and are currently renting a three-bedroom duplex In St. Albert while they plan for a new home build. With all the volatility in both the global and local economies, Carol and Dan are unsure of what the near future has in store. After hearing that you have some experience in spreadsheet modelling, they've come to ask you to help them with their plan to escape to the country. Specifically, they would like to know whether they will be able to afford the home they are dreaming of while not sacrificing their current lifestyle. Family and Finances Carol and Dan are 40 and 44, respectively, and have three children: Alice (18 year), Charles (9 years), and Dunn (5 years). Carol works as a shift manager at a local coffee shop, where she's been for the last 4 years, and Dan is employed as a senior software engineer at a large IT firm, where he's been for the last 12 years. Carol earns $78,000 annual salary, and Dan earns $114,750 annual salary. In addition to his salary, Dan also does contract work throughout the year for which he typically earns an additional $18,000 to $30,000 depending on the amount of work he can find. Carol receives an annual COLA of 1.15% and Dan also receives an annual COLA of 2.19%. Dan pays 9% of his annual salary into a defined benefit pension plan (his employer matches the contribution amount), and if he vests within the plan (age 65), he will receive 61% of his highest three years' average salary in retirement. Dan is also currently contributing 5% of his annual salary into an RRSP, which is invested in long-term medium-risk ETFs that are a combination of equity and fixed income securities (70/30 split, respectively). Dan presently has $78,000 saved in his RRSP with average annual return of 7.75%. Carol does not have a pension or any retirement savings plan. They currently have no outstanding debts (e.g. loans, credit cards, etc.) and have saved $18,000 in TFSA accounts for emergencies. Impact of COVID-19 on Finances Because of the COVID-19 pandemic, Dan has been working remotely from home for the past 26 months. The couple sold their second car as a result and are currently enjoying the monthly savings that not having a second car has allowed (the primary vehicle is a 2016 midsize SUV that is fully paid for). However, it is highly likely that Dan may have to return to work in the office sometime soon, so they will have to purchase a second car when that decision is made. Progress Thus Far This past April (2022), the couple found and purchased a 2-acre lot they really liked. They purchased the privately for $327,000. Because the and purchase is separate from their home purchase, they asked Dan's parents for a bridge loan to cover the cost until they build and move into the new home. Dan's father let them use his personal line of credit to cover the land purchase and asked that they make monthly payments to at least cover the cost of the Interest, which is charged at a rate of 3.99% annually. Once they move into their new home, the couple Intend on paying out the loan to Dan's parents with a line of credit on the newly constructed house (HELOC). In the meantime, Dan is prepared to pay $1,100 per month to cover the Interest and a little bit of principal on the loan. Immediately after purchasing the lot, Carol and Dan Ilsted and sold their single-family home In May for $564,900, and then moved into a duplex rental June 1st. After closing their existing mortgage (along will all fees and penalties) and paying off some smaller debts, the couple were left with $149,000, which they plan to use for the down payment on their new home. The couple have chosen a bullder to work with, and after speaking with them about their plans, the builder has assured that they could complete a new home bulld within the next 14 months (before the end of August 2023). The builder will also allow the couple to finance the project with a completion mortgage If they pay up front 20% of the total build price (GST inclusive). Carol and Dan then met with their banker and have been pre-approved for a 25-year mortgage for a maximum of $775,000. The pre- approved mortgage is currently held at the builder rate of 3.99% for the next 12 months. If Interest rates rise during this time, they will keep the "4% rate, but if rates are lower anytime within three months before the house will be completed, then the couple will be able to use the lower rate. Both Carol and Dan feel that they will need the maximum mortgage amount (or close to it) to build their dream home, though they may be willing to compromise on some things if necessary. Additional Information Other pieces of information that Carol and Dan have provided for your analysis include: Additional monthly expenses in the duplex rental: 1 Rent: $1,650 Utilities (gas, electricity, and water): $550.00 (average) Food & Household items: $2,500.00 Telecom/internet/streaming TV: $180.00 . Entertainment: $250.00 - - Travel/Vacations: $ 600.00 e . e Insurance (auto, home, & life): $560.00 Maintenance & Repairs: $150.00 (home & autos) Fuel: $200.00 (1 auto) Kids activities: $500.00 Dan RRSP contribution: $500.00 Property taxes (for the purchased lot): $183.00 Miscellaneous: $300.00 Estimated monthly expense changes/updates anticipated in the new home: Utilities (gas, electricity, and water). $800.00 (average) Maintenance & Repairs: $450.00 (home & autos) e Estimated property taxes. $660.00 Carol's estimated tax rate applied: 21.5% Dan's estimated tax rate applied. 25% Problem Solving for Modeling Begin to translate the given situation (separate download) into a model: 1. List the key inputs (you must determine which values are important) that can be extracted from the provided information; you will also have to perform some research to obtain a few additional input values 2. 3. 4. Generate a problem statement Create an influence chart State any assumptions that you needed to make if information can be readily obtained (e.g., from the Internet), then do so Spreadsheet Engineering Create a decision support system (DSS) spreadsheet to help calculate the maximum new home purchase price. While constructing the spreadsheet, ensure that you follow the guidelines presented in the content (e.g., isolate inputs and calculations, organize data clearly, make the results clear, etc.). Your sheet can be structured as you please as long as it is clear and effective the data and calculations may dictate that you construct your calculations following a different format than the walkthrough example; not all models will be built the same way! Note: There is ambiguity here, that is part of the assignment. Determine, as accurately as you can, the most likely scenario (i.e., the base case) for how much the couple can spend on their dream home. You will have incomplete information and you will have superfluous information; sorting through the information, evaluating importance, and filling in gaps is part of the challenge. Scenarios Using the scenario manager, or manually, create three additional scenarios that can be compared to the base case that you've already created (choose appropriate names for each of the additional scenarios): Decrease mortgage rates by 0.5%, increase mortgage term to 30 years 2. Increase mortgage rates by 1.5%, remove Dan's contract earnings 3. Increase mortgage term to 30 years, increase Dan's contract earnings to max Questions From your analysis, your team must answer the following questions (provide as much information as necessary to provide a meaningful answer to the couple): 1. What is the maximum amount they will be able to spend on a new home build, while maintaining their desired lifestyle? 4. 5. 2. How will interest rates affect their purchasing power? 3. Will Dan have to adjust his RRSP contributions to get the dream home? What would the impact be if one of them were to lose their job or suffer a pay cut? Will they be able to go on a travel vacation with their family at least once a year