Small Business Services
Ian Marshall has built up a wealth of experience from his management background and during the past ten years of working on Office Answers and other small businesses. Ian typically works three days a week on Office Answers and two days at other small businesses. Ian has worked as a consultant for, or been a director of, a range of small businesses including startups, venture capital funded companies, and small but established companies in both the telecoms market and electronics manufacturing.
Ian describes some of his experience and projects in the sections below and is always interested in discussing new projects. If you are interested in discussing any of these areas further please call Ian on 01628 460 230.
123 Insight MRP Software Implementation and Reporting
I worked with an electronics manufacturing company to implement the MRP software from 123 Insight. The company has a complex range of products and a parts list of around 5000 components. Each product is serial numbered and several products combine sub-assemblies requiring serial number linkage. Twenty years worth of production records were stored in Excel spreadsheets and I wrote macros to extract the required information into the import format for the 123 MRP software. These macros also pulled data from the company’s Access parts database including supplier and manufacturer codes. This process was tested many times leading up to the go live date to the point where a data freeze was only required for four hours to migrate the day to day operation to the 123 MRP software.
Leading up to and since the live date I have created a number of php web pages, Excel and Dashboard style reports for the day to day users and the management team. The 123 MRP database stores all the information required but users often want flexibility on how it is presented which is not possible with the 123 MRP application. For examples of the reports created please see the detailed page 123 MRP Software Implementation and Reporting
Web page reports from SQL
The reporting of the 123 MRP data described above is an example of the type of report that can be created but plenty of other projects have been carried out as well. An example is a website designed for a golf society which, while not a commercial application, shows how database data can be pulled into a web page.
The golf society runs ten events in a year and has an annual competition for the best three rounds over the year. The scores for each event are entered into a SQL database and the php web page code below pulls the best three scores for each player from the database, calculates and sorts by the total, and displays the results so each player can see where they obtained their best scores.
Excel and Advanced Macros
Excel is my favourite tool and I would describe myself as an Excel Black Belt. I have years of experience in writing Excel Macros and VBA code to automate tasks. This has been driven from my background in the telecoms sector where call data needs to be analysed.
An example comes from the billing systems at Office Answers that I wrote myself. Each morning an Excel spreadsheet opens at a scheduled time and performs the following functions automatically:
- connects to multiple FTP servers and downloads call data from the previous day
- sorts the call data by customer to create an summary pivot table
- compares each customer’s usage to predefined credit limits
- sends an email to the management team with details of customers near or over their limit
In the 123 implementation project Excel macros were used extensively to extract data from the spreadsheets that had been used historically. By taking this approach the process could be repeated several times during testing while the spreadsheets were still used in the live environment. We got to the point where the macros could provide the required import files for 123 within two hours, meaning we only had to have a data freeze for about four hours in total.
At Office Answers we only deal with small companies and many are in their startup phase. When offering to send potential customers an email summarising our service it is amazing how many still have [email protected] email addresses. If I ask them what they are doing about a website it is usually something that is giving them hassle on their to do list. For this reason I started offering to build simple websites for these customers as part of our overall service. Nothing too fancy, just a simple but professional website with three or four pages aimed at getting them up and running.
For more information see the Websites for Startups page