Class welcome to module 5,
Milestone 2 is due at the end of this module. Milestone 2 drills down into cost factors. Make sure you have corrected any issues from Milestone 1, otherwise these errors can cause problems in Milestone 2. Also, you need to take care in organizing your Milestone 2 reports in a logical manner and properly labeling the charts and tables.
In Part A you will be producing three costs for each BIN. Those will be:
- Transport Cost (what did each shipment in each BIN actually cost to ship)
- Cost per pallet (if you take the transport cost and divide by number of pallets shipped you will get cost per pallet)
- Cost of shipping (what did it cost the company to make all the shipments in this BIN)
In this part you do need to examine where the break-even point is between shipping individual pallets and when it is cheaper to simply use a whole truck. For example, if it costs $210 to ship pallets individually to Omaha and it costs $1000 to send a whole truck (full or not) you would realize that for any shipment above four pallets (4 x $210 = $840) it would be cheaper to use a whole truck (5 x $210 = $1050). So, for shipments in BINs less than 5, the Transport Costs would equal the cost per pallet and for shipments in BINs 5 to a BIN 24 the Transport Cost would be equal to the cost of a full truck.
In Part B-C, COGs is the Cost of Goods Sold and it equals the cost of producing the wine shipped.
Be very careful with Parts A-D because the parts build on each other, so a mistake made earlier can foul up the rest of your work.
This week you are also being introduced to ‘Solver’. Make sure that it is part of your Excel program or that you have downloaded it as an Add-In. If you are wondering if you have Solver, just look at your ribbon bar and it should appear (for example on this screen shot it is under Data Analysis on the left side.
If you click on Solver, a box (see below) will appear.
If you have not used Solver before, it will take some time to master and you will experience some trial and error. Start early!
What Solver basically figures out is what is possible when you are working with multiple constraints. An example if you were building boxes and you knew that each box required 1 sheet of plywood, 20 linear feet of framing material, and 30 nails, and you had a supply of 20 sheets of plywood, 260 linear feet of framing material, and 900 nails how many boxes could you make? In this simple case the amount of supplies you have would be your constraints.
You could look at each constraint individually:
Plywood, you have 20 sheets and each box requires 1 sheet per box- so you have enough for 20 boxes
Framing material, you have 260 liner feet and each box requires 20 feet- so you have enough for 13 boxes
Nails, you have 900 and each box requires 30- so you have enough for 30 boxes
In this case the binding constraint is the framing material, it will limit total production to 13 boxes.
However, as the formulas become more complex and there are more variables, the time need to solve the problem would become increasing time consuming. Therefore, we use Solver.
When you take the Quiz and the following question appears “What three things do you need to specify before running Solver?” on your quiz do not worry if you get it wrong. There is a problem with the question and I will issue you credit for the question.
You need to master the use of Solver because in Milestone 3 it accounts for a large percentage of the grade.
Keep up the good work!
Steve O’Malley
