Book Review : Excel Programming with VBA Starter by Robert Martin

This blog will focus on a book review that I was invited to join in helping out in doing a technical review with publisher Packt Pub (2010-present).

Robert Martin (2012) "Excel Programming with VBA Starter" [accessed 10 June 2013] has done a good job of laying out a pathway  for beginners to pick up the pace of doing Excel formulas into becoming a VBA programmer.

The book cover these broad topics into sections.

The first section discusses what is Excel's macro and shows possibilities to simplify complex using the power of macros. Introduces the reader the macro environment known as the Visual Basic Editor (VBE) and how would the reader go about in finding out where to create their first macro.

Once the reader develops confidence of creating macros with the macro recorder and learning the "words" of the macro language so to speak. This macro language are described as objects. Objects are represented to perform specific actions as formulas do in calculation or to alter the way information is displayed or presented like changing colours of cells in a spreadsheet.

Next sections covers modules which are a grouping the similar tasks and/or macros. The book discusses how to create modules and functions that returns information after working out a complex maths formula. Variables are mentioned which are often used by programmers to track how many times certain information has been processed. Variables do differ according to their type so the author has done well to expand the reader understanding of what type of variables are used for many things.

The same section assists understanding that there many types of tasks as there are variables which macros can be used to perform on Excel's data. To provide an example, the reader will appreciate how to follow down a large list of financial data in a sheet using a loop that examines the totals with a calculation of a discount in the next cell.

Lastly, the understanding of what objects is and how they affect with each other is expanded to help readers learn more about complex topics of VBA such as Classes and Enumeration.

The book contains good examples to guide the reader through and offers a comprehensive list of website links for readers to seek further on their own art of programming.

Til then,
Peter.

A blog update

Hello again to all Internet readers.

Sorry for the long delay and thanks for your patience.

My wife and I since last September 2012 welcomed a new baby.

Since then I have been very busy taking care of family commitments and other priorities as they arise.

Just taking a moment now to inform readers some changes I have decided to make for my blogging experience manageable going forward.

Firstly, I am currently reviewing my website and be posting new pages/information monthly. Don't be surprised if some pages are shuffled about, this is part of the review. Also I have dropped the numbering of blog names and adapted a casual approach of writing a blog. Soon I'll discuss a book about VBA.

Secondly, I am looking into exploring some software development practices I have heard lately of enabling VBA code be stored into a repository known as Github. A repository is basically like a database to holds lines of VBA code. The benefits or "yellow hat" thinking is to track code changes made over time. Github has a service called "Gists". As I understand gists for now, provides a snapshot of code which can be shared with a hyperlink. To put this in another way, social media like Facebook, Youtube or Linkedin can share information about people, gists can share information about computer code; promoting discussion between users. The interesting thing I believe this website could use is to format computer code nicely with indentation and colours of syntax or reserved words making it easier to read/discuss.

Thirdly, I hold a vision that readers can learn more about test driven development in VBA which is one of the new emerging software development practices. This presents a challenge for me as I don't do much testing as I used to do however it is encouraged. I plan to present how to go about setting up such in VBA,  the means to do some unit tests and allow changes be committed into Github.

That's all I have for now,

Til then,
Peter.

Generating Goods over 12 months

This expressive blog shows the opportunity to see how VBA can translate the previous blog’s formula based approach using list of goods within a given financial year.

We introduce the readers to a fictional conversation of how Mick attempts to convince Jane, Shelly and Walter the benefits of using Excel VBA for business to simplify the practice.

Mick and shortly afterwards Walter arrived in the shop / warehouse late in one morning. Mick gave a short hello wave to Shelly who is currently taking a telephone business order request for goods. Shelly motioned Mick and Walter to come inside during her phone conversations.

Walter directed Mick that he will arrive shortly after checking the staff regarding some matters. Mick approached behind the receptionist’s counter towards the main office in which Mick finds Jane reading the local newspaper at his tidied desk against one wall during his coffee break before starting his daily work. Mick noticed that Joe is not at his desk against the opposite wall and his office space is messy loaded with business papers and scribbled notes.

Mick made a brief knock at the open door frame to announce his presence which Jane welcomed him again and suggests in helping him set up a folded table that is used as business meeting table in the middle of the office. Walter came in momently to prepare his part of the table with his usual monthly business meeting.
Jane started the meeting by addressing Mick in indicating Joe will out of his routine traffic jam during peak traffic on his way back so the hand over and monthly catch up meeting with Walter will get started. “so ah, thank you Mick for helping out collecting our suppliers details and presenting the sheet in a way that helps Walter see the strengths of which one is more expensive so quickly that I could have counted them. I may be not good with numbers as Walter does however I have a good eye on some deals for the business. So what ideas you bring to our table today, Mick”

“Thanks for the feedback on that Jane, I have several points to offer today. Firstly, I’ll put on my white hat to begin with about facts on goods list. I have observed that your sales last financial year which contains several boxes are being too full with some left overs.”

“Yes, I overcome this by having a loose bag to compensate buying more crates than we need. “ Jane explained.

“Oh, okay” reflected Mick “then if I switch onto my green hat and discuss alternatives in practice, do you find it easier to know which of the two produces being Fruits or Vegetables are the strongest for the month?”
“Ah, Walter” queried Jane “any ideas?”

“At present, no we are normally not interested in knowing this for a couple of reasons. The cash receipts we received daily tell us which products sold the most. The books recorded tell us that business has been relatively steady of about plus or minus three percent for the financial year.”

Mick looked a bit disappointed from Walter’s response. “Perhaps I’ll put this what I mean this way, using a blue hat which reflects on my thinking about goods list sales. We want to get an overview how Fruits and Vegetables performed during the financial year easily, does this help Walter?” He slightly nodded at the idea.

“Granted! “continued Mick “Excel is good for collecting and storing information quickly. I was able to extract the information from Shelly’s notes and generated a report that summarise these outcomes. Here let me show you.” Mick did a demonstration of the macro after Jane grabbed Shelly after a phone call.

After the demonstration, Jane seems impressed with the graph showing the differences what he realised that Mick may be up to something good over Walter's methods at this moment.

“One last thing before I go, Jane. Using a black hat of caution with the interpretation of results, I am starting to see where you are running short of money when Walter asks the bank to pay for goods as Fruits or Vegetables prices are higher last month than normal. Here are some suggestions…”

So Mick is now offering good sound advice based on the extra information before Walter had a chance to say something.

How to do this using VBA?
There are a number of components involved that we will need to cover. The process consists of creating a new workbook to contain the data, generating the 12 months of the financial year, populating the data with random orders of items and display a graph on a summary sheet.

How to create a new workbook?
To create a new workbook, we need to have an object that is declared as type Workbook as follows.


In this case, discussing in a top-down manner, Excel being the Application, we invoke the methods for workbooks, containing a behaviour named Add. This tells Excel to create a new workbook into memory. As there is an assignment with the Set keyword, we are passing this information about the object creation into memory to wbGoods which is referenced several times at later stages.
How to generate sheets for the 12 months of the financial year?
The code for this is as follows


The code is arranged to process the current workbook using the With statement. During this object selection, I have an inner loop to create all the sheets ahead of time before an additional inner loop is made rename the sheet tabs according to the list of Month’s array in sequence.
How to populate the data?
The code for this is as follows

The code does not show the array data used for sake of brevity; however the complete code is available for download. We initialise several variables to help us track which of the two factors that may be the largest in the month. The data contains columns of data, so the loop counter needs to skip each row (a set of 5 fields) before the next.
While the loop is processing each row, we are populating each field in turn, hence populating different cells according to type of field we need. Once the entire data is populated, we add at the end, the count of fruits/vegetables and total cost from each.

Formula calculation
The formula calculations involved at VBA level is as follows.
After generating the random number for required order which is the incoming orders, the calculation for Required Crates is after checking that is that required orders exceeds the maximum of the crates, we divide the orders by the maximum of crates. Should the required order is less than the crates; we correct the division error by using 1 in its place.

How do I make the Charts?
The code for this is as follows
Once the summary sheet is crated and selected, we do another loop examining the totals of each month populating the Fruits and Vegetables tally counters and costs involved. In addition, we review which of these two categories’ are dominate, then the chart is crated. I have chosen to the use the code in part from his book Excel 2007 VBA Programmer's Reference (2007) John Green et al, page 186 to help me clear any chart objects before creating the chart itself.

Next blog will discuss how orders of goods received from customers.
Til then,
Peter.

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.

Software Development continues...

This blog expands on the software development.

Software development is an integrated approach of making software from concepts of real world problems into working software. This involves repeating the cycles of inputs, processes and outputs over and again in iterative cycles.

That is until the software is considered finished with the customer's agreement. In many cases, using consistent effort will achieve good results.

Generally speaking, the iterative cycle is about finding out what the customer wants to do by check our understanding of what the customer means. Also, we write the software design well to fulfil the customer's needs and test the software making sure that it works and delivering the written software on time for each item in the list that meets the customer's approval.

I present this from two different point of views.

Firstly, Brett D. McLaughlin, Gary Pollice & David West (2007) has introduced these activities, and the table below is expressed on how I believe everything is put together, that is for each activity on the left hand side, there is a corresponding Input, Process and Outputs to be completed. Note that a series of steps are repeated until everything is covered.

Lastly, Adkins, L., n.d. What is Agile http://goo.gl/m0O8Ni presents videos showing how tasks similar as I described earlier would work for teams of different skills such as designer, programmer, user interface, tester in short bursts of four weeks producing potential deliverable or shippable product.

Til then,
Peter

Excel, VBA and Software Development tools Intro.

This blog introduces readers to all the information in regards to Excel, VBA, Software development and thinking tools encouraged for communicating with others.

For Excel
I am excited to have found a very helpful online community resource called GCFLearnFree.org
(1998- present day) "GCFLearnFree" [accessed 13 June 2012] is a free online learning course for the general population. Excel 2007 and Excel 2010 lessons are available.

For VBA
To save my time reinventing the wheel, I took a look on the Internet of what other websites offered in regarding teaching VBA.

Peter (1997–present) from his website "Excel Macros Programming" [accessed 13 June 2012] has been teaching VBA for all versions for a general audience in positive tone & approach as Peter's (1997) profession as an accountant/businessman by trade. Therefore, I feel it is a good cover point in knowing the basics and simple programming tasks such as modules, functions to simplify complex calculations and procedures.

Chip Pearson  (1997-present day), "Pearson Software Consulting, LLC" [accessed 13 June 2012] explains VBA classes including how to construct them. Pearson (1997) uses the context of business employee to guide the readers about classes.

David Horowitz's contribution to Cimaware software website (2000–present) "Using Classes in VBA | Expert Zone" [accessed 13 June 2012]. Frankly, I found it helpful when someone provides a different context learning classes which Horowitz (2000) used information about dogs. I got the point very quickly there.

Bruce McPherson from his Excel Rambling site (2011–present) "Excel Liberation" [accessed 13 June 2012] provided great contributions to eggheadcafe (2000–present day) [accessed 13 June 2012] for .net developers and extend Excel VBA for integration of other emerging techolongies such as javascript. Bruce (2011) has a no nonsense and to the point approach. Mcpherson (2011) [accessed 13 June 2012]  showed what you really need to understand and learn about VBA then expand that approach about classes [accessed 13 June 2012].

Classes are a means of describing objects related to the real world. I would like to take advantage of using classes to describe the business, associated business practices with modern programming software practices.

For software development
I am inspired by the following books from Head First series, published by O'Reilly Media.

Software Development by Dan Pilone; Russ Miles (2008) 
· Object-Oriented Analysis & Design by Brett D. McLaughlin, Gary Pollice & David West (2007) here
· Design Patterns by Elizabeth Freeman, Eric Freeman, Bert Bates, Kathy Sierra, Elisabeth Robson (2004) here

For other topics
Edward de Bono (1992) developed a thinking tool that was later published into a book titled Teach your Child How to Think. The book is available from Amazon here and has been very helpful for me to discuss ideas in later blogs.

Varing points of view in regards to the content will be highlighted. There is a total of 7 attention directing tools along with 6 coloured thinking hats to show different types (or ways) of thinking. The table below summarises the tools available in no particular order. I'll describe the tools when we use them.

The table below has been developed from Edward de Bono's book Teach your Child How to Think (1992)

Name of Tool	Meaning and purpose of tool
Attention directing tools
CAF – Consider all factors	Used to increase the scope or widen the topic at hand.
APC – Alternatives, Possibilities, Choices	Instead of moving forward, we go sideways or in parallel of the topic.
OPV – Other People’s Views	Recognising that thinking affects people, we need to see their values.
C&S – Consequence and Sequel	If an action is taken now, what consequences will occur in the future
PMI – Plus, Minus & Interesting	A method to explore each part before making judgements
AGO – Aims, Goals and Objectives	A means to break down a problem into smaller parts to be achieved.
FIP – First Important Priorities	Which part do we get to do first?
Types of thinking (6 thinking hats)
White Hat	Facts, figures and information
Red Hat	Emotions, feelings, hunches and intuition.
Black Hat	Caution, truth, is this safe to proceed?
Yellow Hat	Advantages, benefits, savings
Green Hat	Suggestions, new different ideas
Blue Hat	Thinking about thinking. The approach of thinking.

In conclusion, I have discussed what Excel does, what VBA does, how to do classes in VBA, where I received ideas for software development and suggested some ideas explaining how to think about problems.

Til then,
Peter.

Fictional Business Plan

This blog explains the creation of a fictional business plan

I borrowed a business book by Greg Balanko-Dickson (2006), "Tips and Traps For Writing an Effective Business Plan" from my local library. This book is also available on Amazon, just click on the link.  Greg covered many steps involved in making a business plan which I used his points to realise the overall business as examples for blogs.

The information below is just a matter of putting my raw imagination into context for business. All names and places are purely fictional.

The company name is called "J&J's Groceries - You select! We deliver Grocery!". I choose the name as the company provides good thought into a brand name for customers in selecting good quality food (fruit and veggies) from the stores at various locations and the company delivers their food at their earliest convenience.

Business industry is a Grocery and Food Delivery Industries combined. There are three competitors, named "Brians Groceries", "Council Food Deliveries" (similar to "Meals on Wheels" form of service which is managed by a council by volunteers), "Supermarket Fresh" a local supermarket. Excel will be used to find how much share of the industry or market is currently the strongest.

Structure of the company is a small business aged three years old managed by two brothers named Jane and Joe Bros Limited. The ownership is registered by Jane who is in partnership with his younger brother Joe. Jane (pronounced JA-ne) is a business-oriented person who learnt a diploma in TAFE about managing a business. Joe is an experienced green grocer who their late father Back has handed down their family tradition of grocery store to Joe. Joe has asked Jane if he could take care of the business so Joe can focus primarily on goods and supplies. This has worked out well as long as Jane can keep the language simple that Joe could follow and understand about how the business runs.

The staff roles and some responsibilities are listed as follows
Jane and Joe - Business owners. Jane is an afternoon type of worker. Jane will manage the store, briefly review business operations, handle some order processing and finish with some delivery using his own car for customers orders. Joe is a morning type of person who is always follow the rising of the sun to buy daily groceries from various suppliers and be the first in the store selling them to customers before Jane enters the store at around about lunch time.
Shelly - business secretary and receptionist who mostly works in the office and produce reports for both Jane and Joe at various times of the day regarding the flow of business in general.
Eric - Store man manager who works well with Joe in the morning with spending money on goods acquired in the early morning shopping with suppliers and Eric then ensures they are delivered into the store house on time for processing customers and business orders.
Porter - Business only delivery trucker who ensure all orders are delivered to business as a priority.
Mick - an IT university student currently doing some work experience in the business sector to fulfill a business profile study for the course. Micks current Excel skills will become invaluable to both Jane and Joe gaining a snapshot of what occurs in the business. Further blogs will be developed as the story unfolds on how Mick can work towards improving the business.
Ashley, Furnis, Doein - young part-time workers helping out the business. Processing goods in the warehouse for orders/requests into deliveries packages for truck delivery.
Walter - A family friend who is a qualified accountant whom regularly audits the bookkeeping practices, helps out with business tax returns and ensure the financial strategies are sound.

The customer demographics are deliberately set to be simple. We have 6 types of consumers accordingly to their needs.
1) Morning Groups - Various fresh food for families or early starters who are up in the morning often needs fruit for their lunches on their way to work.
2) Afternoon Groups - Various fresh food for workers who walks in for their lunch consisting of fruit or families who have time to get vegetables for dinner.
3) Evening Groups - Various fresh food for later workers who needs to make their dinners
4) Business catering - Fresh food at a modest bulk price to serve food for business lunches.
5) Business restaurants - Fresh food for discounted price.
6) Business cafe - Fresh food as a negotiable price for cafe.

The Suppliers - 5 suppliers were identified.
1) Locally - "Grocery Farmers Market" is the main distribution hub where many local farmers produce their fruits and vegetables. Prices are set at wholesale for bulk containers. Competitors also shows up there.
2) Locally - "Paddy Orchards" for a seasonal and a limited number of stock on fruits only.
3) Locally - "Shack Vegetables" for seasonal and limited stock on vegetables only.
4) Interstate - "Fields Orchards" is a small family owned business providing distribution of their own farmers produce.
5) International - "Hardy Garden Market" is the wholesale distribution for special fruit and vegetables recognised by some customers not produced locally.

The products or goods are basically fruits and vegetables that are managed according to seasons as the suppliers rotate their goods due to availability. Further blogs will be required to explain this section in depth.

The services have not changed over the last three years which is basically put the goods in printed boxes of various sizes to allow better handling and storage on Porter truck or Jane car.

Operational procedures
Administration and Office Manager (Jane & Joe on a time rotation basis, Shelly full time for delegated tasks)
Responsible for some administration, office management, financial keeping and reporting.
· Cash flow management: tracks and analyse financial performance
· Internal control systems: budgets, purchase orders, credit approval and collection.
· Weekly and monthly ratio reports and analysis reports.
· Account receivable collection: maintains average receivables aging 30 to 60 days
· Job costing
· Payroll
· Accounts payment (A/P) and accounts receivable (A/R)
· Overseeing of shop expenses
· Handling of bank reconciliations
· Forecasting of A/P and A/R on a weekly basis with operations manager.
· Individual case studies for major purchases, as needed

Operations Manager - Dispatch and Sales (Eric and Porter)
Management of the day-to-day operations
· Purchasing goods from suppliers
· Maintaining on-time pickup and delivery schedules according to customers demand
· Maintaining daily drivers logs
· Completing dispatch and billing orders
· Compiling weekly management reports
· Sales: Maintaining contact with all major accounts (weekly) and actively establishing relationships with new customers (1 per week).
· Work Monday to Friday 8am to 5pm
· On call roster 24/7

Equipment Maintenance
Jane & Joe has a contract agreement with provider "Tracey's business suppliers" for all primary equipment and maintenance repairs which also include office equipment like computers and photocopiers.

Processes
Early morning is mostly purchasing goods from suppliers according to customer demand
Delivery of goods to arrive in shop / warehouse
Load the shop for regular customers while warehouse starts business packaging
Commence delivery of packaging during the day while customers orders will be generated at the end of the day
Finalise all delivery that are outstanding before closing up the shop

This business has 5 goals.
1) Maintain good food quality - Ensure the quality of food and proper food handing procedures remains high by asking customers a survey of their business at the end of each month. Data is collected and audited by Walter against financial and business performance.
2) Delivery commitment - Ensure all goods received from the suppliers arrived within no more than 3 hours from purchase to allow processing of orders/requests from customers. Ensure all delivery of customers orders/requests are completed with 4 hours maximum. All information are recorded on paper based notes with different time-based stamps.
3) Stakeholders complaints procedure - Ensure all complaints are recorded in a timely manner that can be reviewed and actions are communicated back to the stakeholders affecting the business. Data is collected and audited by Walter against financial and business performance monthly.
4) Financial goals - Ensure a buffer of at least 2 to 3 percent of total cash flow is achievable to cater any unexpected risks to the business.
5) Implement a risk management process throughout the business - This will improve occupational and health safely issues raised in the workplace.
That will be enough to get started on the next blog. I will create an introduction to Excel and VBA before addressing the transfer of content into Excel.

Til then,
Peter.