John and Marys Budget and Balance Sheet Assignment

Instructions

Please use this case study and the assumptions and facts, to prepare a 6-month budget 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). There are two parts (1) the 6-month budget (first sheet) and (2) Balance Sheet (second sheet). Please double check your work. Notice whether the couple are running surpluses or deficits, how those are calculated, and the amount of net worth that have accumulated as of 1/1/18. 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. Johns monthly take home pay is $4,167 while Mary is bringing home $4,583 each month. They receive dividends of $200 in January and April on their modest investment portfolio and earn about $25 of interest each month on their savings. John hopes to collecting a consulting fee of $3,000 (for work already complete) in February and anticipates more consulting work later in the year (but hasnt done the work yet) which should be about $4,000 to be received in May.

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 June):

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 May.

Life insurance in January 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 through March (the cold months), $200 for April, and $100 for May and June.

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 January 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 June 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 of $1,200 for a night class at a local college and will also take 3 summer classes starting in June that will cost a total of $3,600.

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

Using the above information and the Excel budget template provided, prepare a 6-month budget for John and Mary.

Balance Sheet Data

Also prepare a balance sheet for them as of January 1, 2018 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. The appraisal was done because the couple were thinking about refinancing their home mortgage but decided to wait a bit longer. Currently, the mortgage balance on the home is $200,000 and they owe $1,500 in property taxes as of 1/1/18. A small home improvement loan balance, which they used to fix up a bathroom, is $2,000. They borrowed the money from Johns Dad who wont demand repayment for another 2 years.

Mary reviews the bank statement and sees that their checking account balance as of 1/1/18 is $5,000 and they have another $10,000 in a passbook savings account at their credit union. They also have a certificate of deposit at Bank of America of $2,000.

In 2015, the couple bought a new car for $25,000 and the current estimated value per Kellys Bluebook of Used Cars is only $12,000. Furniture is estimated at $10,000 and Marys 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 December 31, 2017 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 outstanding balance of $3,000.

John recently did some consulting work on the side and is owed $3,000. He believes he will collect that in February.

Finally, the total amount owed in student loans is $15,000.

Please use the Excel template (worksheet) to prepare a balance sheet as of 1/1/18. 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 their financial affairs. The primary focus of personal balance sheet is a person's assets and liabilities, and the primary users of personal financial statements normally consider estimated current value information to be more relevant for their decisions than historical cost information.

Budget
Six Months ended June 30, 2018
Monthly income
Item	January	February	March	April	May	June	Totals
Salary - John							$0
Salary - Mary							$0
Dividends							$0
Interest							$0
Other							$0
Total	$0	$0	$0	$0	$0	$0	$0
Monthly expenses
Item	January	February	March	April	May	June	Totals
Mortgage/Rent							$0
Car loan							$0
Car insurance							$0
Property insurance							$0
Property taxes							$0
Life insurance							$0
Charity							$0
Gas/electricity/utilities							$0
Telephone (Cell)							$0
Cable and Internet							$0
Groceries and Housekeeping Supplies							$0
Health insurance							$0
Entertainment and Dinning Out							$0
Gifts							$0
Clothing							$0
Personal care products and services							$0
Education							$0
Gym Membership/Golf							$0
Total	$0	$0	$0	$0	$0	$0	$0
Surplus or (Deficit)	$0	$0	$0	$0	$0	$0	$0

Personal Financial Balance Sheet of:
Mary and John
as of:
1/1/18
Assets	Amount in Dollars
Cash - checking accounts
Cash - savings accounts
Certificates of deposit
Securities - stocks / bonds / mutual funds
Accounts receivable
Life insurance (cash surrender value)
Personal property (autos, jewelry, etc.)
Retirement Funds (eg. IRAs, 401k)
Real estate (market value)
Other assets (specify)	-
Other assets (specify)	-
Total Assets	$-
Liabilities	Amount in Dollars
Current Debt (Credit cards)
Notes payable
Taxes payable
Real estate mortgage
Other liabilities
Other liabilities	-
Total Liabilities	$-
Net Worth	$-