Instructions

Please use this case study and the assumptions and facts, to prepare a $6-$month budget $($for the period January $2025$ through June $2025)$ and a personal balance sheet dated November $1,2024.$ 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 $(1)$ the $6-$month budget $($starting in January $2025$; first sheet$)$ and $(2)$ a Balance Sheet as of November $1,2024($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 $11/1/2024.$ 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 January 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 January $2025($for work already complete. It is an accounts receivable as of November $1,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 $2025.$

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 $2025$ through June $2025)$:

$$ Mortgage $$ the couple pay $$1,400$ a month on the home mortgage

$$ Car loan $$ they pay $$600$ a month on a car loan

$$ Car insurance $-$ $$250$ a month

$$ Property insurance $-$ $$100$ a month for the house

$$ Property taxes of $$1,500$ are owed in February and again in June of $2025.$

$$ Life insurance $$ in July of each year, the couple make a $$2,500$ payment on a $$500,000($death coverage$)$ whole Life policy that also accumulates a cash surrender value.

$$ Charity $$ they contribute about $$20$ a week to their church $$ which is $$80$ a month. They also plan a $$500$ 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 February $($the cold months$),$ and average $$200$ for March through June.

$$ Telephone $($Cell$)-$ $$100$ per month

$$ Cable and Internet $-$ $$125$ per month

$$ Groceries and Housekeeping Sup,plies $-$ $$550$ 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 includes going the movies and dinner at a restaurant.

$$ Gifts $$ They have weddings to attend in May and will be giving total of $$500$ in gifts and in June, they are planning on giving a generous gift of $$800$ to their only nephew who is graduating from UMASS Dartmouth.

$$ Clothing $$ they spend about $$50$ a month on clothing but in March $2025$ 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 $2025$ of $$1,200$ for a night class at a local college. She will also take $3$ fall classes starting in May $2025($$$1,200$ each to be paid in May $2025).$

$$ Gym Membership$/$Golf $$ the gym member ship is $$200$ a month and Mary plans to join the local golf club in May $2025$ and plans on a membership fee $($paid in April $2025)$ of $$2,800.$

Using the above information and the Excel budget template provided, prepare a $6-$month $($January $2025$ June $2025)$ budget for John and Mary.

Balance Sheet Data

Also prepare a balance sheet for them as of November $1,2024$ assuming the following information that Mary has gleaned from bank and investment account statements, life insurance contracts, a household inventory, and real estate documents. The biggest 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($November $1,2024).$ The appraisal was done because the couple were thinking about refinancing their home mortgage but decided to wait a bit longer. The mortgage balance on the home as of November $1,2024$ was $$200,000$ and they owed $$1,500$ in property taxes as of November $1,2024.$ A small home improvement loan balance, which they used to fix up a bathroom, was $$2,000.$ They borrowed the money from John$$s Dad who won$$t demand rep