How best to build this spreadsheet

[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

 

[Andy B]

Hi

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

 

[Freemini]

There are a number of way to do this, and it would depend on whether yo
were trying to cover aspects such as Lookup tables in your example.

One way you can achieve it is to have the components listed from cel
B1 to cell J1
beneath each component enter the cost of that item.

In column A list you sandwiches (starting at A3) and mark th
ingredients with a 1, 2 etc in cell B3 to J3 in cell K3 enter th
formula :
=SUMPRODUCT(B$2:J$2,B3:J3)
and copy this down to K4 etc.

To add a new component siply insert a column between B and J and th
formula will adjust automatically. (if you insert after column J yo
will have to amend the formula and copy down)

hth

Mik

 

[Dave Peterson]

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.

 

[Alan Silver]

I think I'd use multiple sheets.

<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.

 

[Dave Peterson]

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.

 

[Alan Silver]

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

;-) 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.

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

 

[Alan Silver]

There are a number of way to do this, and it would depend on whether
you were trying to cover aspects such as Lookup tables in your example.

I'm trying to keep it as simple as possible !! These boys get easily
lost when you try and discuss maths with them !!

Thanx for the tip. I think I'll stick with my original idea for the
moment as explaining SUMPRODUCT to them might be too much of a challenge
!!

Thanx