Aging

Assumptions in first 10 rows and column B D

current

30 days

60 days

90 days

A/R =

60%

25%

5%

A/P =

60%

40%

0%

Previous Year End ?

Forecasted Yearly Sales

Sales Distribution Throughout the Year

Sales Split

Corp. Tax

Bank Interest Rate (/yr)

Inflation Factor for expenses(per month)

Quarter_1

Quarter_2

Quarter_3

Quarter_4

Cash

Credit

Rate

Savings

Loan

$4,000,000

30%

20%

25%

25%

60%

40%

24%

1%

6%

0.50%

Months of the following fiscal year

Income Statement (Year)

12/31/2015

January

February

March

April

May

June

July

August

September

October

November

December

Revenue

Sales

2,000,000

COGS

30%

600,000

Gross profit

Interest revenue

15,000

Total Revenue

Expenses

Advertising

40,000

Amortization (Depreciation)

Insurance

30,000

Bank charges

180

Office and administration

100,000

Phone

35,000

Rent

60,000

Property Taxes

50,000

Utilities

45,000

Operating credit (bank loan) costs

Administrative salaries

75,000

Management salaries

150,000

Wages - fixed

130,000

Wages - variable

20%

Payroll burden

15%

Total Expenses

Net Income before taxes

Income taxes

Net income (after taxes)

Retained Earnings

Opening RE

Closing RE

Balance Sheet (P/C) Check of A-L-OE ?

Assets

Bank

100,000

Accounts receivable

55,000

Investments

50,000

Inventory

45,000

Capital assets

600,000

Accumulated amortization

Total Assets

Liabilities

Operating (bank loan) credit

300,000

Accounts payable

95,000

Rec General payables - Corp taxes

40,000

Rec General payables - Payroll

30,000

Total Liabilities

Shareholders Equity

Shareholders' loan

45,000

Drawings (withdrawals)

Common stock

20,000

Retained earnings

260,000

Total Shareholders' Equity

Total Liabilities & Shareholders' Equity

current

30 days

60 days

90 days

Accounts Receivable

Aging

Opening A/R

Increase in month

Cash flow received from A/R

Current

received in 30 days

received in 60 days

received in 90 days

Cash received from A/R

Ending A/R

current

30 days

60 days

90 days

Accounts Payable

Aging

Opening A/P

Increase From Inventory Purchases

Increase from expenses (non payroll)

Total increase this month =

Disbursements (A/P Paid):

this month

in 30 days

in 60 days

in 90 days

Total A/P Paid

Closing A/P

Inventory

Opening Inventory

Purchases

COGS

3.5%

Closing Inventory

Receiver General Corporate Taxes

Opening Rec General corp taxes

Increase this month (due the following month)

Paid in month (opening balance)

Closing Rec General Corp Taxes

Receiver General Payroll Taxes

Opening Rec General payroll taxes

Increase this month (due the following month)

Paid in month (opening balance)

Closing Rec General Payroll Taxes

Bank

Opening Bank balance

Cash Collections:

from cash sales

from AR received

from Interest revenue

Cash Disbursements

Bank Charges

Payroll and related expenses

AP paid

Rec General Corp Tax Paid

Rec General Payroll Taxes Paid

Bank balance before financing

Financing (increase in operating credit)

Decrease in Operating Credit

Bank balance after financing

Financing (Operating credit)

Opening Operating Credit

Change in Month

Closing Operating Credit

INSTRUCTION Overview/Template 1. We will all use the same template (Financial_model_FA_2014.xlsx) that I have provided you. Do not modify the template. That is, do not add, delete or move rows or columns. Do not change any of the labels. Leave the layout the way it is. If changes need to be made, a revision to this document will be issued. 2. In order to set up any company or do financial projections we will need a trial balance (list of accounts plus balances). If it is post closing we need the balance sheet = permanent accounts and the list of accounts that the client wants to use. Columns A ? D and the rows at the top should provide all of the information that you need to get started. 3. Sales will drive the model, so start at the top of the template and work your way down through the different sections using this document as a reference. 4. We will use these assumptions to start building our model. When we realize that our assumptions are incorrect, we will make changes together. 5. Models can seem overwhelming when you first see them. Break them down into small pieces that are more manageable, then make the small pieces all work together. Previous Year End 1. Column D on the worksheet is the previous year end. 2. Cell D10 is the end date of the previous year. Projections For The Following Year 1. Cell F10 starts the new year (months of this year will appear in row 10 and automatically change depending on the previous years end date shown in cell D10). 2. The months will appear formatted as Month-Year. For example if I change the previous year end to be November 30, 2010, then the months December-10, January-11, February11.will appear to the right in the same row. If I change the value in cell D10 to be March 31, 2005, then the months to the right of that cell will be April-05, May-05, June-05, etc. Revenue 1. Yearly sales forecasted = $4,000,000. Initial distribution by quarter is 30% (Q1), 20% (Q2), 25% (Q3), 25% (Q4). Q4 is automatically calculated by Excel to maintain data integrity, the same way we did it in class. In each quarter the sales are evenly distributed over each month in the quarter. For example if Q1 includes sales of $30,000 over January, February and March, then each month is assumed to have $10,000 sales. 2. Initial sales split = 60% Cash (cheques and cash), Credit (Accounts Receivable) is the remainder. Use a formula to calculate this. 3. Interest revenue is calculated using simple interest on the previous end-of-month bank balance (Bank). 1. D15 The $15000 interest revenue last year is from investments. Assume you will receive this again this year evenly spread through the year. Also, the bank pays interest on the previous months ending balance using a yearly rate=M7 . Interest is earned on the previous months ending bank balance after financing and will go into cell F15 the first month. The monthly interest rate is assumed to be the yearly interest rate/12. Expenses 1. In business, we are always looking at comparables. Expenses this year (hopefully) are comparable to last years expenses although there will probably be some increase due to inflation. Some expenses are calculated monthly based on last years numbers (adjusted for inflation) and some are based on percentages. 2. Administration salaries, Management salaries and Fixed wages are based on last years numbers. 3. Variable wages are a percent of sales (20%), payroll burden is a percentage (15%) of wages and salaries. Payroll burden (15%) covers the employers additional payroll costs or contributions to such things as WCB, dental plan, CPP, EI, medical, etc. Dont confuse payroll burden with the paycheque deductions for IT, CPP, EI . 4. Interest expense is calculated using simple interest on the previous end-of-month Operating Credit balance and is paid out in the month following (so the interest incurred on last months outstanding balance is paid this month). The monthly interest rate is assumed to be the yearly interest rate/12 . 5. Wages and salaries and payroll burden are paid out in the month that they are incurred. All other expenses (non- payroll, non- bank) are A/P. 6. Remittance for payroll burden is paid the same month as the payroll. For example the salaries and wages paid in January have payroll burden which must be paid in January. 7. Expenses (non-interest, non-bank, and non-amortization) will rise based on an inflation factor (monthly percentage increase that the user provides in cell O7). Variable wages do not because they are tied to sales. The increases start to occur in the 2nd month of your forecast so the first months numbers are based on last years totals only. 8. Cell D22 Bank charges banks charge corporations a fixed fee per month to handle transactions. Assume $15/month Balance Sheet 1. Make sure it balances before you start projecting. 2. If A does not equal L +OE we will need to make an assumption. The original balance sheet does not balance. The retained earnings was revised to a new value 3. Shareholders loan for the forecasted year will remain unchanged = $45,000 . 4. Common stock for the forecasted year will remain unchanged = $20,000 5. No other capital assets are purchased by the company during the forecasted year 6. Depreciation (amortization) on assets is assumed to be an amount per year and then distributed evenly over the entire year. Capital assets were purchased in 2011. a) Useful life = 10 years b) Salvage value = $300,000 c) year rule used in year of acquisition d) Straight line amortization Accounts Receivable 1. Opening balance of A/R (last years ending balance) is to be received during the first month of the projected period. So, if the year end was December 31, 2011, then it will be received in January, 2012. 2. Aging = current (60%), 30 days (25%), 60 days (5%), 90 days (calculated). So 60% of the current months increase in A/R is received this month, 25% the following month, etc. Accounts Payable 1. Opening balance of A/P (last years ending balance) is paid in the first month of the new year. 2. Aging = current (60%), 30 days (40%), 60 days (0%), 90 days (calculated). So 60% of this months increase in A/P is paid this month and the remainder is paid the following month. Inventory Assumptions 3. Inventory to have on hand at month end = base amount of $8,000 + COGS + a percentage (3.5% of COGS). Projection for this months purchases should be based on the next months COGS. This will be more appropriate than using this months COGS, especially when you consider changes in sales for each QTR. For example, assume March COGS (1st quarter) was $1,000 but Aprils COGS (2nd quarter) is $50,000. If we based our March purchases on Marchs COGS we might not have enough inventory for April. The inventory to have on hand at the end of the month involves a calculation that relies on the next month's COGS. For example, January 2012 EOM inventory will be $8000+February 2012 COGS+3.5% x February 2012 COGS. On the 12th month (December 2012 using the default dates), you will need to know COGS in the next month = January 2013 (which is beyond our 12 month projection). Assume the following month (January 2013) will have the same sales and COGS as your 12th month = December 2012. Receiver General Corporate Taxes - Remittance 1. Corporate income tax owing is paid in the following month. For example, January estimated income taxes owing are remitted in February. 2. Ignore Refunds (Negative corporate income tax). We will avoid these. 3. The corporate income tax rate depends on the nature of the income: passive, active, capital gains: Use the average rate shown in cell L7 (24%). Receiver General Payroll Taxes Personal Income Tax, CPP and EI Deductions - Remittances 1. The income tax rate for individuals depends on the amount and nature of their income. For example, low income wage earners could pay very little tax and high income earners could pay tax at the high rate. This may be one of those assumptions that you may not be able to accommodate or you may have to settle on an average tax rate for all employees. Assume the personal Income Tax, CPP and EI = 2 x payroll burden. 2. The Receiver General remittance (for personal income taxes, CPP and EI withheld) is in the month following. For example, January deductions are remitted in February. Bank - Operating Credit (Financing)

4. If the company has an outstanding bank loan but finds itself with surplus cash in the bank at the end of the month, it will pay back the loan in $1000 increments You will have to do multiple tests when figuring out how much to pay back: 1. is the bank balance before financing>0? 2. Is the opening operating credit balance >0? In other words, do you have money in the bank and do you have a loan? Next, if these 2 conditions are satisfied, you can move to the next step. You need to tell Excel to only reduce the amount you pay (loan reduction) by up to the opening balance (not any more or you end up with negative loans). The easiest way to accomplish this task is to use the IF and AND statements together or multiple nested IFs. You might have to dig out your Excel book to review using functions within functions (nesting). This is probably the most difficult part of the whole worksheet, so work on it, leave it alone for a while, then come back to it . Other Assumptions 1. As you go through the model, you will need to make further assumptions or make adjustments to assumptions in this document so that we can build a functioning model that is not too complex. Bring your thoughts to class. We will discuss this, then all of us will incorporate the same assumption(s) and I will append to this document and publish it to the website