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#52988 | Written in 18-Jul-2015Price : $45