You have identified correct principles in Assignment #8. This assignment is designed to do the math that gets the Hopeful Family out of debt and saving for retirement. Using the attached Excel Case Study Schedule Template do the following:

Identify the Hopeful Familys current budget.

Recommend appropriate changes to the current budget so that it not only pays for all budgeted items, but has at least $100 extra to apply towards debt reduction.

Create a roll-over plan to get the family completely out of debt using an extra $100 per month that was found in the revised budget.

Create a retirement account so the Hopefuls can retire at age 65.

Brother and Sister Hopeful are both 35 years old. Brother Hopeful has a good job and brings home $4,800/month in net take home pay. He does have a profit sharing program where the company contributes to his 401k in the amount of $150/month. Because of their financial situation Brother Hopeful is not contributing to his 401k. Sister Hopeful does a small part time job out of their home while the children are at school and brings home a net take home paycheck of $350 per month.

They currently have 2 cars. The older car is paid off, but they still have a payment of $200/month on the new car for 28 more months at 7% APR. They own their home but they have a first mortgage payment of $1,149.00/month on the loan at 6% APR, and $582.82/month payment on the 2nd mortgage at 9.5% APR. Both loans were for 30 years.

When they purchased their home 8 years ago they made a substantial down payment, and their home is currently worth $175,000.

The average monthly expenses that they have provided show the following: $450 for food, $155 for lessons for ALL children combined, $30 for laundry, $20 for dry cleaning, $15 for gifts, $70 for life insurance, $200 for medical insurance, $120 for car insurance for both cars, $25 for internet, $95 for cable TV, $275 for all utilities, $75 home phone service, $85 for cell phone program for both, $170 for gas for both cars, $85 for bowling league, $35 for newspaper, $30 for misc.

They pay $615 in tithing and offerings and $35 to other charities.

They have given you the following information concerning their debt. Because they cannot make all of their payments, they have been making minimum payments, and in some cases, they have missed payments. They are always receiving debt collection phone calls and there is significant stress in their home. Sister Hopeful's mother has made some of the payments for them, but her family is not very well off and can't help much longer.

Assignment #9b-Case Study Schedules Template (1) -Excel Insert Page Layout Formulas Data Review View Help Tell me what you want to do Cut Copy . AutoSum A General Conditional Fomat as Cell Insert Delete Format Fomatting Table Styles Paste B 1 u ii- ?? ?? ? ? ? Merge & Center s-% , ?f: Sort & Find & Filter Select Clear Clipboard Font Alignment Number Cells Editing A7 INSTRUCTIONS: Once you have completed sheets #1-#4, use this sheet to summarize your findings. How? Provide links in the GREYED areas to the cell of the appropriate spreadsheet that contain your answer. 5 #1. How much are they CURRENTLY overspending each month? HINT: S/B over $200) #2a. List your top 3 recommendations for insuring that (1) the budget is positive and (2) you find at least $100 more per month for debt reduction and retirement planning 12 15 #2b. How much additional money does your new budget provide for debt reduction and retirement savings? Must be at least $100 17 18 19 20 21 #3a How long does it take to get them completely out of debt applying an extra $100 per month? months (HINT: S/B between 90 and100 months) 0.0 years #3b. How much will eventually be freed up to use in building a retirement plan? (HINT: S/B between $2,000 and $3,000 per month) #1-Current Budget #4-Retirement Pl + 100% 40 PM O Type here to search 7/18/2018 2 Assignment #9b-Case Study Schedules Template (1) -Excel Insert Page Layout Formulas Data Review View Help Tell me what you want to do Cut Copy . AutoSum A General Conditional Fomat as Cell Insert Delete Format Fomatting Table Styles Paste B 1 u ii- ?? ?? ? ? ? Merge & Center s-% , ?f: Sort & Find & Filter Select Clear Clipboard Font Alignment Number Cells Editing A7 INSTRUCTIONS: Once you have completed sheets #1-#4, use this sheet to summarize your findings. How? Provide links in the GREYED areas to the cell of the appropriate spreadsheet that contain your answer. 5 #1. How much are they CURRENTLY overspending each month? HINT: S/B over $200) #2a. List your top 3 recommendations for insuring that (1) the budget is positive and (2) you find at least $100 more per month for debt reduction and retirement planning 12 15 #2b. How much additional money does your new budget provide for debt reduction and retirement savings? Must be at least $100 17 18 19 20 21 #3a How long does it take to get them completely out of debt applying an extra $100 per month? months (HINT: S/B between 90 and100 months) 0.0 years #3b. How much will eventually be freed up to use in building a retirement plan? (HINT: S/B between $2,000 and $3,000 per month) #1-Current Budget #4-Retirement Pl + 100% 40 PM O Type here to search 7/18/2018 2