How best to build this spreadsheet

  • Thread starter Thread starter Alan Silver
  • Start date Start date
A

Alan Silver

Hello,

I teach teenage boys computer basics and am currently going over Excel
with them. I came across something which I am sure is inefficient the
way I explained it. I would like to know the best way to build a
spreadsheet for the following scenario :-

Suppose you run a sandwich business and wanted to do a "what if"
analysis. This would be to see what effect varying the cost of various
components (ie bits that are used to make the sandwiches) would have on
the profits.

The way I set it up was to have the components listed at the top, with
their prices. So, column A (first eight rows or so) would contain the
names of various bits needed, such as bread, butter, cheese, meat,
cucumber, etc. Column B would contain the prices for each of these (per
sandwich).

Below that, I listed various types of sandwich, eg cheese and tomato,
one per row. On the row, I had a column for each component, and put a 1
in any column where the component was used in the sandwich. Thus the
cheese and tomato sandwich would have a 1 in the bread, butter, cheese
and tomato columns, and nothing in the other columns. The cost was
worked out by creating a formula that multiplied the number in the
appropriate column by the cost in the list at the top. It was done this
way so you could have a double cheese sandwich by including a 2 for the
bread and for the cheese.

This all worked fine, but seemed very inefficient. For one thing, the
formula for the cost was very long and could easily have been entered
incorrectly.
More to the point, it was very inflexible. If I had wanted to add
another component (say I started selling cream cheese sandwiches as
well), the whole spreadsheet would need to be changed. I would need an
extra row in the components list, an extra column in the part that had
the types of sandwiches, and I would need to alter the formula to
calculate the cost.

Does anyone have any better suggestions as to how I should have done
this ? I'm sure there is some simple way, but I couldn't think of it.

TIA

Alan

P.S. I hope my explanation is clear. If you want a copy of the
spreadsheet I did, please e-mail me at :- google at alansilver dot co
dot uk
 
Hi

If you email me your spreadsheet I'll have a look at what you've done and if
it can be improved.
 
I think I'd use multiple sheets.

The price/cost sheet would contain the ingredients and the unit price and unit
cost.

The other sheet would contain a list of possible ingredients (say column A).
Column B would contain the quantity required.
column C would contain the unit cost
column D would contain the unit price
column E would contain the extended cost
column F would contain the extened price

To retrieve the unit cost from the first sheet:

=IF(ISERROR(VLOOKUP(A1,Sheet1!$A:$C,2,FALSE)),"missing",
VLOOKUP(A1,Sheet1!$A:$C,2,FALSE))

And to retrieve the unit price, bring back column C of sheet1:

=IF(ISERROR(VLOOKUP(A1,Sheet1!$A:$C,3,FALSE)),"missing",
VLOOKUP(A1,Sheet1!$A:$C,3,FALSE))


To get the extended cost/price, just multiply that cost/price by the quantity
column.

Add a couple of subtotals (in row 1, so they're always visible).

(and add a description field (if the ingredients aren't enough).)

Apply Data|filter|autofilter and see if that works ok.
 
Dave Peterson <[email protected]> said:
I think I'd use multiple sheets.
Click to expand...
<snip>

Thanx for the suggestion. It looks a little complex for my boys !! They
are not particularly computer literate to start with !! I might play
around with it and see how it works in practice.

Someone else (in a private e-mail) suggested the SUMPRODUCT function,
which is very good as it contains the flexibility to allow extra
components to be added without too much trouble.

I'll look into both and see how they compare.

Thanx again for the reply.
 
I think if I were to choose between teaching =vlookup() and =sumproduct(), I
think I'd go with =vlookup() first.

If you think you need help with the =vlookup() function's syntax, you could
review David McRitchie's notes at:

http://www.mvps.org/dmcritchie/excel/vlookup.htm

It's a very powerful way of merging two pieces of information based on a unique
key.
 
Dave Peterson <[email protected]> said:
I think if I were to choose between teaching =vlookup() and =sumproduct(), I
think I'd go with =vlookup() first.
Click to expand...

;-) You've met my boys eh ? Their skills in maths are, shall we say
challenged !!
If you think you need help with the =vlookup() function's syntax, you could
review David McRitchie's notes at:

http://www.mvps.org/dmcritchie/excel/vlookup.htm

It's a very powerful way of merging two pieces of information based on a unique
key.
Click to expand...

Thanx for that, looks like a useful source of info.
 
Back
Top