Excel documents can get so big that they stop working. Some are critical to your business and have evolved alongside it, but now they are barely holding together.

This is usually due too many formulas and too much data. With the right approach almost any excel document can be restored to it’s former glory in performance.

Reduce the required formulas by doing more data processing when fetching the information. This puts less strain on Excel. When importing from a database, use the powerful processing power of the database server for calculations. Excel should only be used for final step calculations.

Consider reducing the amount of data being imported into Excel. Try filter out unnecessary data when querying a database, or aggregate data when possible. For example, you want to view your top 10 customers and do calculations on them. Instead of drawing every single customer out of the database into Excel, rather refine the query to identify the top 10 already. This will greatly speed up your document. If you know the numbers will only be analysed according to monthly aggregates, try aggregating the data with the database query, instead of importing every single transaction. To get this perfect, it is best to bring in someone with advanced information system skills.

Sometimes Excel reports need to be completely revamped. This can be difficult to do if your staff are use to the current document. The best approach is to get new eyes on the document, someone who can ask the right questions and propose a fresh and lean way of representing the data.

If you would like to know more on how to get your Excel documents efficient and working again, contact Forty One Business Solutions at info@fortyone.co.za. Alternatively see www.fortyone.co.za

It has never been easier to open or expand your business online. Platforms such as Woocommerce and Shopify have allowed small to medium business to open up online sales channels at a fraction of a cost from what it used to be years ago. Anyone can give it go themselves: Shopify.

Businesses have expanded this simplification of eCommerce even further. Forty One Business Solutions sets up your store for you, and trains you on the important parts to get you going. UAfrica have integrated courier quoting, allowing to manage deliveries with a click of a button. They even give you courier discount.

Error tracking and visibility can greatly minimize disruptions and enhance client satisfaction. A simple, yet very good practice is to add error catching in stored procedures.

SQL offers many functions in the scope of the catch block, that can return information about the error. Some examples are ERROR_PROCEDURE()  and ERROR_MESSAGE(), which return the name of the stored procedure and the error message respectively. You can read more about them here: https://msdn.microsoft.com/en-za/library/ms175976.aspx

The below example shows you what a stored procedure should look like that will catch and save error information:

CREATE proc dbo.sp_NameOfStoreProc
(
@parameters
)
AS
BEGIN

BEGIN TRY
/* Queries */
END TRY

/* Any errors will cause the catch section to execute */
BEGIN CATCH
/* Save the information about the error */
INSERT INTO dbo.ErrorLog VALUES (GETDATE(), ERROR_PROCEDURE(), ERROR_MESSAGE());
END CATCH

END

The table ErrorLog needs to be created by us. In this example, the error log table has columns for time of error, location of the error, and description of the error. You should add columns for any extra information that will help you troubleshoot.

Any time an error is encountered, it will be saved in the ErrorLog table. We can now also add mail notifications or views of the table to enhance error visibility.

I am a firm believer of setting goals. Be clear and realistic on what you want to achieve. A few years ago I was blessed with an opportunity to be part of a project with strong culture of setting goals and collaboration.

A top investment management firm was looking towards automating the distribution of their clients’ tax certificates. A few teams formed part of the project team. We were a team of four consultants, and our focus was on data source design, report design and report distribution methods based on client requirements.

The investment firm is extremely client-focused. For example, there was a person who complained that he did not get his son’s certificate the same day as his own. This person’s certificates were held back to ensure simultaneous distribution, despite the extra time taken by the investment firm.

There were many different rules that affected the outcome of a certificate. For example, a certificate could cover many pages, or had an incomplete address. The reports had to be very robust and dynamic.

We collaborated with the client’s business experts to develop understanding of the existing process. With this knowledge, we could design solutions and do resource planning with confidence. It was important to avoid workarounds and over-complications, because the reports were going to be reviewed each year. Clear communication with developers to find or add solutions to the reporting software kept schedule disruption to a minimum.

The first objective was to build a solution to stage the data for reporting. The data source would be an ORACLE database, where the previous solution used Microsoft SQL Server. Millions of transactions are stored in the live system every month, therefore due to time and performance constraints it was not feasible to generate tax certificates directly from the live system. The staging solution consisted of an automated daily import process, copying large amounts of data from the ORACLE database to a Microsoft SQL Server database. We added data verification checks, such as before and after aggregation checks. If any batch was out by even one decimal, the records would be removed and the batch investigated.

Once the data was staged successfully, TSQL queries were engineered to provide processed data for the reports. These queries were extremely complex. They were excellent opportunities to challenge all aspects of database and query design. Optimization was a key concern, as thousands of tax certificates had to be generated within hours. To achieve acceptable performance, queries were reviewed several times during the course of development. Indexes were added to tables to further reduce query times. We manage to reduce from around 6 seconds/report to less than 1 second/report.

The following testing techniques were applied to ensure a robust solution:

Staging of data:

• Expected aggregates of numerical data were tested against aggregates of imported data.
• Expected number of records was matched against actual number of records imported.

Reports:

• Random samples were given to fresh eyes from different departments for spot checks.
• Consultants identified extreme cases to test, such as long names, large values, many-paged reports etc.
• Consultants were challenged to find mistakes.

Distribution:

• Generation test runs of all tax certificates on server.
• Distribution test run of all certificates.
• Monitoring of mail server performance.
• Expected number of tax certificates generated was matched against actual number of tax certificates generated
• Expected number of mails to send matched against actual number of mails sent

The project was deemed very successful. Elements that lead to the success of the project:

• A few minutes were spent each morning setting and assigning goals for the day (daily stand up).
• Individual accountability.
• Effective collaboration between individuals and departments.
• Effective cross team collaboration.
• Defined project owner and project sponsor.
• Success of the project and client satisfaction was the most important to each individual.

Project Mobile Sales (MS), is an initiative to replace a manual invoicing system with an automatic, mobile one. It involves mobile phones and printers to that download sales and distribution information from an ERP system, controls stock exiting and entering the depot, invoices and credits customers on-the-go. It removes many manual steps which are prone to human error, and significantly speeds up information flow and accuracy.

Project MS was already implemented at two bottling companies from different countries: Uganda and Zambia. The first implementation in Uganda took 6 weeks. There were still many shortcomings that needed to be addressed.

I joined the project at the end of 2014 to assist in driving it. At the time, Botswana had a bottling company scheduled for Project MS. I faced the challenge of having to immediately implement the current version to a new company. Confidence from stakeholders were low due to difficult implementations of the past. The bottling company from Botswana had infrastructure challenges, leading to power and network failures. The first bottling company took 4 weeks to train 10 sales people, and the second one took 4 weeks to train 20 sales people. There were many additional hours of remote support and visits.

At the time of writing, we have cut down the implementations to 2 weeks for a company with 40 sales people. Current support averages 1 hour per month per company.

Strategies deployed to tackle fundamental flaws in the project:
Software Analysis and Design

• Data validation:  Solving complex problems would often require testing the whole system from start to finish to determine where information was lost. We added validation steps that would stop the process if any data failed validation. This allowed us to identify and act on problems before they reached customers.
• Simplify: The steps that a sales person (who was often very uneducated) had to take was overwhelming. The process was changed to follow a linear set of steps, that guided the sales person through the process.
• Information System: The server database structure was confusing and broken. The local database on the mobile phones suffered incompatibility issues. In the immediate term, we fixed several fields to reflect the correct information and removed redundancies. In the long term, we completely redesigned the server database, and changed the mobile database type to a much simpler one.

Business Strategies

• Stakeholder Cooperation: We put focus on establishing strong relationships with each stakeholder. This boosted stakeholders confidence in the project, which in turn strengthened cooperation and allowed us to make decision with more ease.
• Change Management: Client staff were very resistant to the change. We focused on benefits of the project, and empowered top staff members who embraced the change. This created acceptance in others. Once they saw their peers enjoying the benefits, the majority felt more inclined to change, and laggards feared being left behind.
• Improved Communication:There was a gap between what companies expected and what they actually got. We focused on bringing clarity to requirements, establish trust, establish urgency, and request sign-off on each component of the system.At the time of writing, the project is scheduled for deployment in three more bottling companies across Africa over the next few months. This equates to a 40%-50% growth.

Hello! My name is Marius. I have been fortunate to have worked at top South African financial institutions to manufacturing giants, but at the end of the day, the only thing that matters to me is leaving behind a smile.

I will be using this platform as a way to reflect on my past and current projects. I also want to raise opinions and views that I find interesting. These will be mostly business related, but I do not believe in setting anything in stone. Always be ready to adapt!

For now I leave this: There is no greater happiness than giving others happiness.