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:

Location	2015	2016	2017
Chester	125,500	128,750	131,750
Manchester	110,000	116,500	119,750
Liverpool	110,000	112,250	118,850

<tbody>
</tbody>

Labour costs - the locations have different labour characteristics so it is expected that rates will rise by different percentages.

Location	Current	2015	2016	2017
Chester	£16,000	2%	3%	4%
Manchester	£15,500	1%	2%	2%
Liverpool	£12,000	3%	1%	1%

<tbody>
</tbody>

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?