Need assistance with a problem that uses excel to be solved!

[mattquinones]

4. You are in charge of a small materials company. You need to produce an alloy, consisting of two different materials, A and B. The alloy has 75 units of material A and 25 units of material B. You can purchase four different ores; their characteristics and prices are shown in the following table:

Supplier 1 2 3 4
Composition of A (Units) 55 40 20 85
Composition of B (Units) 40 55 70 10
Price $3.75 $4.05 $5.00 $3.15

Determine the percentage of each suppliers material you will purchase in order to minimize the total cost of the order, keeping in mind the restraints that the A must compose 75 units of the alloy, and B must compose 25 units of the alloy.

Some pointers given were:
-remember there are 4 suppliers
-use solver
-and guess percentages for each supplier

Pleas if someone can point me in the right direction that would be awesome.. Thank you!

[MrShorty]

As this is obviously homework, I don't want to do too much and take away the educational experience from you. As a nudge in the right direction, I find that most of the time when I'm developing a solution to a problem like this (even back in high school algebra when we would solve these by pencil and paper), the usual best first place to start was figuring out what my "objective function" should be. The objective function is the function that you are trying to find the max, min, or root of. So, what will your objective function for a problem like this look like?

[TMS]

Hi, and welcome to the forum.

Unfortunately, we don't do homework. And any hints we might be able to offer have already been given, the key one being, use Solver. Research that ... I'm no Solver expert, so can't offer help. But, if you Google Solver, I'm sure you'll find lots of examples and videos.

Regards, TMS

[mattquinones]

Yes it is, and I was told to calculate the total number of units A based off guessed percentages based off of guessed percentages. However I am just not sure how to figure out the formulas. I am in contact with the professor through email but I just cant understand how he is putting it.

[MrShorty]

So, he is suggesting that you start by figuring out how to calculate units of A, but you can't figure out the formulas. How would you calculate units of A (at any given percentages) purchased in algebraic terms (let's not get into Excel, yet)?

[mattquinones]

So since there has to be 75 units of A and the first supplier has 55 units of A, would I divide 55 by 75? which comes out to 73%. If that is correct, can you explain to me what 73% defines?

[MrShorty]

If I understand correctly, that 73% means, "If I buy all (100%) of 1's A, then I will only have 73% of the total A required. So I will need to look at the other suppliers and decide how much to buy from each of them to obtain the other 20 units. I could buy all of 3's A. But 3 wants more $ than the other three suppliers. Maybe it would be better to buy half (50%) of 2's supply of A or 24% of 4's supply of A to obtain the other 20 units I need."

Does that make sense?

[mattquinones]

Alright this is starting to make more sense now. So my problem is creating the formula that will be my set cell in the solver. I am not sure how to incorporate both composition A and B while trying to find out which combination will be the least costly.

[MrShorty]

As I said at the beginning, I often find that my first task when setting up a problem like this is figuring out the objective function -- the function that I need to maximize, minimize, or find the root of. At this point, I am usually not too worried about what the min/max/root actually is or what effect any other constraints might have on the solution -- just trying to define what that function is. So, what do you think will be your objective function?

[mattquinones]

Is this when i used the SUMPRODUCT() function with the A's and B's?

[mattquinones]

or would it be (guessed percentage*supplier 1 (A's)+(guessed percentage*supplier 1 (B's) and so on?

[MrShorty]

post #11 is a sumproduct function. What does that function translate into as your objective function?

[mattquinones]

so SUMPRODUCT(percentage,Supplier1 A)?

[MrShorty]

And what quantity will that formula give you?

[mattquinones]

How many units of A will be there with the percentage that i have guessed? (out of 55?)

[MrShorty]

Correct. Then, if you sum that across all suppliers, you will get total units of A, right?

[mattquinones]

correct, so i do the sum product of each % and A units, then add all of the A's up. Do I do the same for the B's?

[MrShorty]

this might be a good time to go back to the original problem statement. There are a few different quantities that will be important to solving this problem. The important skill here is to pick out which quantities do we need to be able to calculate to get at the final solution. From the problem statement, does it look to you like you will need to be able to calculate total units of A? total units of B?

[arlu1201]

mattquinones,

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)