Simulation in Excel - Best Fit

[shanck]

<style type="text/css">p.p1 {margin: 0.0px 0.0px 8.0px 0.0px; font: 11.0px 'Trebuchet MS'; color: #000000; -webkit-text-stroke: #000000}p.p2 {margin: 0.0px 0.0px 8.0px 72.0px; text-indent: 36.0px; font: 11.0px 'Trebuchet MS'; color: #000000; -webkit-text-stroke: #000000}span.s1 {font-kerning: none}span.Apple-tab-span {white-spacere}</style>Can someone please help us with the below problem. I do not expect to solve this completely( Of course, that would be highly appreciated). I am struggling hard to find out an approach to go about for this problem.
------------------------------------------------------
XYZ Corporation is a large manufacturer of batteries. The company recently received a $920,000 order for different quantities of three types of batteries. Manufacturing each batteries requires certain amount of time to manufacture battery and certain amount of time to test the battery. Table below summarizes the requirements for the three models of batteries.
Model 1 Model 2 Model 3
Number ordered: 3,000 2,000 900
Manuf./Unit (hours): 2 1.5 3
Testing/Unit (hours): 1 2 1
Unfortunately, XYZ Corporation doesn’t have enough manufacturing and testing capacity to fill the order by its due date. The company has only 10,000 hours of manufacturing capacity and 5,000 hours of testing capacity available to devote to this order.
However, the company can subcontract any portion of the order. The unit costs of producing each model in-house and buying the finished products from a subcontractor are summarized below:
Model 1 Model 2 Model 3
Cost to make: $50 $83 $130
Cost to buy: $60 $97 $145
Create a simulation in Excel to model XYZ Corporation’s objective function and then varying the number of units of each model to manufacture such that resource constraints are met( total availability of manufacturing time and testing time), attempt to determine the number of batteries of each type to make and the number of batteries of each type to buy in order to fill the customer order at the least possible cost.

 

[Eric W]

This looks like some kind of class assignment. Is this an Excel class, a VBA class, a math class, or some kind of financial analysis class? What tools and/or skills do you have available? I assume you know something about Excel, which is why you're here. Do you know formulas, Solver, VBA?

The general consensus here is that we might give some direction, but we think that giving you an answer doesn't really help you. So here are a few thoughts.

You basically have a problem that has 3 variables. The number of Model 1 batteries to make, the number of Model 2 batteries to make, and the number of Model 3 batteries to make. You have constraints on time which must be met, and a potential cost you want to minimize. So let's say you have the number of Model 1 batteries in a spreadsheet cell A2. You have available manufacturing hours in F2, available testing hours in G2, and cost in H2. So if A2 is 1, then F2 must go down by 2 hours, G2 must go down by 1 hour, and H2 must go up by $50.

You also know that if you only make 1 Model 1 battery, then you must subcontract out 2999 of them at $60 each, so your cost also must go up by 2999*$60. F2, G2, and H2 can all be generated by basic Excel formulas. Once you have them working for Model 1, then put the number of Model 2 batteries in B2, and Model 3 batteries in C2. Then update your formulas to include those. You now have a basic model (simulation) of the situation. You should be able to change the values in A2:C2 and instantly see what the cost is, and how much time it takes to create those batteries.

At this point, you want to figure out how to minimize the cost. Two approaches. First, use Solver. Minimize H2, while adding the constraints that F2 can't exceed 10000 and G2 can't exceed 5000. You must also add constraints that A2:C2 are in the right ranges and are integers. Next approach, write a VBA macro that changes the values of A2:C2 according to the number ordered, makes sure that all constraints are met, and keeps the best case.

So the first step is to build your model in a spreadsheet. Good luck!

 

[joeu2004]

You have wisely ask only for guidance, not a turnkey solution. Since this is an assignment of some kind, it would be unethical for us to provide a turnkey solution, as tempting as that might be. (Hey, it's fun!)

First, it might be noted that this might be a "linear programming" problem. With the proper LP model (set of formulas to minimize and maximize), it might be possible to use the Solver Simplex LP method to find a solution. (Then again, maybe not. I haven't looked at the details.)

(PS.... Just saw Eric's response, and on second thought, I think his suggestion of simply using the "min value" option and GRG Nonlinear method in Solver should be sufficient, since you have only one variable to minimize. Be that as it may....)

However, the assignment specifically asks that you to use simulation. If you google "excel simulation" without quotes, possibly adding the words "monte carlo", you might find some useful examples.

Generally, one approach would be as follows.

1. Use RANDBETWEEN to choose zero or more of each model to manufacture in-house, subject to the constraints of 10,000 total manufacturing hours, 5,000 total testing hours, and the number ordered of each model, namely 3000, 2000 and 900. Call those random numbers M1, M2 and M3.

2. Then the number to subcontract out is 3000-M1, 2000-M2 and 900-M3. Call those numbers S1, S2 and S3. Apparently, there are no constraints on manufacturing and testing hours that are subcontracted out ("any portion of the order").

3. Then the total in-house cost might be written SUMPRODUCT(M1:M2,{50,83,130}). And the total subcontracted cost might be written SUMPRODUCT(S1:S3,{60,97,145}). The sum of the SUMPRODUCTs is the total cost. (Implementation note: It would be better to put the costs into cell ranges instead of using array constants.)

"Repeat" steps #1-3, keeping track of M1:M3 and S1:S3 that results in smallest total cost (#3).

The first question is: how many "repeats" are necessary?

There is a statistical method to bootstrap such a number; that is, to determine when you have repeated "enough times". But generally, 250, 500, 1000, etc are typical numbers that people use arbitrarily. In part, it depends on how much time and memory it takes.

The second question is: how to implement the "repeats"?

You might write a VBA procedure. But the assignment says "in Excel". Does that mean that you cannot use VBA? Only you know the answer.

One way to implement the "repeats" in Excel without relying on VBA is to set up a row of the calculations in steps #1-3. "Repeat" the calculations in, say, 250 rows total. (Since we use RANDBETWEEN, each row will be different, probably.) Then use MIN, INDEX and MATCH or VLOOKUP to determine the "best" solution; that is, the values of M1:M3 and S1:S3 in the row that has the least total cost.

Obviously, I have not implemented and tested this approach myself. I might have overlooked some details. But I don't think so.

Does that help?