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.