Instructions

Please use this case study and the assumptions and facts, to prepare a $6-$month budget $($for the period July $2024$ through December $2024)$ and a personal balance sheet dated June $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 July $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 $\frac{6}{1}\frac{?}{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 September and December 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 August $2024($for work already complete. It is an accounts receivable as of June $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 December 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 $($July $2024$ through December $2024)$:

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.

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 September and $$600$ to the American Heart Association in November.

Gas$/$electricity$/$utilities $-$ $$350$ per month for November and December $($the cold months$),$ and average $$200$ for July through October.

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

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

Groceries and Housekeeping Supplies $-$ $$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 August and will be giving total of $$500$ in gifts and in November, 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 October$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 July $2024$ of $$1,200$ for a night class at a local college. She will also take $3$ fall classes starting in September $2024($ $$1,200$ each to be paid in September $2024).$

Gym Membership$/$Golf $-$ the gym member ship is $$200$ a month and Mary plans to join the local golf club in October$2024$ and plans on a membership fee $($paid in

Balance Sheet Data

Also prepare a balance sheet for them as of June $12024$ 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 October $2024)$ of $$2,800.$