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.