Help - Daughter

[Newbie]

My daughter is taking an excel class - she needs help with this story problem
- can anyone assist us on getting her started.

Manager of furniture store is planning a sale. The store has 75 square feet
of space to display and stock merchandise. During the sale, each table cost
$5, and retails for $11, and takes up two square feet of space.

Each chair cost $4 and retails for $9 and takes up one square foot of space.

The maximum amount allocated for purchasing the tables and chairs for the
sale is $280.

The manager doesn't think she can sell more thean 40 chairs but the demand
for the tables is unlimited.

We need to help determine how many table and chairs the manager should
puchase in order to make the most profit.

Help - We hate story problems

 

[PCLIVE]

Sorry, this is not an Excel problem. It is a math problem. Once you have
the appropriate equations, if you need help putting them into Excel, then
that would be a question for this forum. Maybe someone else will think
different though.

I hate story problems too.

Regards,
Paul

 

[Dave F]

The first thing I would suggest is write all the variables in algebraic form
and then plug them into XL. Then create a table which iterates all the
various scenarios. Finally, take the maximum profit--there's your answer.

Does that help?

Dave

 

[Newbie]

Thanks for your responses - She forgot to show me a couple of things

She needs to create an Orders worksheet containing an income statement as
well as an estimate on the amount of space the tables and chairs will take up
in the display area.

Based on the information we know that the number of tables and chairs
ordered must be greater then 0 and less than 40, and the total cost is equal
to $280.And also the total space taken up must be less than or equal to 75
square feet.

But the question states to create an answer report detailing the parameters
of the problem and the optimal solution.....I'm lost.

 

[Brad]

There are some elegant ways to figure this out - but since you are new to
excel try this method out:

Start off with 40 chairs and determine how many tables you can have with the
restrictions you have been given. The repeat the process with 39 chairs....
until you finally get down to 1 chair.

The equations that you have to deal with are

Chairs <=40

1 * chairs + 2 * tables <= 75 or tables <= (75 - 1 * chairs ) / 2

4 * chairs + 5 * tables <= 280 or tables <= (280 - 4 * chairs) / 5

The number of tables that you can use is the minimum of the two answers from
the two equations above.

and you want the max of

9 * chairs + 11 * tables

Hence you need to perform the above calculation for each row of data -
starting with the forty chairs - all the way down to one chair.

 

[Dave F]

"Optimal solution" leads me to believe the question is asking you to use
Excel's Solver tool. Does it make any mention of that?

Dave

 

[Newbie]

Dave it does....It states test your knowledg of Solver to create a worksheet
finding a product mix for a furniture store that maximizes profits subject to
constraints of floor space.

 

[Dave F]

You should start your research here: http://www.solver.com/tutorial.htm

You also should make sure you have Solver installed on your excel
installation. To check if you do, go to Tools menu and see if Solver is an
option. If it is, it is installed. If not, you need to install it, likely
via the Office installation CDs (or if you're using a company-supplied
version of XL, via your company's network).

Dave

 

[Newbie]

Thanks Dave, I really appreciate your assistance.

I know nothing of "Solver" so this should help!

Daughters I thought I was done with Story Problems

 

[Newbie]

Hi Brad,

Thanks for your response. Dave suggested using the Solver and according to
the assignment I think that is what we need to do. Even though I know nothing
about "Solver"

 

[Don Guillett]

We kinda have rules around here about doing homework for students. Maybe you
should also.

 

[Allllen]

Doing this on solver gives me a solution that says 40 chairs and 17½ tables
would be the best.

How can I set solver to work to integers?

 

[Dave F]

You have to add the constraint "cell reference = int" where "int" is integer.

Dave

 

[Newbie]

I wasn't asking for someone to do homework I was asking for someone to lead
me in the right direction, as Brad and Dave did.

 

[Dana DeLouis]

...how many table and chairs the manager should

and you want the max of
9 * chairs + 11 * tables

Just two cents. This is how one would set up Solver.
However, this would maximize sales.
Perhaps for Profit, consider maximizing:
(11-5) Tables + (9-4) Chairs.
or
6 Tables + 5 Chairs.

For the op, this doesn't change the Integer solutions, but if any of the
numbers were different, it could change the dynamics of the solution. ( ie
$303 "Profit", with 18 Tables & 39 Chairs.)

 

[Roger Govier]

Hi

Even without Solver (or Excel) this particular example presents a fairly
simple problem.
The most limiting resource is space - 75 sq feet.
Chairs generate $5 each, with 1 sq ft space hence 5$/sq ft
Tables generate $6 each, but require 2 sq ft hence 3$/sq ft.

First try maximising the item that returns the greatest value to the
most limiting resource.
This is chairs (stated to be 40 max) leaving 35 sq ft which would mean
that 17 tables could be purchased (and sold) which would generate $302
total profit.
This would only use 74 sq ft, so there is 1 sq ft slack.
Sacrificing 1 chair and thereby dropping profit by $5, would release
another 1sq ft and enable 1 more table with an increase of $6, lifting
total profit to $303.
So optimal solution is 39 chairs and 17 tables.

 

[Roger Govier]

So optimal solution is 39 chairs and 17 tables
That should have read
So optimal solution is 39 chairs and 18 tables

 

[Allllen]

Yes but it was a good question to learn from

 

[Tushar Mehta]

I realize everyone is pushing towards using Solver, but this is a problem
that requires more logic than the mechanical use of a black-box tool.

Each table nets a profit of $6 and uses 2 sq. ft. of space. Essentially,
the store makes $3/sq.ft.

Each chair nets $5/sq.ft.

Clearly, you want to focus on chairs. The clincher is that each chair costs
less than a table ($4 vs. $5).

So, the store should sell as many chairs as it can. From the space
limitation that would be 75. From the budget limitation that would be 70.
From the estimated demand it would be 40.

Taking the min. of the three, the answer is #chairs = 40.

That leaves the store with a budget of $280 - $4*40 = $120 and 75-40 = 35
sq.ft. of space for tables.

In 35 sq.ft. one can fit 17-1/2 or 17 tables. With $120, one can buy 120/5
= 24 tables. Take the min. of the two and the answer is 17 tables.

Bottom line: 40 chairs and 17 tables.

No need for Solver, XL, or complicated algebra. Just basic logic and simple
arithmetic.

Of course, not all problems are this easy to solve. Most *do* require
Solver. But, in this case it appears the teacher created the problem "by
hand." Bad move on her/his part.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Tushar Mehta]

Correction: I don't see my first post yet, but I should have reviewed it
before hitting 'send'.

The 1 sq. ft. that is wasted was bugging me but I ignored the feeling.

A slightly improved solution would be to take away 1 chair (lose $5) and add
1 table (gain $6). That would use up the last sq. ft. of space, still be
under the budget, and increase profit by $1.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions