Spreadsheet Help

[aquamoon94]

Hi, I was wondering if anyone could help me out with an Excel problem. I am not sure how the spreadsheet is supposed to look.

Scenario
A client is planning to open a restaurant and is trying to choose a location. There are 3 possibilities – Chester, Manchester and Liverpool. It is expected to start trading at the beginning of 2015, and needs to plan for 3 years. The business will start with a loan of £50,000 and cash on hand of £20,000, but these figures may change.Scenario
A client is planning to open a restaurant and is trying to choose a location. There are 3 possibilities – Chester, Manchester and Liverpool. It is expected to start trading at the beginning of 2015, and needs to plan for 3 years. The business will start with a loan of £50,000 and cash on hand of £20,000, but these figures may change.

The 3 locations have slightly different market potential at present and different growth rates.

Market Potential – it is expected that the restaurant will pick up 12% of the population. The population predictions are as follows:
[TABLE="width: 366"]
<tbody>[TR]
[TD]Location[/TD]
[TD]2015[/TD]
[TD]2016[/TD]
[TD]2017[/TD]
[/TR]
[TR]
[TD]Chester[/TD]
[TD]125,500[/TD]
[TD]128,750[/TD]
[TD]131,750[/TD]
[/TR]
[TR]
[TD]Manchester[/TD]
[TD]110,000[/TD]
[TD]116,500[/TD]
[TD]119,750[/TD]
[/TR]
[TR]
[TD]Liverpool[/TD]
[TD]110,000[/TD]
[TD]112,250[/TD]
[TD]118,850[/TD]
[/TR]
</tbody>[/TABLE]


Labour costs - the locations have different labour characteristics so it is expected that rates will rise by different percentages.
[TABLE="width: 365"]
<tbody>[TR]
[TD]Location[/TD]
[TD]Current[/TD]
[TD]2015[/TD]
[TD]2016[/TD]
[TD]2017[/TD]
[/TR]
[TR]
[TD]Chester[/TD]
[TD]£16,000[/TD]
[TD] 2%[/TD]
[TD] 3%[/TD]
[TD] 4%[/TD]
[/TR]
[TR]
[TD]Manchester[/TD]
[TD]£15,500[/TD]
[TD] 1% [/TD]
[TD] 2%[/TD]
[TD] 2%[/TD]
[/TR]
[TR]
[TD]Liverpool[/TD]
[TD]£12,000[/TD]
[TD]3%[/TD]
[TD] 1%[/TD]
[TD] 1%[/TD]
[/TR]
</tbody>[/TABLE]



Other factors that influence the financial status of the restaurant do not vary between locations:

	2015	2016	2017
Average selling price per meal	£12.00	£12.50	£13.00
Average ingredient cost per meal	£5.00	£5.50	£6.00
Fixed Costs	£12,000	£13,000	£14,000
Tax Rate	3%	4%	5%
Interest Rate	1%	1%	1%

<tbody>
</tbody>

For every 4,500 meals the restaurant needs 2 staff. To maintain quality of service there will not be part-time staff nor overtime.

So, for example, if the restaurant sells 14,333 meals it will need 8 staff (6 staff have a capacity of 13,500 meals, 8 have a capacity of 18,000 meals)


For each year you will need to calculate the operating surplus:
Revenue from meals (= number of meals x selling price per meal)
less ingredient costs (= number of meals x ingredient cost per meal)
less wage costs (= number of staff x wage rate)
less fixed costs

I was wondering if anyone could give me a few pointers on how to do the calculations and how the spreadsheet is supposed to look like.

 

[igold]

Looks like homework to me. Tell us how much of it you have done already and where you hung up...

 

[aquamoon94]

I've only done the formatting so far, like placing them under their respective sheets (i.e. Market Potential, Labor Costs) my only problem is the calculations.

 

[igold]

The calculations are pretty straightforward and they are given to you in the problem. Which calculation is giving you issues.

I have done Chester. Formatting is something else- were you given any guidelines on what the finished product should look like. I think if you get the data on a worksheet and do the calculations, you can then format the sheet into something presentable and readable or formatted as dictated by the assignment.

 

[aquamoon94]

Nope. No format. THat is what has me confused

 

[svendiamond]

Nope. No format. THat is what has me confused

You are not helping yourself here. What specific problem do you have?