Tayo Solagbade’s
Performance Improvement IDEAS
(PI Squared) Newsletter
Monday 6th February 2017
NB: This PI Squared newsletter will be published weekly, on Mondays, in place of the Speaking/Web Marketing IDEAS newsletter, starting from today – 15th February 2016. I’m reinventing my Monday newsletter content and theme, to accommodate my vision of serving the growing audience of serious minded individuals and organizations reaching out to me, with information, education. news and research findings designed to help them do what they do better.
************
    
PII 052: Real Life Farm Business Case Study – Excel-VB Ration Formulator Spreadsheet Software Anatomy
In this week’s issue of my Performance Improvement Ideas (PII) newsletter, I discuss – using annotated screenshot images – design considerations employed in developing my popular Excel-VB Ration Formulator spreadsheet software.
Prospective buyers, as well as users/owners of this app curious to better understand how it works, and how to make more productive use of it, will find reading this a useful experience – as would persons interested in learning how to build commercial quality apps with user friendly features.
This is a real life farm business case study designed to offer potentially useful insights other business users can learn from, in building zero cost custom apps to solve real life problems.
The information provided is designed to guide the app’s user to become familiar with making use of the app. But it also highlights the thinking that guided design of the different interfaces and features/functions.
This was done to provide the farm CEO, his/her personnel (with skills to function as in-house developers) and possibly extension specialists, information and insights to enable them develop similar apps on their own, at zero cost, to solve other data handling and report generation problems.
4a. Control of Unauthorized Access to Ration Formulation Workbooks is Crucial
The Excel-VB Ration Formulator application is not a normal Excel workbook. When opened, it remains in a small window due to initial disabling of Excel-VB code in it. A PDF user guide (and a 4 part video screenshot tutorial) explains (and demonstrates) how to get to – and past – the login screen (shown above).
A ration formulation workbook typically holds sensitive information, which if tampered with could have serious consequences. The login screen (left above) prevents unauthorized users from gaining access to the contents of this workbook app. A username and password assigned to the licensed owner grants user access.
Following login, the Navigation Menu ensures the user does not stray. S/he clicks the appropriate command button to move directly to the desired interface.
The easy-to-understand interface greatly reduces the need for formal Excel training of end users – especially farm business owners who often have a lot on their hands, and could struggle to make out time to learn Excel in order to use it. The intuitive graphic user interfaces make it possible for a complete Excel novice to use the Excel-VB Ration Formulator.
To ensure success, extension specialists or farm CEOs who choose to develop their own custom apps for use in managing farm business operations will need to build in similar user friendly features to enable ease-of-use.
4b. Best Practice Worksheet Design Is Essential to a Error-Free Use
Figure 3: This nutrient composition table is in a different worksheet from the ration computation table, which has formulas that draw on values from the former. Names of new ingredients added to the nutrient composition table instantly appear in the ration computation table’s drop menus.
For every ingredient that’s chosen by the user in the computation table(below) ALL corresponding nutrient values (protein, energy, calcium etc) entered for it, in the nutrient table (above) instantly appear in the corresponding columns in the composition table – via a database function. No need to type new values in, each time a new or different ingredient is added. This app is futuristic in that it enables users easily add new ingredients or completely replace old ones and their nutrients without having to tamper with the spreadsheet etc. So s/he gets to focus on formulating the ration, rather than worrying about modifying a spreadsheet.
Above: The user clicks an ingredient’s name in the in-cell drop menu or via the floating data entry form (see below). Clicking an ingredient’s name will post it in the cell and the database functions in the table will instantly cause its computed nutrient values to appear along the same row (drawing from the nutrient composition table where they would have been posted initially). A drop menu is also used to choose “fixed” or “variable” label to assign to each ingredient.
4c. Computing a Ration Formula Using the Excel-VB Driven App (Steps A to D)
At “A” Type target protein value into the field labeled “Tgt Pr+ (%)”. Below it, enter total kg of feed you want to make
At “B” Use the drop menus to choose the crude protein sources you want to use
If you choose more than ONE CP source, you MUSTU choose “Mixture” at “C” in the “Variable No. 2” drop menu under Pearson Square Inputs. Once the above is done, click the REFRESH button. The software will generate values into the grey boxes in the “Estimated % To Use In Ration” section (see “C” above).
At “D” You will now enter the values from “C” above into the data entry fields for EACH ingredient at “D” e.g. 55.57 for Maize. Then click the REFRESH button. The formulated ration will be updated on the form (and also on the worksheet).
Drop menus provided next to each ingredient name allow you specify if it is a Variable of Fixed ingredient. The app’s computation takes this into consideration. If you forget to make up the totals to 100% the app will throw an error alert and force you to find and correct the mistake BEFORE you can progress.
The table’s total protein should equal the target Pr+ value you want in the ration you’re formulating. If not check the total % in ration (should be approx 100%) in the table. Sometimes minor changes to the variable ingredients will be needed to get the target protein right. The kcal ME/g, Calcium, Fibre (should be within desired range for your animals).
You will check Ingredients Cost (Naira). Compare to your budget. Make adjustments if needed – possibly using cheaper alternatives. Note that the app instantly inserts user entries in on the data form into the correct location on the spreadsheet. The user need not interact with the spreadsheet at all. And when the right formula is obtained it can be stored (by clicking the “Store this formula” on the worksheet and/or printed out by clicking the “Print Preview” on the worksheet.
4d. Smart Error Handling Ensure Users Get Reliable Ration Formulas
The app formats relevant formula cells in the password protected ration formulation area. They turn RED when “invalid” values appear in them. And it throws an error alert prompt notifying the user.
If the error is not corrected, the user will not be allowed to move beyond that stage. In the instance below, the TOTAL % in ration is less than 100%. The user must clear whatever caused that error before s/he can continue with the ration formulation.
In contrast, manual Excel workbooks created for feed formulation typically allow such severe errors go unnoticed, since no in-built error handling exists.
Bear in mind that this application has evolved over the years, based on feedback from users, to improve the “reliable and safe use”. The original version built in 2004, was not as enhances as this.
4e. Custom Excel Apps Can Use Different Ration Formulation Techniques
This app is based on an improved version of the Pearson Square computation technique, which – unlike the original version – allows for feed formulation using more than one protein source/ingredients at a time. It generates a print-ready formula and diagram.
At the bottom, the user is reminded of rules guiding feed formulation via this technique
Read: If You Don’t Understand Feed Formulation, You Cannot Formulate Feed Correctly [Hint: Answers to a Farm CEO’s Whatsapp Queries About Feed Formulation]
4f. Farmers Must Know the Full Nutrient Profiles of their Animals
Example of Broiler Starter Ration profile a farmer will formulate a ration to meet
Click here to let me know if you’d like to learn more about how to build a commercial quality Excel-VB driven spreadsheet application to improve your work, or that you can sell to clients.
Excel-VB Driven Ration Formulator
1. Click here to learn more about this app – watch demo videos etc
2. Click here to watch a 4 part video in which I demonstrate how to use this app to formulate rations using real life data sent to me by an Algerian PhD student.
Click here to contact me about purchasing this product.
EXCEL-VB DRIVEN POULTRY LAYER FARM MANAGER SOFTWARE
Click here to download a detailed PDF user guide and watch 15 screen shot user guide tutorials of the Monthly Poultry Farm Manager that I now offer Farm CEOs.
Click here to contact me about purchasing this product.
SDN Blog™
New posts from last week*
Monday:
[Wednesday]:
[Thursday]:
[Friday]:
[Saturday]:
N/A
[Sunday]:
|
