Purchasing Model in Excel

Description
Purchasing Model consists of a model which optimizes the purchasing of 3 different raw materials from 5 different suppliers for 5 different plants to minimize the total purchasing costs.

!! You need to enable macros to use the financial model !! 


On the Inputs tab, there is a description of the color coding: Inputs are always depicted with a yellow fill and blue letters, call up (that is direct links from other cells) and calculations are depicted with white fill and black characters.


There is also a color coding for the various tabs of the model. Yellow tabs are mostly assumptions tabs, grey tabs are calculations tabs, blue tabs are outputs tabs (that is effectively results or graphs) and finally light blue tabs are admin tabs (for example: the cover page, contents, and checks).


The only tab where the user input is needed is in the Inputs tab. The user needs to fill the yellow cells in each of these tabs, all the remaining tabs are calculated automatically.


The user sets the shipping costs per raw material from Supplier to Plant. You have to fill these inputs for each of the 5 suppliers, 5 plants and 3 raw materials.


After that the user sets the prices for each of the 3 raw materials from each supplier, the maximum supply from each supplier per material, and the demand from each plant per raw material.


Moving to the Calcs tab the user just needs to press the Optimize Raw Material Purchases. After a while the results are calculated, and you just need to press OK.


The results are presented in the Outputs tab, as the supply percentage per supplier per material, demand percentage per plant per material, raw materials purchase costs , shipping costs, and total costs per plant, supplier and raw material.


A set of graphs in the Charts tab, present graphically the supply quantities, the demand quantities, raw material purchase costs, shipping costs, and total purchase costs.


Finally, the checks tab where the most critical checks are aggregated. Whenever you see an error message in any page, you should consult this page to see where the error is coming from.