Spreadsheet Assignment #1

The Restaurant Business

Assignment:

1. 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

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 taxes as personal income to the business owner).

Sales Forecast 1996

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 1997 and 1998. Maintain 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 Salaries and Wages	$92,828.20

Equity Contribution

Land	$45,000
Equipment	5,000
Liquor License	2,000
Cash	5,000
Total	$57,000

Uses of Loan Proceeds Borrow $175,000, 20 years @ 11.25% interest

Buildings
2,800 sq. ft. Building @ $47.50/sq. ft.	$133,000
Pavement of parking area	16,500
Block fence on back of property	2,500
Septic System	6,000
Water Meter	2,000
Building Total	$160,000
Restaurant Equipment	$5,000
Initial Inventory	5, 322
Misc. Start-up expenses	4, 678
Total	$175,000

Other Assumptions

• Cost of Goods Sold=38% of sales
• Minimum cash balance= $15,000
• Property and Related Taxes paid quarterly in January, April, July, and October and with a one quarter (three months) lag in payment.

Expenses:

• 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

Depreciation Schedule

• Building $160,000 over 15 years, straight-line
• Equipment $10,000 over 7 years, straight-line