Excel Development Techniques
We have been using our Fast Excel Development Methodology for a number of years. Systems developed with it are in daily use by manufacturing companies world-wide, and we teach it in our hands-on workshops, and with our on-line eLearning modules.
It is aimed at Excel literate people who have become frustrated with the traditional IT approach, and its failure to deliver. Once you have mastered the skills, you can turn your own ideas into working systems in a fraction of the time.
Here is an overview to the proven pathway for Fast Excel Development:
Integrate with system data
No data are stored in an Excel system. Planning systems query ERP database tables or exported text files for a single version of the truth. Input data are compiled from master data tables such as Item Master, Orderbook, BOM, Routing, Calendars together with parameters and settings that control the function.
Separate data flow, calculation and reporting
Systems are designed to separate out the flow of data from processing, calculation and reporting. This ensures efficient processing and a robust design where users only access necessary functions.
A typical system will have multiple users and each may have a different role. The data are stored in a single place, usually in the database of the host system. They are brought into Excel using flat tables and calculation sheets will query them to perform the result.
Define logic in a single place
Formulae that carry logic and calculations are defined in location, applied to ten-thousands of like records and then replaced with values. This technique vastly reduces the risk of error and keeps file-size small and manageable.
The definition of calculations is usually kept in formula and these are specified in a single place above the table of data where they are applied. A macro will apply the formula to the entire table of data and then replace the results with values. This means the calculation files are kept small and manageable, and the results stable to a controlled process flow.
Calculate in normalized tables
Most excel users wish to read data in a matrix- computers need tables. Fast Excel Development uses normalised data and pivot tables to calculate in efficient structure and report data in a way that people like to see.
Normalised tables specify a systematic structure for data with each fact relationship appearing only once. For instance, the Item Master carries Item attributes and any calculation or report that needs them will query this table.
Each worksheet will typically pull in table data, perform calculations and then export the results to another table. Much of the system is manipulating tables of data, applying formula and presenting it to the next process.
Conventions for processing data in a flow
A factory brings in raw materials and components, processes it and delivers the finished product to the customer. There is a logical flow where each step needs to be performed in the right sequence. Building intelligence in a planning system is the same.
Automating with macros
Each action is performed in development and then automated in a macro. Macros can be recorded with no need for Visual Basic code. Powerful VBA function is available in pre-built modules that can offer optimal functions with a familiar user interface.