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.

Sunday, May 13, 2012

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.