Part 2: The Maxwell Family - College Savings (15 points) Matt and Martha Maxwell live in an upscale neighborhood in Orem, Utah. Matt is a partner in the family owned automotive painting business. Martha stays home with their child, Melanie, who is age 5. After visiting with their financial planner, the couple became concerned that they were spending too much and not putting enough funds aside for Melanie's future educational needs. Matt earns $105,000 per year, but with the rising costs of education, they are concerned. Matt is an alumnus of Ohio State University, with tuition and book expenses of approximately $13,600 per year. Martha graduated from Utah Valley University. The expense for tuition and books there is estimated at about $7,500 per year. When Melanie turns 18, the couple wishes to send her to one of these two exceptional universities. They have a slight preference for Utah Valley University. The problem, however, is that with the rate at which tuition is increasing the Maxwell's are not sure they can save enough money and they have decided they do not want to borrow to pay for Melanie's education. Assume the tuition at both universities will increase at an annual rate of 6% per year for the foreseeable future. Living expenses are currently estimated at $8,000 per year at both schools. This expense is expected to grow at only 3% per year. Further, assume that Maxwell's can deposit their money into a growth oriented mutual fund at the Salt Lake City based mutual fund company, Wasatch Advisors which has historically earned 12% per annum. The couple wishes to save by having a pre-determined amount automatically withdrawn form their bank account at the end each month. They plan to contribute from now until Melanie starts college. When Melanie starts college, at the beginning of his freshman year, they will stop making contributions. They want to have enough in their account to cover all four years of college expenses when Melanie starts college. They will make annual withdrawals from the account to cover both tuition and living expenses for Melanie at the beginning of his freshman, sophomore, junior, and senior years. When the withdrawal for the senior year is made the account balance will be zero. Remember, that the funds will continue to earn interest while Melanie is in college. Complete a thorough analysis and write a professional letter to the Maxwell's (who don't understand finance) explaining the analysis you performed, why you performed it, the results and conclusions. In the letter and attached schedules provide information that answers the following questions. What will be the tuition expense, living expense, and total expense for each of the four years that Melanie will attend college? Provide the information for each University. What amount will be needed in the account when Melanie starts his freshman year if he attends Ohio State? What amount if he attends UVU? How much money will Matt and Debra have to deposit at the end of each month to allow Melanie to attend Ohio State? How much money will have to be deposited per month to allow Melanie to attend Utah Valley University? Assume that Matt and Debra stop making deposits when Melanie starts college. The Pearson's are concerned that given the current market performance the mutual fund will only earn 10% per year. If the return is only 10% how much will be needed in the account when Melanie starts college and how much will have to be deposited per month for Melanie to have sufficient funds to attend each school? Note: Assume that Melanie will pay his tuition and living expenses at the beginning of the year. Include your Excel file with your submission. Part 2: The Maxwell Family - College Savings (15 points) Matt and Martha Maxwell live in an upscale neighborhood in Orem, Utah. Matt is a partner in the family owned automotive painting business. Martha stays home with their child, Melanie, who is age 5. After visiting with their financial planner, the couple became concerned that they were spending too much and not putting enough funds aside for Melanie's future educational needs. Matt earns $105,000 per year, but with the rising costs of education, they are concerned. Matt is an alumnus of Ohio State University, with tuition and book expenses of approximately $13,600 per year. Martha graduated from Utah Valley University. The expense for tuition and books there is estimated at about $7,500 per year. When Melanie turns 18, the couple wishes to send her to one of these two exceptional universities. They have a slight preference for Utah Valley University. The problem, however, is that with the rate at which tuition is increasing the Maxwell's are not sure they can save enough money and they have decided they do not want to borrow to pay for Melanie's education. Assume the tuition at both universities will increase at an annual rate of 6% per year for the foreseeable future. Living expenses are currently estimated at $8,000 per year at both schools. This expense is expected to grow at only 3% per year. Further, assume that Maxwell's can deposit their money into a growth oriented mutual fund at the Salt Lake City based mutual fund company, Wasatch Advisors which has historically earned 12% per annum. The couple wishes to save by having a pre-determined amount automatically withdrawn form their bank account at the end each month. They plan to contribute from now until Melanie starts college. When Melanie starts college, at the beginning of his freshman year, they will stop making contributions. They want to have enough in their account to cover all four years of college expenses when Melanie starts college. They will make annual withdrawals from the account to cover both tuition and living expenses for Melanie at the beginning of his freshman, sophomore, junior, and senior years. When the withdrawal for the senior year is made the account balance will be zero. Remember, that the funds will continue to earn interest while Melanie is in college. Complete a thorough analysis and write a professional letter to the Maxwell's (who don't understand finance) explaining the analysis you performed, why you performed it, the results and conclusions. In the letter and attached schedules provide information that answers the following questions. What will be the tuition expense, living expense, and total expense for each of the four years that Melanie will attend college? Provide the information for each University. What amount will be needed in the account when Melanie starts his freshman year if he attends Ohio State? What amount if he attends UVU? How much money will Matt and Debra have to deposit at the end of each month to allow Melanie to attend Ohio State? How much money will have to be deposited per month to allow Melanie to attend Utah Valley University? Assume that Matt and Debra stop making deposits when Melanie starts college. The Pearson's are concerned that given the current market performance the mutual fund will only earn 10% per year. If the return is only 10% how much will be needed in the account when Melanie starts college and how much will have to be deposited per month for Melanie to have sufficient funds to attend each school? Note: Assume that Melanie will pay his tuition and living expenses at the beginning of the year. Include your Excel file with your submission