analyze

profilezeel906
FIN5823FINALPROJECTWEEK81.xls
Home>Business & Finance homework help>Accounting homework help>analyze

Overview

This spreadsheet provides two sample financial models. Entrepreneurs use financial models to project revenue and expenses to ultimately determine the best paths to profitability. More complex models factor in marketing expenses to project rate-of-growth and scale.

WEEK 8 FINAL PROJECT

SAMPLE FINANCIAL MODEL
Website Consultancy
CORE BUSINESS ASSUMPTIONS A website consultancy generates profits by charging 2-3x what it costs to produce a website. For the sake of this model, consider that websites take about three months to produce (which is over-simplified for the sake of the model -- all websites and clients are different). Notice when you reduce from four projects per quarter to three (cell B7), profits become dangerously low, so this model tells you how many website contracts per quarter you need to sell to create a healthy consulting business. Businesses are never as clean as their models - some months you'll close three new contracts at once and then you'll go many months with none. This model illustrates one possible scenario around expenses, cost, and cash flow, reminding you to save funds on good months so you can cover expenses in months when there are no checks coming in.
Average website sale price 15,000
Duration of each website build (months) 3
New project contracts sold per quarter 4
Project Cost Assumptions
Project manager internal bill rate (contractor) $75
Project manager average hours per website 24
Designer internal bill rate (contractor) $50
Design hours per website 20
Programmer internal bill rate (contractor) $75
Programmer hours per website 50
Cost to produce a website $6,550
REVENUE Jan Feb Mar April May June July Aug Sept Oct Nov Dec TOTAL YEAR 1
New contracts signed 4 0 0 4 0 0 4 0 0 4 0 0
TOTAL REVENUE 20,000 20,000 20,000 20,000 20,000 20,000 20,000 20,000 20,000 20,000 20,000 20,000 240,000
EXPENSES
Programmer 5,000 5,000 5,000 5,000 5,000 5,000 5,000 5,000 5,000 5,000 5,000 5,000 60,000
Designer 1,333 1,333 1,333 1,333 1,333 1,333 1,333 1,333 1,333 1,333 1,333 1,333 16,000
Project manager 2,400 2,400 2,400 2,400 2,400 2,400 2,400 2,400 2,400 2,400 2,400 2,400 28,800
Bookkeeper 300 300 300 300 300 300 300 300 300 300 300 300 3,600
Legal 2,000 0 0 0 0 0 0 0 0 0 0 0 2,000
Accountant 1,000 0 0 1,000 0 0 0 0 0 1,000 0 0 3,000
Insurance (workman's comp, renter's, etc.) 500 500 500 500 500 500 500 500 500 500 500 500 6,000
Misc. 1,000 1,000 1,000 1,000 1,000 1,000 1,000 1,000 1,000 1,000 1,000 1,000 12,000
Owner income 6,000 6,000 6,000 6,000 6,000 6,000 6,000 6,000 6,000 6,000 6,000 6,000 72,000
TOTAL EXPENSES 19,533 16,533 16,533 17,533 16,533 16,533 16,533 16,533 16,533 17,533 16,533 16,533 203,400
OPERATING CASH FLOW 467 3,467 3,467 2,467 3,467 3,467 3,467 3,467 3,467 2,467 3,467 3,467 36,600

WEEK 4 Neighborhood Cafe

SAMPLE FINANCIAL MODEL
Neighborhood café
CORE BUSINESS ASSUMPTIONS
% Growth per month 0.05% In some business models, like restaurants, revenue determines expenses. For example, a restaurant owner may find that labor and food costs should each be 30% of total revenue for the business model to work. As you run your business over time, you'll see certain patterns and limitations (e.g. cafes only have a certain number of seats) -- patterns in revenue to determine expenses.
Average check per person $16
Customers served per day 125
Days per month 31
Food costs as % of total revenue 30%
Labor as % of total revenue 30%
REVENUE Jan Feb Mar April May June July Aug Sept Oct Nov Dec TOTAL YEAR 1
Customers per month 3,875 3,877 3,879 3,881 3,883 3,885 3,887 3,889 3,891 3,892 3,894 3,896 46,628
TOTAL REVENUE 62,000 62,031 62,062 62,093 62,124 62,155 62,186 62,217 62,248 62,280 62,311 62,342 746,049
EXPENSES
Labor costs 18,600 18,609 18,619 18,628 18,637 18,647 18,656 18,665 18,675 18,684 18,693 18,703 223,815
Food costs 18,600 18,609 18,619 18,628 18,637 18,647 18,656 18,665 18,675 18,684 18,693 18,703 223,815
Rent & utilities 5,000 5,000 5,000 5,000 5,000 5,000 5,000 5,000 5,000 5,000 5,000 5,000 60,000
Bookkeeper 300 300 300 300 300 300 300 300 300 300 300 300 3,600
Legal 2,000 1,000 1,000 1,000 5,000
Accountant 1,000 1,000 1,000 1,000 4,000
Insurance (workman's comp, renter's, etc) 500 500 500 500 500 500 500 500 500 500 500 500 6,000
Marketing/website maintenance 1,000 1,000 1,000 1,000 1,000 1,000 1,000 1,000 1,000 1,000 1,000 1,000 12,000
Misc. 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 2,000 24,000
Owner income 8,000 8,000 8,000 8,000 8,000 8,000 8,000 8,000 8,000 8,000 8,000 8,000 96,000
TOTAL EXPENSES 57,000 54,019 54,037 56,056 54,074 54,093 56,112 54,130 54,149 56,168 54,186 54,205 658,230
OPERATING CASH FLOW 5,000 8,012 8,025 6,037 8,050 8,062 6,074 8,087 8,099 6,112 8,124 8,137 87,820