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.

Random Suppliers over Financial Months in VBA.

This blog continues with Suppliers for the same VBA class and will expand the results to spread over the given financial year. As we know the financial year is from July to June each year and also we have an average of 30 days per month.

Let us further suppose that Mick has found out that the daily results have overall helped the business and received a request to find out is the business is improving month by month.

Mick then updated into Excel the additional requirements.

Story ID	Title	As a…[Role]	I want to…[Feature]	So that…[Benefit]
2	Supplier	Business Owner	See the list of supplier’s expenses over the financial year. July to June.	I can determine which supplier to continue trading.

And the acceptance criteria, Hint: $2,000 per day times the average 30 days equals $60,000

Story ID	Scenario Title	Given… [Context]	When… [Event]	Then… [Outcome]
2	High monthly cost of the Supplier	The supplier monthly cost	the supplier monthly cost is calculated exceeds $150,000	Flagged too costly and continues traded with extreme caution
	Low monthly cost of the supplier	The supplier monthly cost	The supplier monthly cost is calculated between $60,000 and up to $150,000	Flagged too cheap and continues traded with caution
	Average monthly cost of the supplier	The supplier monthly cost	The supplier monthly cost is calculated under $60,000	Flagged as cheaper and continues traded as caution.

The suggested solution for this exercise is use “Random Supplier in VBA” as a base and make additional changes to the range of random values and allow another loop to cycle through months.

The Supplier class cSupplier will not require the Rank so we remove this from the class and introduce Excel’s average formula to create an average across the months.

In Excel 2007 or above, I have set the conditional formatting to show the rank by different colours. The colours are Green for below the average, Grey around the average and Red if above the average.

Next blog will discuss how to create a random list of goods for a given supplier.

Til then, Peter.

Fictional Business - Random Supplier in VBA

This blog begins by making a feature list about J&J's Groceries, focusing on Suppliers for the VBA class.

Let us suppose that Mick, the IT guy who is gaining some work experience was asked to see if there are some ways of improving the business using Excel. Mick commenced by introducing Jane and Joe the six thinking hats in order for Mick to establish many common ways of discussing business from different points of view. Mick used extensively at present the white hat thinking of facts and figures about the way suppliers are organised and managed.

The feature list contains user stories and acceptance criteria to define what is “done”. This framework is derived from Behaviour Driven Development which is found on Wikipedia (2001-present) "Behaviour Driven Development" [accessed 13 May 2012].

Mick then wrote into Excel as a list of feature that incorporates the key words below as shown in the table headings.

Story ID	Title	As a…[Role]	I want to…[Feature]	So that…[Benefit]
1	Supplier	Business Owner	See the list of supplier total cost of the day	I can determine which supplier for J&J Grocers continue orders for tomorrow

And the acceptance criteria follows this format structure.

Story ID	Scenario Title	Given… [Context]	When… [Event]	Then… [Outcome]
1	High Total cost of the Supplier	The supplier total cost for today’s order	the supplier total cost is calculated exceeds $5,000	Flagged too costly and traded with extreme caution
	Low total cost of the supplier	The supplier total cost for today’s order	The supplier total cost is calculated between $2,000 and up to $5,000	Flagged too cheap and traded with caution
	Average total cost of the supplier	The supplier total cost for today’s order	The supplier total cost is calculated under $2,000	Flagged as cheaper and traded as caution.

The solution for this exercise is to create classes namely cSupplier with few methods (behaviour) and a macro procedure SupplierExample to execute the code via two buttons. The first button is to clear all the fields. The second button allows the VBA class to run again easily.

The Supplier class cSupplier contains these private variables Name, Amount and Rank.
Supplier class has a method to create a random amount and provide output of the rank given the amount randomized.

Next blog will discuss how to spread the days over a period of one financial year, know in business circles "Year to Date" or YTD for short.

Til then,
Peter.

Fictional business - Random Suppliers in Excel

This blog resumes creating a fictional business focusing on about suppliers.

We begin by creating a small suppliers list using Excels random number function.

What is a random number?
Computers are predictable in their computation power as it is possible to select a different number at a certain time. The number is always between 0 and 1. Wikipedia (2001-present) [accessed 25 April 2012] has a good overview about Random Number Generators.

Excel has two in-built functions called RAND and RANDBETWEEN as both function returns a number between 0 and 1 to many decimal places after a decimal point.

Take note that each time there is a change in a cell or formula. Excel will by default update all calculations automatically. For functions like RAND and RANDBETWEEN is calculated with a new number.

If the random result appears suitable for business data, we simply copy or cut and paste the values in another cell or range of cells in order to preserve the data.

Why do we require the random?
To generate a fictional data for business, we need the computer to select different numbers for a range of items like company supplier names and eventually select supplier’s goods and prices. In essence, the computer will select a supplier name from a database.

How do I get a random number?
The picture attached shows when we enter “=RAND()” formula in Excel. We need the brackets as the results returning to a cell contains no extra information provided.

Thus, to generate a number between 1 and 10, we need to change “=RAND()” as “=INT(RAND()*10)+1)”. The use of INT is another formula to return an integer that the decimal point is not expressed.

On the other hand, =RANDBETWEEN(Bottom,Top) needs information about the lowest number namely Bottom and the highest number Top. The picture below shows the difference between these two functions.
This can be simplified by using =RANDBETWEEN(1,10) which produces the same results as =INT(RAND()*10+1) does.

How to make a random list of supplier names?
To achieve this, we need a list that expected not to change when the RAND() function is called. The small list here is shown as an example.

The next picture introduces another column called Result.

I use the VLOOKUP formula (short for vertical lookup) to ask Excel return the supplier name from a range of cells. The information for the formula is as follows. VLOOKUP(,,,).

Thus, when combining both the random information above with the VLOOKUP, the formula to enter becomes =VLOOKUP(RANDBETWEEN(1,5),$A$3:$B$8,2,FALSE)

The $ dollar signs are important in the formula to keep the range of cells the same should we drag the list down like the next picture shows. It is a matter of personal choice on how large the list can go.

The next blog will apply these principles into a VBA module.

Til then, Peter.