- Create 3 years of the following financial statements required for a business plan.
a) Monthly Cash Budgets
b) Year-End Income Statements
c) Year-End Balance Sheets
Spreadsheet Assignment #2
Restaurant Business Startup
Due Monday, April 29th (via e-mail)
Assignment:
1. Prepare three years of monthly cash budgets, yearly income statements, and yearly balance
sheets for the restaurant start-up business described below.
2. You may work in groups of two or less.
3. Please e-mail me one spreadsheet for your group. The spreadsheet should include all group
members’ last names at the top of the first sheet and in the file name. This should be e-mailed
to me by the start of class on April 29th.
Notes:
1. This is a start-up restaurant business.
2. It is cash-based (i.e., there are no accounts receivable or accounts payable)
3. Assume it is a sole proprietorship, so there are no corporate taxes. (the income of the business
is taxed as personal income to the business owner).
Sales Forecast 2013
Month
Sales
January
$22,814
February
24,399
March
25,984
April
27,569
May
30,769
June
32,324
July
35,494
August
38,664
September 38,664
October
32,324
November 29,154
December 25,984
Assume sales grow at 10% annually in 2014 and 2015. Maintain the seasonal pattern of sales.
Salaries and Wages
Open 15 hours per day x 364 days = 5,460 hours
Part-time help will work 5 hours per day x 364 = 1,820 hours
1 Wait person
5,460 x $2.19=$11,957.40
1 Cook
5,460 x $6.00=$32,760.00
1 Dishwasher/busboy
5,460 x $4.25=$23,205.00
Part-time wait person
1,820 x $2.19=$3,985.80
Part-time cook
1,820 x $6.00=$10,920.00
Manager Salary (Owner)
$10,000.00
Total Annual Salaries and Wages
$92,828.20
Equity Contribution (these are the initial assets and owner’s equity)
Land
$45,000
Equipment
5,000
Liquor License
2,000
Cash
5,000
Total
$57,000
Loan Information
Borrow $175,000, 20 years @ 11.25% interest. Loan will be disbursed on January 1 and payments will
be made starting in January of the first year. The loan proceeds will be used as shown below. These
items are all purchased in January once the loan is received.
Buildings
2,800 sq. ft. Building @ $47.50/sq. ft.
Pavement of parking area
Block fence on back of property
Septic System
Water Meter
Building Total
Restaurant Equipment
Initial Inventory
Misc. Start-up expenses
Total
$133,000
16,500
2,500
6,000
2,000
$160,000
$5,000
5, 322
4, 678
$175,000
Expenses:
Cost of Goods Sold=38% of sales
Employee Related Expenses = 15% of salaries and wages
Direct Operating Expenses = 3.6% of sales
Utilities and Telephone = 3.4% of sales
Property Insurance = 1.5% of sales
Administrative Expenses = 1.7% of sales
Repairs and Maintenance = 1.0% of sales
Advertising and Promotion = 2.0% of sales
Property and Related Taxes = 2.2% of sales; calculated quarterly and paid in the month following
each quarter (in January, April, July, and October).
Depreciation Schedule
Building $160,000 over 15 years, straight-line
Equipment $10,000 over 7 years, straight-line