1 / 4 91% + IO Enter all the information on your clients from the client data sheets into a blank Excel spreadsheet a. In that single Excel file, create 1) a data worksheet, 2) one worksheet for the Cash Flow Statement, 3) another worksheet for the Balance Sheet, 4) a worksheet for ratio calculations, and 5) a worksheet for additional calculations required for the case. b. Set up your data, assumptions, and calculations in Excel so that if you have to change a number in the data/assumptions, everything in your financial statements will automatically recalculate. You must name your cells and link cells to receive full credit. (This will be demonstrated in class.) c. Do all possible calculations in Excel. Do not calculate something on your calculator and then type the number into Excel. 2. Prepare. 1) a current balance sheet and 2) a cash flow statement for your next meeting with the Swansons. Date the Balance Sheet Dec 1, 2021 and complete the cash flow statement for the year ended December 31, 2021. a. Numbers in the financial statements must be linked to the data in the data worksheet. Do not type numbers for assets, liabilities, cash inflow, and cash outflow directly into the financial statements b. Pay attention to how you format your financial statements. In addition to being accurate, they should be neat, easy to read, and include categories that help you and the client make sense of the data. Round your numbers to 0 decimal points...no one cares about pennies, and they clutter the financial statements. Limit S signs to the first and last number in a column...every number does not need a S in front of it...just more clutter. 3. After correcting the financial statements, use the financial ratios provided on Blackboard to analyze the Swansons' current financial condition. Do the ratio calculations in a fourth Excel worksheet in the same file as your Cash Flow Statement and Balance Sheet. Link all calculations to the data found in your two financial statements. 4. Based on the financial statements and the ratios calculated, prepare a report on Mr. and Mrs. Swanson's current financial condition. This should be written to the clients and communicate the information clearly, highlighting the aspects that you feel are most important for them to understand. In this assignment you will be evaluating and analyzing current financial circumstances. You're welcome to make recommendations in this assignment. 1. The following approaches to financial planning should be utilized: i. Financial statements and ratio analysis ii. Pie chart and/or other appropriate graphics BE 50 1/3 l - 91% + 10 Client Data-Josie and Richard Swanson You just completed a data gathering session with your new clients, Mr. and Mrs. Swanson. From you conversation and the documents they've provided, you gathered a lot of financial data and learned mo about their goals. Richard is 66 years old and this is his second marriage. His first wife was killed in a auto accident 20 years ago, and he has two daughters from that marriage. Martha (age 25) lives with h long-time boyfriend, Anderson, and Jill (age 29) is married to Larry, Jill and Larry have a daughter, Marilyn (age 5) and a son, Joel (age 3). Josie is 64 years old, and this is her first marriage. Richard and Josie recently celebrated their 16h wedding anniversary. Two years after they were married Richard and Josie adopted two daughters who are now in their freshman and junior years of high school. Jackie (14) and Katie (16) are both very brig Richard and Josie live in Rochester, NY where Richard works as an English professor at St John Fisher College. He really enjoys his work and is not certain when he wants to retire...perhaps 5 to 10 years from now. After juggling work and the children for many years, Josie retired 3 years ago from her position at an accounting firm. She is very happy to have more time with their daughters and with their grandchild who live in Syracuse, NY. Richard has a base salary of $135,000 per year from his position as a tenured professor. In addition, he has held a chaired professorship for the last 10 years that increases his annual gross income by $45.000, and he plans on holding that professorship until he is age 70. Richard is covered by the SJFC 403(b) retirement plan that has an annual required employee contribution of 6% of salary. The State of New Yo also contributes 7% of Richard's salary per year to the 403(b). The contributions are tax-deferred for federal income tax purposes, but the employee contributions are taxable for Social Security (FICA) tax purposes. He currently has a balance of $1,252,331 in this 403(b) account, which is invested in a diversified portfolio In addition to payroll withholding for his required retirement plan, Richard has the following deductions from his paycheck each month: Federal income tax-$1,950 FICA: Social Security-6.2% up to $137,700 ($141.900 in 2021): Medicare-1.45% on all earnings Health insurance coverage (monthly) for Josie, Jackie, Katic, and himself-9715 Disability insurance coverage (monthly) - $225 Term life insurance coverage (monthly) - $132 When Josie retired, she rolled her company 401(k) plan into an IRA that has a current balance of $2,314,626. She began taking Social Security benefits when she tumed 64 in January. Her monthly benefit from Social Security is $2.200, and her annual federal income tax on benefits is estimated to be S1,320 Bi 60 The following are the balances in the other accounts held by Richard and Josie: Joint taxable investment account--$67,000 Joint emergency fund (money market account)--$44.222 Josie's money market account (an inheritance from Josie's mother)--$150,000 Joint checking account--$20,000 Three months ago, Josie's favorite aunt passed away and Josie received a life insurance payment of $200,000. That windfall (plus the cash flow from Josie's Social Security benefits) are what prompted Richard and Josie to call you for financial guidance. Josie wants to use the $200,000 life insurance benefit to pay for Jackie and Katie's college education, but both she and Richard feel they need help deciding how to pay for their daughters' education while continuing to pursue their other financial goals Josie has parked this money in a separate money market account until the college funding decision has been made Richard has a coin collection inherited from his father valued at $12,000. Josie owns a modest amount jewelry that cost $12,000, appraised for property insurance purposes at a replacement value of $14,750, and could be sold today for $10,000. The only life insurance the family has is a term life policy with a face value of $1,000,000 through SJFC. Premiums for this policy are paid through payroll withholding (mentioned previously). They own their home, currently assessed for property taxes at $575.000, and aggregate property taxes ru 1.83% of taxable value annually. Their original mortgage, taken out 10 years ago, was for $440,000 financed for 30 years at 3.25%. Their monthly mortgage payment for principal and interest is $ and their current mortgage balance after making 120 payments on time is $ The annual cost of their homeowner's insurance is $3,500. They estimate that their home furnishings and other personal belongings have a current market value of -$125,000. Richard drives a 2015 Dodge Ram pickup truck with a blue book value of $32,000 (fully paid for), while Josie just bought a new 2021 Lexus IS 350 last month that cost $41.500. She was miserable when she - at the urging of her husband -checked her new car's blue book value and saw that her car's value had depreciated by $5,000. Her auto loan is for $35,500, financed for 4 years at 2.69% through her credit union. Her monthly payment is $ and they will make her first payment on October I. Richard also owns a 2010 Honda Gold Wing motorcycle, which he thinks is worth $5,500. He bought it two years ago for $7.690, financed it for four years at 2.8%. He still owes $3,952 on the bike, and his payments are $169.50/month. Vehicle insurance for both cars is $2,200 annually, and motorcycle coverage costs $850 annually. In addition they have a $2 million umbrella liability policy that costs $525 annually. They anticipate their auto insurance bill will skyrocket once Katie gets her driver's license. a 60 3/3 T 91% + | The Swanson's typically spend $2,000 a month on food, S1,200 monthly on miscellaneous expenses related to the girls' activities (sports uniforms, club dues, allowances, etc.), $750 a month on entertainment, and $4,800 annually on clothing. Auto maintenance typically costs them about $3,500 a year, and they average $500/month in gas. Total utilities (electricity, gas, and water) average $750 per month. Their monthly phone bill is $450 and their Suddenlink bill (for cable TV and Internet) is S150/month. They spend approximately $8,000 annually on gifts and $10,000/year on family vacations Their out-of-poqfet health expenses average about $3.500 annually. In order to encourage Katie and Jackie to save for their college educations they set up money market account for the girls at the credit union. Katie and Jackie consistently deposit some of their earnings from babysitting, cash from gifts, etc Josie and Richard also contribute $1 50/month to each of the girls' accounts. The balances in Katie and Jackie's accounts are now $15,400 and $12,522, respectively. Historically the Swanson's used their MasterCard and Visa to make most of their purchases. At the beginning of this year they realized they were spending too much money and stopped using their cards (no additional purchases as of 1/1/21). They pay the minimum payments required for the cards, which total $781.02 per month. Their outstanding balances are $21,773 on the MasterCard and S22,599 on the Visa. The couple has learned from their experiences and is willing to work hard to pay down their debts. Goals In visiting with Richard in Josie, they have shared with you the following financial goals: Get a better picture of their current financial position Make sound financial decisions regarding the debt they are carrying should they refinance their current home, should they prepay any debt, etc. They plan on moving to Syracuse and purchasing a home when Richard retires, but they would like to know if they should go ahead and purchase that house now while interest rates are still low. Josie is lobbying for this because it would give her a nice place to stay when visiting the grandchildren, but Richard doesn't know if they can afford to maintain two homes. Provide a good college education for Katie and Jackie...and hopefully be in position to help fund 50% of their grandchildren's college educations. Have a comfortable retirement (maintain a standard of living that is consistent with their current lifestyle) and not be a burden to their children. 60 1 / 4 91% + IO Enter all the information on your clients from the client data sheets into a blank Excel spreadsheet a. In that single Excel file, create 1) a data worksheet, 2) one worksheet for the Cash Flow Statement, 3) another worksheet for the Balance Sheet, 4) a worksheet for ratio calculations, and 5) a worksheet for additional calculations required for the case. b. Set up your data, assumptions, and calculations in Excel so that if you have to change a number in the data/assumptions, everything in your financial statements will automatically recalculate. You must name your cells and link cells to receive full credit. (This will be demonstrated in class.) c. Do all possible calculations in Excel. Do not calculate something on your calculator and then type the number into Excel. 2. Prepare. 1) a current balance sheet and 2) a cash flow statement for your next meeting with the Swansons. Date the Balance Sheet Dec 1, 2021 and complete the cash flow statement for the year ended December 31, 2021. a. Numbers in the financial statements must be linked to the data in the data worksheet. Do not type numbers for assets, liabilities, cash inflow, and cash outflow directly into the financial statements b. Pay attention to how you format your financial statements. In addition to being accurate, they should be neat, easy to read, and include categories that help you and the client make sense of the data. Round your numbers to 0 decimal points...no one cares about pennies, and they clutter the financial statements. Limit S signs to the first and last number in a column...every number does not need a S in front of it...just more clutter. 3. After correcting the financial statements, use the financial ratios provided on Blackboard to analyze the Swansons' current financial condition. Do the ratio calculations in a fourth Excel worksheet in the same file as your Cash Flow Statement and Balance Sheet. Link all calculations to the data found in your two financial statements. 4. Based on the financial statements and the ratios calculated, prepare a report on Mr. and Mrs. Swanson's current financial condition. This should be written to the clients and communicate the information clearly, highlighting the aspects that you feel are most important for them to understand. In this assignment you will be evaluating and analyzing current financial circumstances. You're welcome to make recommendations in this assignment. 1. The following approaches to financial planning should be utilized: i. Financial statements and ratio analysis ii. Pie chart and/or other appropriate graphics BE 50 1/3 l - 91% + 10 Client Data-Josie and Richard Swanson You just completed a data gathering session with your new clients, Mr. and Mrs. Swanson. From you conversation and the documents they've provided, you gathered a lot of financial data and learned mo about their goals. Richard is 66 years old and this is his second marriage. His first wife was killed in a auto accident 20 years ago, and he has two daughters from that marriage. Martha (age 25) lives with h long-time boyfriend, Anderson, and Jill (age 29) is married to Larry, Jill and Larry have a daughter, Marilyn (age 5) and a son, Joel (age 3). Josie is 64 years old, and this is her first marriage. Richard and Josie recently celebrated their 16h wedding anniversary. Two years after they were married Richard and Josie adopted two daughters who are now in their freshman and junior years of high school. Jackie (14) and Katie (16) are both very brig Richard and Josie live in Rochester, NY where Richard works as an English professor at St John Fisher College. He really enjoys his work and is not certain when he wants to retire...perhaps 5 to 10 years from now. After juggling work and the children for many years, Josie retired 3 years ago from her position at an accounting firm. She is very happy to have more time with their daughters and with their grandchild who live in Syracuse, NY. Richard has a base salary of $135,000 per year from his position as a tenured professor. In addition, he has held a chaired professorship for the last 10 years that increases his annual gross income by $45.000, and he plans on holding that professorship until he is age 70. Richard is covered by the SJFC 403(b) retirement plan that has an annual required employee contribution of 6% of salary. The State of New Yo also contributes 7% of Richard's salary per year to the 403(b). The contributions are tax-deferred for federal income tax purposes, but the employee contributions are taxable for Social Security (FICA) tax purposes. He currently has a balance of $1,252,331 in this 403(b) account, which is invested in a diversified portfolio In addition to payroll withholding for his required retirement plan, Richard has the following deductions from his paycheck each month: Federal income tax-$1,950 FICA: Social Security-6.2% up to $137,700 ($141.900 in 2021): Medicare-1.45% on all earnings Health insurance coverage (monthly) for Josie, Jackie, Katic, and himself-9715 Disability insurance coverage (monthly) - $225 Term life insurance coverage (monthly) - $132 When Josie retired, she rolled her company 401(k) plan into an IRA that has a current balance of $2,314,626. She began taking Social Security benefits when she tumed 64 in January. Her monthly benefit from Social Security is $2.200, and her annual federal income tax on benefits is estimated to be S1,320 Bi 60 The following are the balances in the other accounts held by Richard and Josie: Joint taxable investment account--$67,000 Joint emergency fund (money market account)--$44.222 Josie's money market account (an inheritance from Josie's mother)--$150,000 Joint checking account--$20,000 Three months ago, Josie's favorite aunt passed away and Josie received a life insurance payment of $200,000. That windfall (plus the cash flow from Josie's Social Security benefits) are what prompted Richard and Josie to call you for financial guidance. Josie wants to use the $200,000 life insurance benefit to pay for Jackie and Katie's college education, but both she and Richard feel they need help deciding how to pay for their daughters' education while continuing to pursue their other financial goals Josie has parked this money in a separate money market account until the college funding decision has been made Richard has a coin collection inherited from his father valued at $12,000. Josie owns a modest amount jewelry that cost $12,000, appraised for property insurance purposes at a replacement value of $14,750, and could be sold today for $10,000. The only life insurance the family has is a term life policy with a face value of $1,000,000 through SJFC. Premiums for this policy are paid through payroll withholding (mentioned previously). They own their home, currently assessed for property taxes at $575.000, and aggregate property taxes ru 1.83% of taxable value annually. Their original mortgage, taken out 10 years ago, was for $440,000 financed for 30 years at 3.25%. Their monthly mortgage payment for principal and interest is $ and their current mortgage balance after making 120 payments on time is $ The annual cost of their homeowner's insurance is $3,500. They estimate that their home furnishings and other personal belongings have a current market value of -$125,000. Richard drives a 2015 Dodge Ram pickup truck with a blue book value of $32,000 (fully paid for), while Josie just bought a new 2021 Lexus IS 350 last month that cost $41.500. She was miserable when she - at the urging of her husband -checked her new car's blue book value and saw that her car's value had depreciated by $5,000. Her auto loan is for $35,500, financed for 4 years at 2.69% through her credit union. Her monthly payment is $ and they will make her first payment on October I. Richard also owns a 2010 Honda Gold Wing motorcycle, which he thinks is worth $5,500. He bought it two years ago for $7.690, financed it for four years at 2.8%. He still owes $3,952 on the bike, and his payments are $169.50/month. Vehicle insurance for both cars is $2,200 annually, and motorcycle coverage costs $850 annually. In addition they have a $2 million umbrella liability policy that costs $525 annually. They anticipate their auto insurance bill will skyrocket once Katie gets her driver's license. a 60 3/3 T 91% + | The Swanson's typically spend $2,000 a month on food, S1,200 monthly on miscellaneous expenses related to the girls' activities (sports uniforms, club dues, allowances, etc.), $750 a month on entertainment, and $4,800 annually on clothing. Auto maintenance typically costs them about $3,500 a year, and they average $500/month in gas. Total utilities (electricity, gas, and water) average $750 per month. Their monthly phone bill is $450 and their Suddenlink bill (for cable TV and Internet) is S150/month. They spend approximately $8,000 annually on gifts and $10,000/year on family vacations Their out-of-poqfet health expenses average about $3.500 annually. In order to encourage Katie and Jackie to save for their college educations they set up money market account for the girls at the credit union. Katie and Jackie consistently deposit some of their earnings from babysitting, cash from gifts, etc Josie and Richard also contribute $1 50/month to each of the girls' accounts. The balances in Katie and Jackie's accounts are now $15,400 and $12,522, respectively. Historically the Swanson's used their MasterCard and Visa to make most of their purchases. At the beginning of this year they realized they were spending too much money and stopped using their cards (no additional purchases as of 1/1/21). They pay the minimum payments required for the cards, which total $781.02 per month. Their outstanding balances are $21,773 on the MasterCard and S22,599 on the Visa. The couple has learned from their experiences and is willing to work hard to pay down their debts. Goals In visiting with Richard in Josie, they have shared with you the following financial goals: Get a better picture of their current financial position Make sound financial decisions regarding the debt they are carrying should they refinance their current home, should they prepay any debt, etc. They plan on moving to Syracuse and purchasing a home when Richard retires, but they would like to know if they should go ahead and purchase that house now while interest rates are still low. Josie is lobbying for this because it would give her a nice place to stay when visiting the grandchildren, but Richard doesn't know if they can afford to maintain two homes. Provide a good college education for Katie and Jackie...and hopefully be in position to help fund 50% of their grandchildren's college educations. Have a comfortable retirement (maintain a standard of living that is consistent with their current lifestyle) and not be a burden to their children. 60