Question

James Pike, a new client who lives in Saskatoon area, has approached you for financial planning assistance. James runs his own business teaching underwater basket weaving on-line. James makes $5,000 per month. James’ wife Janet works for Real Canadian Superstore on a full-time basis and makes approximately $3,000 per month. Both figures are gross income, before deductions. James and Janet pay approximately 25% tax on their combined income.

James and Janet have two children (age 5 and 10). The Pikes own two houses worth $750,000 in total. The residential house has two bedrooms and a basement suite. James uses one basement suite as his office to teach, the other to store the children when they come home from school. Another small two-bedroom bungalow is rented for $1,000 per month. Both houses have a $200,000 mortgage outstanding and the banks charge 4.50% fixed interest per year. The mortgages are amortized over 25 years and have 5 years to maturity. The Pikes do live in Canada; maybe just down the street from your place! Other information on James & Janet is as follows:

Monthly Expenses

All “Car” expenses are for both cars combined; all “House” expenditures are for both buildings.

Mortgage payment on $400,000 mortgages	$2,214
Property tax, two houses	400
Home insurance	100
Home maintenance	50
Gas	250
Electricity	200
Water, sewer and Garbage	100
Cable TV	100
Telephone	30
Internet	65
Groceries	600
Car insurance	250
Car expenses- fuel & oil	200
Car maintenance	100
Clothing and personal items	300
Disability insurance (James)	100
Employee Savings Plan (Janet)	300
Registered Education Savings Plan	150

James and Janet have $2,000 in cash in their sock drawer and $10,000 in their chequing account. Their two cars are worth approximately $30,000, the TV is worth $1,200, and other furniture is worth $6,500.

They have two VISA cards with a combined $25,000 balance outstanding and a line of credit from RBC with a zero balance. James and Janet pay Prime + 3% on their line of credit and VISA charges 10.0% on outstanding balances (as at 19 August 2019). The current prime rate is 2.70%.

1. Based on the information provided, please prepare a cash flow statement and a balance sheet for the Pikes in an Excel spreadsheet and label the first column “Year 1 / 2020”. Don't forget the ratios. If you put the Balance Sheet and the Ratios immediately under the Cash Flow Statement, you can use the same column headers and it's easier to follow the changes year-by-year (see below).

Please show your work and assumptions on your spreadsheet (an empty column to the right of your financials is useful for this). For example: How did you calculate the monthly mortgage & loan payments? What did you base your growth rates on, if not explicitly stated below?

Based on the following assumptions, please show the Pikes’ Cash Flow and Balance Sheet projections for the next four years (from Year Two to Year Five), adding the appropriately labelled columns to your initial exercise. Remember that these are cash projections, so you needn't worry about non-cash items such as depreciation, accruals and the like.

Cost of living for the next five years (all taxes and service & maintenance costs)	1.5%
Janet’s annual pay increases for the next five years	2%
James’ annual increase to Gross Income over the next 5 years	4%
Income tax over the next five years	Unchanged
Property assessment in Year Three	One house $500,000 Other house $200,000
No additional children, accidents or illness. No housing or automobile upgrades.
Roof repair, one building in Year Three. No insurance coverage (it was just old and there were no timely hail storms)	$15,000
RESP contributions	increase at same rate as combined gross income

Answer 1

A cash flow statement is a financial statement that summarizes the amount of cash and cash equivalents entering and leaving a company. The cash flow statement measures how well a company manages its cash position, meaning how well the company generates cash to pay its debt obligations and fund its operating expenses.

A balance sheet is a financial statement showing a business's worth at a given point in time by outlining the assets, liabilities, & equity of the company.

The bank has to make sure that you're able to repay the loan on time. The higher the monthly disposable income, the higher will be the loan amount you will be eligible for. Typically, a bank assumes that about 50% of your monthly disposable/surplus income is available for repayment. The tenure and interest rate will also determine the loan amount. Further, the banks generally fix an upper age limit for home loan applicants, which could impact one's eligibility.
Multiply the amount you borrow by the annual interest rate. Then divide by the number of payments per year. There are other ways to arrive at that same result