This week’s issue of my Performance Improvement Ideas newsletter is based on transcript excerpts from the Introduction video in which I demonstrate (for members of my Excel-VB club) how I built my commercial MS Excel-VB driven Main, Imprest and Bank Cash Book/Reconciliation Application (which I sold to several Lagos based .

I built it for – and in collaboration with – a Chartered Accountant who also happens to be an ICAN (Institute of Chartered Accountants of Nigeria) examiner and auditor, with more than 20 years of experience. At rhe time he worked as head of the finance section of a popular large Lagos based hospital. Today, he runs his own financial services consulting firm.

So welcome to the first case study we’re going to work on. I’m going to show you how to build an automated cash book.

SCROLL DOWN TO READ THE FULL ISSUE

Tayo Solagbade’s
Performance Improvement IDEAS
(PI Squared) Newsletter

Monday 19th June 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 071: VIDEO Tutorial – How I built my Automated Cash Book/Bank Reconciliation Spreadsheet Software [Part 1]

This week’s issue of my Performance Improvement Ideas newsletter is based on transcript excerpts from the Introduction video in which I demonstrate (for members of my Excel-VB club) how I built my commercial MS Excel-VB driven Main, Imprest and Bank Cash Book/Reconciliation Application (which I sold to several Lagos based .

I built it for – and in collaboration with – a Chartered Accountant who also happens to be an ICAN (Institute of Chartered Accountants of Nigeria) examiner and auditor, with more than 20 years of experience. At rhe time he worked as head of the finance section of a popular large Lagos based hospital. Today, he runs his own financial consulting firm.

So welcome to the first case study we’re going to work on. I’m going to show you how to build an automated cash book.

This application was actually built for a large hospital (in 2007 – when my 9 year old domain –http://www.spontaneousdevelopment.com  – NOW defunct – was still in use).

They had over 120 staff, somewhere here in Lagos, Ikeja. And what I just did now, I’m going to deliberately exit and open it again for you to see.

When I open the workbook app, the first thing I have to do is “Enable Macros” on the security warning that appears above the row headers area, I click the ‘options’ radio button in front of it.

Then I click on the “Enable this content” radio button. So this is what you will see on your own screen when you do the above

I’m going to send you a copy of the app to use. It loads an automatic main menu form. I’m going to be showing you how to build this kind of form in this tutorial, over time.

It’s advanced stuff, and may not be something you can do immediately. But I will show you how it works, and that would be part of the whole idea.

 That’s why you’re a member of the club. Over time, you will gradually pick up the skills. Little by little, I will introduce you to all these skills. It’s not going to be sometime you can learn overnight.

But I will show you how it works, and you will understand the thinking behind it.

However, the structure and the initial design behind it is what I’m really going to emphasize that you get, so that you can build your own basic version of this application.

Right. So this is an automated Main, Impress and Bank Cash Book application, and it generates payment summaries.

It even does bank reconciliation where you do bank cash balance as well as the cash book balance.

So the bank balance and the cash book balance and reconciled bank balance.

I did all this with a Chartered Accountant who also happens to be an ICAN (Institute of Chartered Accountants of Nigeria) examiner and auditor, with more than 20 years of experience.

So, in building this application, I learned quite a lot from him about book keeping and accounts at a basic level – enough to enable me deliver apps that automated what he did manually, so he could free himself from routine and repetitive tasks and be more productive on his job.

Back in 2007, when we worked together, he was the head of the accounts department of a well known large Lagos based hospital. Today, he runs his own Financial Consultancy Services outfit.

Now let’s take a look at the app’s Main Menu:

Now, I put the title for THIS tutorial on the top right of the Main Menu for the demo copy of the app – on the placeholder label that reads “Demo Tutorial App for Excel-VB Club 2016”

[Hint: I originally wanted to run this tutorial series in 2016 – but decided to take members through weekly basic tutorials to enable them better relate to the advanced concepts that come up in THIS tutorial]

But what you actually have behind that placeholder label is ALWAYS the logo of the license owner company – because I brand my application for each company.

So, in other words, I use skills from different environments.

For instance, what you see as a banner (bearing my logo and the app name) with the yellow background, located to the left of the placeholder label, was done in Photoshop, where I created it as an image, then exported it in JPEG format and imported it into the form control on the Main Menu Navigation form.

So, what you see on the Main Menu Interface was all designed by me. I put the command buttons on it. You’re going to learn how to do it, but this is advanced It’s not something you’re going to learn anytime soon.

But I will give you basic introduction to it, and you can develop your skills along the way.

What we’re going to do is we’re going to look at the design, the architecture of the software to understand the thinking behind building it.

So, you look at the menus, you look at the left block of command menus, and the 2nd command button from the top, labeled “Update Settings” – what is that?

[TIP: Note that in the video, to enable you understand the following explanation, I restore the formula bar and row/column headings by clicking the corresponding check boxes under the VIEW tab on the MS Excel Ribbon. This is so you can see the cell addresses etc]

When you’re designing applications, you will see that this is just a normal worksheet, and I’ve hidden all the other columns in the worksheet. So I only have columns “B” to “O” visible and accessible to the user.

And in those columns I’ve put in the Cost Headings, List of Debtors, the bank accounts, the receipts i.e. the income heads.

Now first of all how did I hide the other columns and why did I do that?

I did that because when you design applications for people, no matter how simplistic the app may be, it is better that you imagine you are trying to create something that could be called a “Novice Proof” interface for users, where what they should be allowed to see, should be only what they need to see.

That way they don’t go drifting around all over the worksheet. This is important when you consider that fact that today’s MS Excel 2007 and above spreadsheets have OVER 60,000 rows and more than 1,000 columns.

That’s a huge amount of spreadsheet space capital and a user who is not savvy or clued up can easily get lost or confused if the interfaced does not restrict him/her to the app’s defined user interface.

So, you have to control that space. Create a table where you want the user to work, and use color formatting to add guiding information.

For instance, in the Update Settings interface, you can see I color formatted the outside of the table, and then I added field headings for each column in the table.

Then I left the body of the table “white” and used blue text color to indicate to the user sections where s/he can post entries, while “black” text color indicates protected sections (typically containing values that are NOT to be modified).

As shown in the video, pressing ENTER key while the cursor is on an area that has black colored text causes an error message to appear stating that the cell is password protected.

In contrast, pressing a letter key on ANY area where text colored text appears initiates data entry – and no error message appears.

The user quickly learns to use this intuitive interface, without problems.

So, this color coding is part of the formatting used in design of the user interface. A yellow bar appearing along a vertical column under a particular field header demarcates the END of a list of menu items.

For instance, the list of banks shown in the table – appears in a drop menu – including ALL blue colored items in the column up till the yellow band. Any entries BELOW that band will NOT appear in the drop menu.

In this video, clicking the drop menu just above the BANK field heading shows a sequence of bank names corresponding to those arranged BELOW the list of month names under the WORKSHEETS heading in the Update Settings table.

And that’s what happens in your design. You can actually make it unnecessary for users to type headings of interfaces in the software i.e. to minimize chances of typo errors causing problems in the many areas where the app uses such headings/listings in computations.

This was not something I arrived at arbitrarily for each data entry field. It involved extensive review of the potential menu items to be used, in conjunction with the client, to AGREE on how many spaces (rows) to provide.

They wanted to be able to add new banks, in case they opened more accounts. So we did some thinking to decide just how many extra spaces would be realistic to add.

The above indicates that there Excel-VB Solutions Development MUST be done in a far from abstract manner, if you want to end up delivering an app that clients will LOVE using.

This has been the key to my success in developing and selling a wide range of custom Excel-VB apps to business users in different industries.

So this is in terms of the design of the workbook interface.

In this Update Settings interface, we’re doing what can be called “Initialization of Data”. We’re setting up generic information that the application will use. Now, the client users constantly need to make entries of name of debtors – who are yet to pay up money owned for use of the company’s services.

With the design used here, the person posting entries into the Debtors/Creditors schedule will therefore NOT need to start typing the names of the individual debtors.

Instead s/he would simply choose the appropriate name from the in-cell drop menu in the schedule.

That makes it very easy for the application to then generate needed reports with minimal errors. This is so because once you’re using drop menus, ALL the names of debtors will appear exactly as they need to be.

The minute the user chooses a name, it will be inserted into the table, and the report can be generated for that customer.

BELOW: Watch the quick start user demonstration video that I created for users of the app

********************************

End of Part 1 (Click here to request a copy of this introductory Excel-VB Tutorial video that you can download and watch offline)

********************************

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]:

[RECOMMENDED] Lagos Opens Website To Register Undergraduates For Internship Scheme

[Friday]: 

N/A

[Saturday]:

N/A

[Sunday]:

The FARM CEO (Issue 85): VIDEOS – How to Start a Farm with No Money, How to build a fish pond (Backyard fish farming), How to Make Money on a Small Farm

Tayo K. Solagbade*

Self-Development/Performance Improvement Specialist

*Best Practice Farm Business Support Specialist & Founder of the MS Excel Heaven Visual Basic Automation Club and Competition

Mobile: +234-803-302-1263 (in Nigeria) or +229-66-122-136 (in Benin Republic)

http://www.tayosolagbade.com

Tayo K. Solagbade is a Location Independent Performance Improvement

Specialist and Multipreneur (i.e. a highly versatile/multi-skilled entrepreneur), with a bias for delivering Best Practice solutions to Farm Businesses and others.

Since 2002, he has earned multiple streams of income providing individuals and organizations with personal development training and coaching, custom MS Excel-VB solutions, web marketing systems/web hosting, freelance writing services, and best practice extension support services (for farm business owners).

Tayo is the author of the Self-Development (SD) Bible™ and the popular Livestock Feed Formulation Handbook. He is also the developer of its accompanying Excel-VB driven Ration Formulator™ and the Poultry Farm Manager™ software.

He has delivered talks/papers to audiences in various groups and organizations, including the Centre for Management Development, University of Lagos, Christ Baptist Church, Volunteer Corps, Tantalisers Fast Foods and others.

In May 2012 he was the Guest Speaker at the Centre for Entrepreneurship Development’s Annual Semester Entrepreneurial Lecture at Yaba College of Technology in Lagos.

On 1st April 2013, Tayo (who reads, write and speaks the French language) relocated to Cotonou, Benin Republic to begin slowly traveling across the West African region.

His key purpose is to deliver talks, seminars and workshops on his key areas of focus and interest to interested audiences (Email tayo at tksola dot com for details).

In a previous life, before leaving to become self-employed, Tayo served for seven years (October 1994 to December 2001) as a high performing manager in Guinness Nigeria. He rose from Shift Brewer to Training & Technical Development Manager, and later acted in senior roles as Production Manager and Technical Manager.

In addition to constantly challenging the status quo and influencing positive work changes, he built a reputation for using self-taught spreadsheet programming skills (starting with Lotus 1-2-3, and later moving to Excel Visual Basic) – in his spare time – to develop Automated Spreadsheet Applications to computerize manual report generation processes in the departments he worked. Over four(4) of his applications were adopted for brewery level reporting.

Tayo holds a B.Sc degree in Agricultural Extension Services from the University of Ibadan, Nigeria, having graduated top of his class – with Second Class Upper Division honors – in 1992. He is an Associate Member of the UK Institute & Guild of Brewing, a 1997 National Finalist of the Nigerian Institute of Management’s(NIM) Young Managers’ competition, a Certified Psychometric Test Administrator for Psytech UK, innovator of Spontaneous Coaching for Self-Development™ (SCfS-D™), and Founder of the Self-Development Academy (SDAc).

When he’s not amazing clients with his superhuman skills (wink), Tayo works as the creative force behind his Daily Self-Development Nuggets blog – on which he also publishes The Farm CEO Weekly Newspaper (sent via email to paid subscribers) and his Weekly Performance Improvement IDEAS newsletter.

You can connect with him on Twitter @tksola.com and Facebook.

Visit Tayo Solagbade Dot Com, to download over over 10 performance improvement resources to boost your personal and work related productivity.

====
[IMPORTANT NOTE:====

On 4th May 2014, Tayo’s 9 year old domain (Spontaneousdevelopment dot com), which hosted his website, was taken over by Aplus.net.

Within a few days however, Tayo used his advanced self-taught web development skills to build a SUPERIOR “reincarnation” of it the website http://www.tayosolagbade.com.

But updates are still ongoing to URLs bearing the old domain name in most of the over 1,000 web pages, and blog posts he’s published.

If you experience any difficulties finding a page or document, email Tayo at tksola dot com.

Click “Tayo, What Happened to SpontaneousDevelopmentDotCom ?” to read a detailed narrative about how the above event occurred :-))

Here’s an article Tayo wrote, to inspire others to defy adversity, and bounce back to even greater reckoning at what they do EVERY time:

And he wrote the one below, to explain why losing a domain name, no matter how old, NO LONGER determines your online success or otherwise:

A Proven Strategy to Find Profitable Buyers Regardless of Your Domain Name
==================

Home |About | Contact | SD Nuggets™ | Speaking/Web Marketing IDEAS | Web Marketing Systems | Freelance Writing | MS Excel® Heaven™ | Cost-Saving Farm Biz Ideas

Source: ExcelVB