Tuesday, May 15, 2012

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.