Question;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.
Paper#41482 | Written in 18-Jul-2015Price : $27