Spreadsheet Project Westside Convenience Store

Spreadsheet Project
Westside Convenience Store

Problem: Designing a comprehensive spreadsheet model

Austin Industries is a medium sized
business located in Northwest Atlanta.
Charles Austin started the company over ten years ago as a side business
while working as an auditor for an accounting firm. Through his extensive audit and tax work, he
became familiar with the financial aspects of hundreds of different small to
medium size company’s. About ten years
ago he decided to purchase the company of one of his clients. The company was experiencing financial
difficulty and Charles felt sure he could turn it around. He was also able to
negotiate good terms for his purchase as his client was anxious to terminate
his business.

Within two years Mr. Austin
was turning a 35% profit. Over the next
10 years, Mr. Austin has bought and managed over thirty other companies and has
enjoyed continuous growth and profit. Austin Industries now employs over 150
full time people, primarily in the financial and administrative area including
the managers he hires to operate his newly purchased company’s.

Mr. Austin does extensive
research on companies he is considering buying and has a small staff of
assistants who now do most of the information gathering and analysis. Mr. Austin’s latest proposal is a convenience
store located on the west side of
town. Even though the store seems
to be doing well, the owner wants to sell because he is financially
over-extended and has too much personal debt.
Mr. Austin feels it is in a good location but has been poorly
managed. The owner could not even
provide Mr. Austin with a set of
financial. All he could manage was a
rather unorganized letter to Mr. Austin listing by item all of the stores
revenues and costs.

Mr. Austin has instructed
one of his staff to prepare a 12 month income statement from the information
provided on the letter. All the
information is there. Assume you are Mr.
Austin’s employee and have been given this assignment. Using your knowledge of Excel, your task is
to design and prepare this income statement. The information is on the attached
page.

The income statement should be monthly – May 2012
through April 2013 – and a total column for annual amounts.

Many of the items on the statement are a function of
gallons of gasoline sold, therefore, use variable factors that can be changed
without having to rewrite the formulas.

Estimated
gallons of gasoline sold from May through April are as follows:

REG UNL
170000,147000,156000,200000,200000,200000,151000,151000,158000,166000,150000,142000

PREM UNL

29000,26000,22000,28000,28000,28000,22000,22000,23000,24000,22000,21000

DIESEL

10000,9000,10900,14000,14000,14000,10500,10500,11000,11500,10400,9000

Pricing and
costs:

Price
Cost
REG UNL 2.50/Gal 89% of price
PREM UNL 2.89/GAL 83% of price
DIESEL
2.10/Gal 96% of price

STORE SALES: (Based on gallons of
gasoline sold)
Cigarettes $0.06
per Gallon of gas sold and cost is 82%
of sales
Drinks $0.03
per Gallon of gas sold and cost is 80% of sales
Groceries $0.18 per
Gallon of gas sold and cost is 60% of sales
Beer/Wine $0.03
per Gallon of gas sold and cost is 79% of sales
Deli $0.04
per Gallon of gas sold and cost is 40% of sales

OPERATING
EXPENSES

PAYROLL
EXPENSES ($ per month)
Mgmt salary 3600
Wages 6500
Payroll Taxes 12%
of salary and wages
Group Insurance
100
Worker’s Comp
180

GENERAL,
SELLING and ADMINISTRATIVE ($ per month)
Supplies and Uniforms 320
Advertising(help wanted) 275
Polygraph expense 260
Insurance 288
Company gas 120
Credit card processing 320
Advertising and promotion 4100
Taxes and licenses 200

FACILITY
EXPENSES: ($ per month)
Refuse removal 270
Maintenance 350
Utilities 2100
Telephone 180
Monthly services 100
Other 300

Your bottom line should be OPERATING INCOME BEFORE
TAX

The flow of your statement should include the
following sections:

1. Units of gasoline sold by type and totals.
2. Sales $ of gasoline sold by type and totals.
3. Sales $ of store items sold by type and total
4. Cost of merchandise sold ( separate gas and store
items with subtotals and grand total for total cost of all.
5. Gross profit by product type and total.
6. Operating expenses which should be segregated in
to the 3 sections as listed above with sub totals and a grand total.
7. Operating income-bottom line.