Spreadsheets and databases are both amazing tools, but they have very different purposes. If your spreadsheets are doing more than crunching numbers, or they are getting far too complex, its time to rethink. Vendor supplied solutions and custom Software Development are both worth consideration. At Vaillus IT, we know what tool to use when. Give us a call at 918-734-7215. Let’s have a conversation about the best way to organize one of your business’s most strategic assets: its data!
Starting Point
Existing spreadsheets are a very good place to start when considering a software development project. But that doesn’t mean every spreadsheet needs to be an application. Spreadsheets have a very useful role to play in just about any business. Know when to use them for their maximum benefit and avoid them where they become a problem.
Apply the first rule of thumb for when to use a spreadsheet: there needs to be some useful result at the end of the session. If you are collecting data, but not expecting a result anytime soon, choose a database instead. Engage a software development professional in your IT group if you have one. If not, then let Vaillus IT expertly build a database design and a set of intuitive forms and reports.
Number Crunching
Spreadsheets are the master move for number crunching. They provide insight into the state of the business and help you make informed decisions instead of educated guesses. Keep refining your work, but don’t plan for the data to be useful beyond the current session. The value in the spreadsheet is in its formulas and logic as well as its formatted output. Enter new data during each session and let your spreadsheet fulfill its purpose of generating the expected output. If you are collecting data without any expected result, a spreadsheet is the wrong tool.
Hint, Hint
Spreadsheets have a wide array of built in functions for performing mathematical and financial calculations. Take a hint from this key feature to understand why spreadsheet applications exist and how they are meant to be used as high-end number crunchers. Outfitting cells with complex formulas that combine cell references to multiple pieces of data, built-in functions, and simple math operations are the bread and butter of proper spreadsheets. Use them to turn raw data into knowledge and insight for running your business efficiently.
Spreadsheets’ True Value
Again, the value in a spreadsheet is in its ability to calculate. The data in the spreadsheet is of no particular value, so plan to replace some or all of it in each session. The data that is input to a spreadsheet can come from many sources. It may be downloaded from a line of business application such as an Accounting System or online portal. It may be in summary information from another spreadsheet or report from an application. Spreadsheets are also the modeling tools that let you test out what if scenarios, entering data that might be true under certain circumstances. For example, if you raise your prices by 3%, and your expenses one of my 2%, what is your expected margin?
Simple to Complex Over Time
Spreadsheets that started out solving a simple problem then to grow into something very complex are common. Maintaining elaborate formulas quickly becomes unwieldy. In addition, the output itself becomes valuable in its own right and needs to be tracked over time. When this happens, undertake a software development project to simplify the calculations, save the output for later use to a database, and provide insight across time periods.
Capture historical data and log files is another common use of spreadsheets. The content is not stored in the spreadsheet application’s native format such as an Excel spreadsheet file. A file contains just the text with no formatting, typically as a comma delimited file. The spreadsheet accesses this data to make it easy to store and analyze the data. Don’t plan on accessing this data routinely. Choose to use a database solution instead if you find that you refer to it routinely or it has historical significance.
Connect to Live Data
Connect a spreadsheet to a live set of data in a database, such as your Accounting Software, by using an advanced feature of Microsoft Excel called Data Connections. The data refreshes to reflect the most recent content whenever the spreadsheet is opened. The spreadsheet performs its calculations on this read-only linked data just as if you had entered it. Charts and graphs update to reflect this new data as well. Make spreadsheets a useful companion to your line-of-business application’s built-in report writing tools by using connected data.
Expert Software Development
Call Vaillus IT at 918-734-7215. We can help you get the right tools in place to manage one of your business’s most strategic assets: its data. Let our expert team build a solution that fits your business.