Creating a financial plan for a new tourism or recreation business-Financial Plan Project Help
TRMT 324: Financial Plan Project
Part 1 – Financial Plan Proposal (15% of mark)
Part 2 – Financial Plan Framework (15% of mark)
Part 3 – Complete Financial Plan (70% of mark)
Introduction
Welcome to the world of make-believe! You are asked to create a financial plan for a new tourism or
recreation business. You will use your financial plan to
try out your business idea to determine profitability and uncover potential financial problems
before you risk money creating this new business
find out how much money you will need to invest to get started and provide working capital until
the business starts making a profit
set financial goals for the business
develop a budget for the first year
showcase your vision for the business
persuade your investors to purchase shares in your business
persuade the Bank to give you the mortgage and loans you need
This financial plan can be prepared individually or in pairs.
Part 1: Financial Plan Proposal 15% of financial plan mark
Your first step is to create a Financial Plan Proposal, including
a written description of your business (Word document)
the planning assumptions needed to develop your financial exhibits (Excel document)
Marking Criteria
This proposal presents your business concept in a Word document, and the planning assumptions on
which you will base your financial exhibits in an Excel document. The proposal will be marked to the
following criteria:
Your word document presents a business concept that fits the requirements described in this
document, and is
o written clearly in professional third-person English
o free of grammar and spelling errors
o organized in a logical manner with a cover page, section headings, a brief introduction
and conclusion
o refers to exhibits in your Excel document, and is consistent with the information in your
Excel document
Your spreadsheets contain all required information described in this document and are
comprehensive;
flexible and ‘linked’ throughout;
organized and logical;
attractively, appropriately, and consistently formatted
consistent with the information in your Word document.
When I mark your proposal, I will include feedback with any recommendations for changes or additions to
create a solid base for your financial plan.
General Requirements and Assumptions
It is not necessary to do any market research for this exercise. Have fun and play with your ideas
while trying to create a plausible fantasy business.
Your fictional business must be in the tourism/recreation/hospitality industry.
You will be incorporating your business, therefore you should choose a business name that ends
with one of the following: Inc., Ltd, Incorporated, or Limited.
Locate the business wherever you like so long as you believe the location is realistic to give you
the local or tourist traffic you need.
2
Your business must have several sources of revenue, including
o revenue from providing services to your customers/clients
o revenue from selling merchandise to your customers/clients
o revenue from at least one other source – use your imagination so long as it makes sense
and is not illegal.
Sample business Service revenue
from
Sales revenue from Other revenue from
Bed and breakfast Room rentals with
breakfast
Small gift shop with
local art
Catering services, or
residential events
with a hired
presenter
White water rafting Rafting excursions Sales of customer
excursion videos
Rafting lessons
Fitness centre Memberships Juice bar or pro shop Drop in classes,
personal trainer
consultations
Spa Professional services:
massages, facials, etc
Cosmetics, vitamins Meditation classes,
couples massage
classes, or ???
You may choose to lease or buy land and building for your facilities. If you take over an existing
building, you should plan to spend a considerable amount in renovations (if buying) or leasehold
improvements (if leasing). Have fun because this assignment requires you to spend a lot of
money.
You should project using the first month to get your business set up – renovations, preparation,
hiring, advertising, etc. You’ll open your doors the first day of the second month.
You have received an inheritance from your great-aunt Bessie (or uncle Barry) who liked your
attitude. The amount of this inheritance is explained in the Excel instructions for table 8 on the
last page of this document. You will be investing this money into the business and in return the
business will give you common shares in the new business (remember, a corporation is a separate
legal entity from its owners). In addition, some local business people (your business associates)
have become interested in your ideas and they, as a group, have expressed interest in investing
1/3 of the amount you yourself will be investing. This money is an equity investment, therefore
the associates will also receive common shares in exchange for their investment. You will have
75% of the shares, the other investors will have 25%.
A: Word document – instructions
Attractive Cover Page for your proposal
Use report format for content.
Contents: two to three page description of what your business is, where located, what form of
business organization (remember, it’s a corporation), and where it is headed in general terms.
Include a description of different areas or departments of the business (i.e. pro shop, gym, juice
bar, catering, residential workshops). Include a brief discussion of the competition, explaining
how you will differentiate your business from other similar businesses currently in existence.
Pictures can also enhance your report.
B: Excel workbook – instructions
Formatting instructions
All your planning assumptions are to be in one workbook (in a single Excel file.)
Do not place more tables on any one worksheet than can be printed attractively on a single page.
You must use several worksheets for your planning assumptions.
Change the name on the tabs of the worksheets you use for your planning tables to indicate the
Table number and name (i.e. Table 1: Capital Costs)
Give each worksheet in your workbook a header with your name on the left, and your business
name on the right.
Feel free to use colors and shading to enhance appearance and readability of your tables.
Never enter a number in more than one location. For example, if you have calculated the total
cost for exercise equipment in the Capital Costs table and need that number again in your
3
Amortization table, use a formula in the amortization table to link to the original number’s
location in the capital costs table (i.e. =$C$18)
Use View, Page Layout to check that sheets will print readably and attractively
Be sure to spell check your Excel sheets before submitting!
Required Planning Assumption Tables: Tables shown below are samples to help you visualize. Feel free
to create your own layout so long as it presents the information clearly, attractively, and in logical
order.
Table 1: Capital Costs Capital assets are assets with a life longer than one year. Detail your
long-lived property plant and equipment assets in a table with category subtotals (for example,
your categories might be: land, building, exercise equipment, office equipment, etc). Your
capital costs should include the following
Land and building purchase (separate value of land and building) PLUS cost of renovation to
building for your purpose (include renovation costs to the building category)
OR
Details of renovation costs to a rented/leased building to convert it to your purpose (this
category of capital asset is called “leasehold improvements”)
Equipment and furniture
Any other long lived assets, organized by category with subtotals for each category
Table 2: Amortization of Capital Assets In the first column, list each capital asset category. In
the second column, create a formula to show the total cost for each category as calculated in
table 1 (i.e. =CellLocationFromTable1). “Life in years” refers to how many years you expect your
business to use the asset before disposing of it. Monthly amortization should be a formula
calculated based on (Cost – residual value) / (Life in years x 12). Note that land is never
amortized.
(Sample) Table 2: Amortization of Capital Assets
Capital Asset Cost Residual
Value
Life
In years
Monthly
Amortization
Land $300,000 N/A N/A
Building 400,000 $220,000 15 $1,000
Exercise
equipment
200,000 20,000 5 3,000
Totals $1,000,000 $240,000 $4,000
Table 3: Supplies – List supplies required for startup and estimate required monthly replacement
supplies (remember, supplies are things you need that are not immediately used up, but will
normally be used up within a year). Detail supplies in a table, with totals for startup and each
month. Divide your supplies into two categories – supplies with a fixed monthly cost, and those
with a variable monthly cost based on some other variable. Each item under supplies will have
either a fixed monthly cost, or a variable cost (not both)
(Sample) Table 3: Supplies
Item Jan, 2015
(Startup
Costs)
Monthly
replacements
(Feb-Dec)
Variable %
% of
(if variable)
Towels $1,500 1% Spa treatment
revenues
Cleaning/janitorial
supplies
200 $60
… additional lines as needed
Monthly Totals $xx,xxx $x,xxx N/A N/A
Cleaning supplies will
be about $60 each
month. They are
NOT expected to
vary with revenues.
Cost of towels used in
spa treatments is
variable – more
treatments means more
towels will be used.
Estimated cost of towels
is 1% of revenue from
spa treatments.
4
Table 4: Startup Inventory – List assumptions for opening day inventory of goods to be resold.
Itemize by category and total. You’ll want enough inventory to cover your highest two months of
sales.
Table 5: Other Expenditures – List assumptions for startup and ongoing monthly expenses (omit
amortization), including cost of goods sold (when you sell your inventory, its cost becomes cost
of goods sold, and you will need to replace it). Look at a sample business income statement for
ideas on what some of your other expenses might be. If an expense relies on another variable,
detail the relationship in another column instead of putting in an absolute amount. Make the last
two rows in your expenditures column Mortgage Payment and Term Loan Payment, but leave the
amounts blank. Be sure the Monthly Totals formula includes the blank cells in these two rows
(sample) Table 5: Expenses Requiring Cash Outlay
Expenditure Jan,
2015
(Startup
Costs)
Monthly
replacements
(Feb-Dec)
Variable
%
% of
(if variable)
Cost of goods
sold,
cosmetics
40% Cosmetic sales
Spa
professionals
compensation
35% Spa treatment revenue
Advertising $5,000 $600
… additional lines as needed, one line for each expense item. Do NOT include
amortization
Monthly
Totals
$xx,xxx $x,xxx
Table 6: Startup Cost Summary –This table will tell you how much money you need to start your
business. Don’t worry if it’s a large amount; your finance plan will be designed to persuade
investors to help you raise the money. List each category of cost from the tables above including
any first month (January) costs, and the cost of both supplies and expenses for February and
March. Do not type any numbers into this table – instead, link to the numbers in your other
tables.
(sample) Table 6: Startup Cost Summary
Item Cost
Land $300,000
Building 400,000
… line for each
additional capital asset
Supplies
(startup/January)
Supplies Feb/March
Expenditures
(startup/Jan)
Expenditures
Feb/March)
Totals $x,xxx,xxx
The cosmetics
you sell will cost
40% of your
selling price (i.e.
You will sell a
$4 item for $10)
– a VARIABLE
cost
You will pay spa
technicians 35% of
what you charge
customers for their
services.-
VARIABLE cost
You’ll do a big
advertising blitz
in January, then
plan to spend
$600 a month
after that. (This
is a FIXED cost)
5
Table 7: Financial Assumptions: This table contains the financial assumptions you will use for a
variety of calculations in your financial plan. You can use the assumptions given in the sample
table below, or replace the numbers with your own realistic assumptions. Don’t crowd the page
this table is on as you may need to add other financial assumptions as you go on.
(sample) Table 7: Financial Assumptions
Item Cost
Corporate income tax
rate
26%
Mortgage interest rate 6.9%
5-year term loan rate 9%
Line of credit interest
rate
10%
Minimum cash balance $5,000
% of sales on 30-day
credit
40%
% of inventory purchases
on 30 day credit
60%
Expenditures
Feb/March)
Totals $x,xxx,xxx
Table 8: Financing Sources and Cost of Capital To have a reasonable certainty of success, you
need to raise the total amount in your Startup Cost Summary before you can start the business.
In addition you need a line of credit to allow you to run an overdraft if required to replace any
inventory you sell during the first two months. Luckily, you already have a start.
o Remember your Aunt Bessie? The inheritance she left you is equal to 30% of the total
amount you need.
o The friends who may invest will be investing 10% of the total amount you need if you
produce a convincing financial plan
o You hope to get a 20-year mortgage for 60% of the value of the capital assets (omit the
mortgage if this amount is less than $100,000)
o You hope to get a 5-year term loan for the remainder of the money you need
o You also want to negotiate a line of credit allowing you to overdraw your bank account
up to the amount of your startup inventory
o Create formulas to calculate the values for the 2nd column of a table similar to the
sample below. Leave the other 4 columns blank.
o
Table 8: Financial Sources and Cost of Capital (Sample)
Investment Amount Proportion % Before Taxes % After Taxes
Investment in share capital
(majority shareholder)
Investment in share capital
(minority shareholder)
Mortgage
Term Loan
Line of Credit
Totals
That’s it for part 1!
C: Submission
Upload both the Word and the Excel document to D2L by the assignment due date.
6
Part 2: Financial Plan Exhibits – 15% of Financial Plan Mark
A: Additional Assumptions
You created Excel tables 1 through 8 in your financial plan proposal. Before you prepare your pro forma
financial statements, you may need to revise these tables in response to feedback you received from
your proposal. You may also want to revise some of your assumptions due to your own changing vision of
this business.
In addition, you will need to complete Table 8, and add two additional tables, as follows:
Table 9: Loan Amortization. Complete a loan amortization table for the first 12 months of your
business, for each loan or mortgage listed in table 8. (Do NOT include your line of credit loan).
Table 10: Sales Assumptions. In this table, you should prepare the sales assumptions on which you
will base your pro forma statements. These assumptions will vary depending on the nature of your
business. See the sample financial plan exhibits on D2L (under “Assignments”) for ideas.
B: Your Pro Forma Financial Exhibits
Overview
Based on information in your financial plan proposal, develop the following financial exhibits in the Excel
Workbook you created when doing the proposal. When preparing these statements, you will find that
your balance sheet will be easier to calculate if you plan that in your first month you will be preparing to
open your doors (training staff, organizing advertizing, getting inventory in place, etc), and sales will
begin in the second month.
Exhibit A: Sales and Expense Budget with one column for each month, and a total column for the
year.
Exhibit B: Cash Budget
o Use one column for each month and a total column for the year.
o After the “Cash Balance at End of Month” row, add a “Line of Credit” row, followed by a
final total of ” Balance after line of credit.” The line of credit should be calculated using an
IF statement, so that the final balance is never less than $5,000.
Exhibit C: Income Statement. The rows on this statement should be the same as those in the Sales
and Expense Budget, and the numbers should be equal to the total of each row in your Sales and
Expense Budget.
Exhibit D: Statement of Retained Earnings
Exhibit E: Balance Sheet.
o Link Cash to your ending balance before line of credit in your Cash Budget.
o Don’t forget to include accumulated amortization on capital assets
o Calculate the inventory that you will have left (startup inventory + purchases of inventory
(from Cash Budget) – Cost of Goods Sold (from income statement)
o Calculate the amount of supplies you have left (supplies purchased from cash flow statement
– supplies expense on income statement
o Calculate the accounts receivable still owing to you from last month’s sales
o List each category of capital asset your purchased, less it’s accumulated amortization (from
sales and expense budget)
o Calculate the accounts payable you owe (=cost of goods sold from last month)
o Get the balances of your loans from the ending balance on your loan amortization tables
o Common Shares will be equal to the amount you invested in the business, plus any other
investments of equity
o Retained Earnings will be equal to the balance from your retained earnings account
Exhibit F: Scenario Analysis with columns for Expected Scenario (this will be the same as your
income statement, so link to the numbers on your income statement), Pessimistic Scenario, and
Optimistic Scenario.
7
Hints for Developing a financial plan exhibits for a New Business
When creating your spreadsheet, start by reviewing your business objectives and your planning
assumptions. Do they make sense? Do you want to revise them?
Express your assumptions such that a number, for example, your membership fee, can be linked
wherever appropriate throughout the Exhibits. All numbers showing in exhibits should be calculated by
using formulas and links. This way you can easily change the numbers and see the effect on the
spreadsheet. Have all pages of the spreadsheet linked and all in one file. Re-name and add sheets as
needed.
Part 3: Financial Plan Completion – 70% of Financial Plan Mark
Suggested order for completion of exhibits
First develop all your sales, expense, income statement, balance sheet, and cash flow accounts
before you add any numbers. Think about your business objectives before you deal with any
numbers.
Look at the start up costs and assumptions you developed in tables 1 through 10. Be sure that you do
not have any numbers put into more than 1 location (always link when referring to an amount that
has previously been put into the workbook). Consider whether you wish to change any of your
assumptions, or add to them.
Next create a monthly sales and expense budget, with totals. Be sure that you do not type any
numbers into this, but use formulas to calculate amounts based on numbers and percentages in your
assumptions, and on other numbers in your sales and expense budget (you may need to add expense
assumptions as you think of new expenses).
You should now be able to create a forecasted first annual income statement and statement of
retained earnings.
Now create a monthly forecasted cash flow statement. Amortization will NOT be in this statement
as it does not involve cash, but all your financing money will be there in the first month, and your
purchases of capital assets and startup inventory and supplies will be included. Total the months in a
final column on the right so you can have a year total cash flow. Consider all the sources of cash
going into the business and all the out flow of cash from the business. In this type of business it
might be helpful to do some break-even analysis. How many memberships must you sell minimally
before you break even?
Once your Income Statement, Statement of Retained Earnings, and Cash Budget are complete, you
can then complete your year-end balance sheet
Finally, create the Scenario Analysis
C: Your Financial Plan Report (Word or PDF file)
When you write your plan, address each item in the “Written Report” section of the marking guide. Be
sure to paint a work-picture of your vision of the business, and be specific and clear about your planning
assumptions.
Note About Your Financing Request
This report is being prepared both as a planning document, and as a tool in selling your financing
request. While your business partners are very supportive, your plan will have to persuade them that
your business is a good investment for their money. Include a section in your report where you
specifically address their interests to persuade them that this is a good plan for them. In addition, you
will be going to the bank for some kind of debt financing, and this document is your main tool in
persuading the bank that you and your business are creditworthy.
Address the bank’s interests and put forward reasons as to why this is a safe project for them. Take care
to do a thorough discussion of the risks associated with this venture. Think about all the things which
could go wrong and how you will react. Your Part of your discussion should be a scenario analysis. Your
‘base case’ is your most likely scenario. Also present ‘pessimistic’ and ‘optimistic’ scenarios.
8
Marking Guide –Financial Planning Project – 100 marks
Project proposal (Criteria – See Financial Plan Proposal Section) (15 points)
Written Report (35 points)
Cover page
Table of contents, including list of Exhibits
Vision of what the business will be and where it is headed in general terms;
Business objectives – realistic and clear.
Planning assumptions – clear summary (see below)
Discussion of highlights of pro forma financial statements and cash budget
Discuss risk factors and how you can reduce or deal with risks,
Discussion of scenario analysis – most likely, pessimistic & optimistic; indicate assumptions used
in forecasting scenarios (does pessimistic assumption relate to risks?)
Financing request and arguments for financing as previously noted (cover the 5 C’s of credit)
Conclusion
Refer to exhibits throughout report
Clear writing and accurate grammar and spelling
Include in planning assumption discussion, as appropriate…
Land and building purchase or investment in renovations and leasehold improvements, and total
cost
Equipment to be purchased and total cost
Nature of services and products being sold
Financing assumptions
Amount of accounts receivable to be given and trade credit (accounts payable) received
Exhibits, clearly labeled and presented with Table/Exhibit number and title (25 points)
In addition, each sheet is examined for its appearance when printed, including readability of font size
and colours, and appropriate use of landscape setup when appropriate
Planning assumptions (including any changes needed in tables 1-7)
Start up financing needs and cost of capital
Loan amortization table for all loans, by month, for the first year
Sales Assumptions
Monthly sales and expense budgets, with a column for total for the year
Monthly cash budget with a separate column for startup costs, rows for line of credit, and a total
for the year
Income Statement and Statement of Retained Earnings
Balance Sheet
Scenario Analysis
Electronic Spreadsheet(s) (25 points)
Comprehensive;
Flexible and ‘linked’ throughout;
Include all important items;
Organized and logical;
Clear planning assumptions
Attractively, appropriately, and consistently formatted
Submission
This is a project where it might help to seek the advice of fellow students. However, each person (or
pair) is expected to create a unique budget and plan created by the student(s) submitting it.
Submit this project by uploading your Excel Workbook and Word or PDF document to the Financial Plan
D2L dropbox by the due date/time.
