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.

Our guarantees

Study Acers provides students with tutoring and help them save time, and excel in their courses. Students LOVE us!No matter what kind of essay paper you need, it is simple and secure to hire an essay writer for a price you can afford at StudyAcers. Save more time for yourself. Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

Privacy policy

Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Fair-cooperation guarantee

By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Get your paper done by an expert