Distribution Model in Excel

Description
Distribution Model consists of a model which optimizes the distribution of products between plants and warehouses to minimize the shipping costs to the customers.

!! 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 product from Plant to Customer, from Plant to Warehouse, and from Warehouse to Customer. Additionally, the user sets the product capacity for each plant.

After that the user set the Warehouse Capacity for each Warehouse and Product, and finally the user sets the Demand for each customer and for each Product.

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

The results are presented in the Outputs tab, as the total cost of shipping, and a summary for all products, products sent to customer, products sent from plant to customer, and finally demand and capacity load factors.

A set of graphs in the Charts tab, present graphically the quantities, shipping prices, shipping costs, shipping costs to customer, shipping costs from plant to customer, and capacity load factors.

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.