Cash Flows, Balance Sheet & Budgeting

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?

b. Add an extra sheet to your spreadsheet. Label it Me. Better yet, use your own name. Using the same format and layout you used for the Pikes spreadsheet, fill in your figures, as of now.

If you need to add or delete rows to your worksheet, go ahead. Be as accurate and thorough as you can be, as we will need this information later in the course.

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. Aren't I just the nicest Finance instructor you know?? Take a look at the "Linx" spreadsheet I have prepared as an example of how to keep all your rates in one table, in an easily traceable and changeable format. You would likely input the following table in a third sheet in your Excel file and wire it into your financials....

Cost of living for the next five years (all taxes and service & maintenance costs)	1.5%
Janets 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

List out as many questions you would like to ask the Pikes at your next meeting (bullet form is Ok).

What calculations in the basics and assumptions are a little fishy (sorry, that slipped out)?

What suggestion(s) would you make to the Pikes concerning debt management? Show this in your projections, as a picture is worth 1,000 words!

What sort of retirement planning would you suggest?

What numbers would we like to investigate further, tax-wise? Are there any opportunities to minimize taxes?