John and Mary's Budget and Balance Sheet Assignment Instructions Please use this case study and the assumptions and facts, to prepare a 6-month budget (for the period January 2024 through June 2024) and a personal balance sheet. Please be sure to read the case carefully, enter the proper amounts into the Excel templates provided (Excel workbook to be downloaded) and upload the completed assignment to myCourses. There are two parts (2) the 6-month budget (starting in January 2024; first sheet) and (2) a Balance Sheet (second sheet). Please double check your work. Notice whether the couple are running surpluses or deficits (in the budget), how those numbers are calculated, and the amount of net worth (on the balance sheet) that have "accumulated" as of 9/1/2023 (assume that you are doing this analysis in September 2023). The Excel template will calculate totals and the net worth (balance sheet), You must enter numbers in the yellow cells to flesh out the budget and then the balance sheet. Budget Data John and Mary are a couple who own a home and are both employed, John's monthly take home pay is $4,167 while Mary is bringing home $4,583 each month. They receive dividends of $200 in March and June on their modest investment portfolio and earn about \$25 of interest each month on their savings. John expects to collect $3,000 of a consulting fee in february 2024 (for work already complete, It is an accounts recelvable as of January 3, 2024. John anticipates more consulting work later in the year (but hasn't done the work yet) which should be about $4,000 to be received in June of 2024 . An analysis of their spending, which Mary did by reviewing the activity in their checking account (they pay for most of their expenses with either checks or the checking account debit cards), shows the following information for preparing a 6 -month budget (January 2024 through June 2024): An analysis of their spending, which Mary did by reviewing the activity in their checking account (they pay for most of their expenses with either checks of the checking account de bit cards). shows the following information for preparing a 6 -month budget (fanuary 2024 through June 2024): - Mortgage - the couple pay $1,400 a month on the fome mortgage - Car loan - they pay 5600 a month on a car loan - Car insurance- $750 a month - Property insurance $100 a month for the house - Property taxes of $1,500 are owed in February and again in fune. - Life insurance - in lanuary of each vear, the couple make a $2,500 payment on a $500,000 (death coverage) whole life policy that abo accumulates a cash surrender value. - Charity-they contribute about $20 a week to their church-which is $30 a month. They also plan a $00 contribution to the United Way in March and \$600 to the American Heart Association in May. - Gas/electricity/utilities - $350 per month for January and fetruary (thie cold months). and average \$200 for March through June. - Telephone (Cell) - $100 per month - Cable and internet - $125 per month - Groceries and Housekecping Supplies - \$5s0 per month - Health insurance-Their enployers plan covers them for health insurance, but they must atso pay $300 per month out of their own pochets - Entertainment and Dinring Cut - \$250 per month which indudes goine the movies and - Gas/electricity/utilities - $350 per month for January and February (the cold months). and average \$200 for March through lune. - Telephone (Cell) - $100 per month - Cable and internet-\$12s per month - Groceries and Housekeeping Supplies - \$SS0 per month - Health insurance - Their employers plan covers them for health insurance, but they must also pay $300 per month out of their own pockets - Entertainment and Dinning Out - \$250 per month which indudes going the movies and dinner at a restaurant. - Gifts - They have weddings to attend in February and will be giving total of $500 in gifts and in May, they are planning on giving a generous gitt of $800 to their only nephew who is graduating from UMASS Dartmouth. - Clothing - they spend about $50 a month on dothing but in Aprit 2024 they will spend an additional $700 on a custom suit that John wants. - Personal care products and services - $120 a month. - Education - Mary has tuition due in January 2024 of $1,200 for a night class at a local college, She wilt also take 3 summer classes in fune 2024 ( $1,200 each to be paid in June 2024). - Gym Membership/Golf - the gym member ship is $200 a month and Mary plans to join the focat golf club in April 2024 and plans on a membership fee (paid in April 2024) of $2,800. Using the above Information and the Excel budget template provided, prepare a 6-inonth (January 2024 - June 2024) budget for John and Mary. Balance Sheet Data Aso prepare a balance sheet for them as of September 1, 2023 assuming the following information that Mary has gleaned from bank and investment account statements, lfe insurnnce contracts, a household inventory, and real estate documents, The bigest asset they own is their home. They purchased the home a few years ago for $225,000, The tax assessed value (used to calculate their property taxes is $250,000. A very recent appraisal was done on the property by an expert, independent reat estate appralser and showed an estimated maiket value of $300,000(1/3/24). The appraisal was dpne because the couple were thinking about refinancing their home mortgage but decided to wait a bit longet. The mortgage balance on the Balance Sheet Data Aho prepare a balance sheet for them as of September 1,2023 assuming the following inforination that Mary has gleaned from bank and investment account statements, life insurance contracts, a household inventory, and real estate documents, The bigest asset they own is their home. They purchased the home a few years ago for $225,000. The tax assessed value (used to-calculate their property taxes is $250,000. A very recent appraisal was done on the property by an expert, independent real estate appraiser and showed an estimated market value of $300,000(1/3/24). The appraisal was done because the couple were thinking about refinancing their home mortgage but decided to wait a bit longer. The mortgage batance on the home as of 9/1/23 was $200,000 and they owed $1,500 in property taxes as of 9/1/23. A small home improvement loan balance, which they used to frx up a bathroom, was $2,000. They borrowed the money from John's Dad who won't demand repayment for another 2 years. Mary reviews the bank satement and sees that their checking account batance as of 9/1/23H $5,000 and they have another $10,000 in a parsbook sawings account at their credit union. They also have a certificate of deposit at Bank of Americar of $2,000. In 2018, the couple bought a new car for $25,000 and the current estimated value por Kelly Bluebook of Uned Cars is only $12,000. Fumiture is estimated at $10,000 and Mary? enpagement ring was recently valued by a fewelry shop at $4,000 Their invstment portfolio (thiry own some shares of Hock in is frw corporations) cost $3,000 and their muket value as of the close of the stock muket on September 1, 2023 was 56,000 . In 2018, the couple bought a new car for $25,000 and the current estimated value per Kelly's Bluebook of Used Cars is only $12,000. Furniture is estimated at $10,000 and Mary's engagement ring was recently valued by a jewelry shop at $4,000. Their investment portfolio (they own some shares of stock in a few corporations) cost $3,000 and their market value as of the close of the stock market on September 1, 2023 was $6,000. Their life insurance policy has a death benefit of $500,000 (if either one of them dies, the survivor will receive $500,000 ) and a cash surrender value of $4,000 which they could cash-in if they needed to or could borrow against it. Between their employers 401(k) plan and their IRAs, the couple have a market value of $35,000. They recently used their credit cards for a vacation and holiday gifts and have an cutstanding balance of $3,000. John recently did some consulting work on "the side" and is owed $3,000. But even though he completed the consulting work, he believes he will not collect the $3,000 (accounts receivable) until sometime in June 2024. Be sure to consider that amount for the balance sheet and also for the budget. Finally, the totat amount owed in student loans is $15,000. Please use the Excef temptate (worksheet) to prepare a batance sheet as of 9/1/23. Keep in mind that personal financial statements are prepared for individuals either to formally organize and plan their financial affairs in general or for specific purposes, such as obtaining of credit, income tax planning, retirement planning. gift and estate planning, or public disclosure of theif financial affairs. The primary focus of personat batance sheet is a persori's assets and thabilities, and the primary users of personal financial statenients normally consider estimated current value information to be more relevant for their decisions than historical cost information