Sunday, June 3, 2012

List of random goods from the supplier


This blog progresses onto another topic for the fictional business regarding the Suppliers goods.

We are going to develop a list of goods of fruit and vegetables. The orders are deliberately random generated so demand gives us an idea of the expenses we may pay those goods.

What is the source of the Goods list ?
I have found a good Australian website of Nicole Avery (2011-present) "Planning with Kids" [accessed 03 June 2012] to list the fruit and vegetables which you can see this and record the list into Excel.

I have assumed the maximum capacity for each item on a wooden box which is indicated as a column, how much each box would cost, how many items we need to ask our supplier to satisfy demand and how many boxes in total to check our shipment.

What the cost of Crates?
I researched on the internet what price may be reasonable for wooden crates and settled on $60 per box. Sounds pricey for many businesses as it serves the purpose for this model of costs per demand.

If anyone has some suggestions to show me how wooden crates are meant to be priced, leave a comment and I will update costing assumption in later blogs.

How did you calculate the number of boxes required?
I used a mathematical formula in Excel to see how many boxes as in quantity.

The formula is expressed as =IF(INT(F5/D5)<1,1,(INT(F5/D5)))

I expand the meaning of the Excel formula as follows:-
The first part of the formula is =IF(INT(F5/D5)<1,1,(INT(F5/D5)))

Let F5 mean the “Required amount from the supplier”. To put this in another way, this means how many goods we need once all the orders have been added up for the day before we distribute the goods tomorrow to our customers.

Let D5 mean Total Wooden Box capacity. I used my imagination to see what would be the practical maximum amount of goods could fit in a wooden box for certain items.

We then divide the Required amount from the supplier from the total capacity to see how many boxes is required.

As an example, let F5 = 325 and let D5 = 70 thus 325 / 70 is 4.6 boxes. I use the Excels function called INT for INTEGER to strip any decimal points as this exercise isn’t necessary.

So the 4.6 boxes become 4 with any loose or extras are placed on top of the last box when shipped to the warehouse.

The second part of the formula is =IF(INT(F5/D5)<1,1,(INT(F5/D5)))

There is a logic decision in place to check that we have no errors with the division. As the general assumption is, we cannot divide any number by zero as this is a mathematical impossibility.

Also, if any division returns a lower or small result like 0.46 for an example, we ask Excel to place at least a minimum of 1 wooden box regardless.

Next blog will discuss how to spread the cost of Goods over the financial year as I had done for Suppliers.
Til then,
Peter.