Data Modelling Using Spreadsheets By Lewis Bullen HowData Modelling can be used to find solutions?Thestages involved in the decision-making processThere are several stages involved in the decision-makingprocess which will make a substantial difference in the final data model, forexample if you do not fully understand what you are creating the spreadsheetfor there could be inconsistencies with the remainder of the data model, evenif you understand all the other stages which are involved in the decision-makingprocess.· Understandingthe ScenarioThe scenario is the outline for everything relating tothe task, it will state all the specifications and requirements that the clientwishes to be incorporated into the data model, or could even state that it mustbe created in the form of a data model.
Understanding the scenario is key to making sure youcreate the data model properly, and willmake the difference between your spreadsheet beinguseful or not to the client. The first stage of the process of understanding thescenario is to know who will be using the spreadsheet and what the purpose ofit is, whether it be for a business to manage staff information, log incomingorders, etc. and what should be in the spreadsheet for example data comparisonsto prove a point between external data that has been gathered and the client’sdata.
The second stage of understanding the scenario is tomake sure the spreadsheet helps the clientsdecision making process, you will be ableto do this by making sure you have considered all viable alternative solutionsand have gave them the best options forthe external sources that have been chosen. An example of not understanding the scenario is if thespreadsheet is being used to decide how much a new product should cost, theywill have to compare the data to that of their competitors by includinginformation such as how much the product costs them to create, how much competitorscharge for a similar product and show how using alternative pricing structureswill impact them – whether it will encourage people to buy their products ornot. · Identifyinginformation and sourcesIdentifying the data that you have been given, throughthe scenario or additional data that you have found is a key factor whencreating a data model, you will need to make sure that you have reviewed all ofthe various factors regarding the data to make sure that it is current,reliable and does not contain any inaccurate data. v Required InformationThisis the data that is required for the spreadsheet to function and work properlyin accordance with the scenario.
For example, if you want to create predictionchart to show how a product may sell, you will need to rely on past data forexample from past products sold by the company or from public information. v Pre-existing InformationSomeof the information is already defined and is already provided in the scenario,which can be inserted into the data model.Anexample of the data which is provided by the scenario may be costs of theproduct that you are creating, how much it could be sold for and then datacould then gather into a chart to show the point of how a product could sell. v Additional Data requiredInsome cases, additional information may be a requirement, whether due to a lackof information being available in the scenario or due to comparisons being madefrom data that is found online. Additionalinformation could be found online, however with thisyou will have to be careful as with data being found online may not be current,contain anomalies or the data may not be relevant to the content that you havemade. Anexample of some of the data that you may have to find is if you are creating achart that historically measures weather and shows how it is changing overtime, you would be able to put this information into a graph to make this dataeasy to read.
· FactorsAffecting the Quality of InformationThe accuracy of the data is important, as it willpotentially be used to make important decisions by the client such as pricing aproduct, if the data being used to gather these results is invalid it couldlead to a product being priced either too low or unreasonably high which mayaffect the sales of a product. v Currency of DataThecurrency of the data relates to how recent the data was gathered, for exampleusing data from a few years ago won’t be reliable and more than likely is to beout of date.Thecurrency of data is a key factor withinyour data model, it is how recent the data that has been gathered is.
Ifthe data is not recent and a few years out date it can affect how the data canbe read and how the data can be used, as a result of this it will affect the decision-makingprocess. v Accuracy of DataTheaccuracy of data is data which is provided in an easy to read format this canbe done by ensuring that the data contains no errors and that there are noobvious mistakes within the database which may affect how the data could beused, or presented. v External FactorsAnexternal factor is a factor which affects your data and is something which isnot related to your data model but may still impact the data within the model,for example a competitor releasing aproduct of their own similar to yours, this may be an external factor affectingyour sales. · AnalysinginformationThe analysis of the data in your data model is one ofthe most important aspects, it relates to the data that needs to be collectedand how it is processed to fit the requirements and the scenario.
· AlternativeSources of InformationOnce information has been analysed it will be noticed that there will bea variety of different solutions that can be used to solve a problem, beforeimplementing a solution you should make sure all options are looked atcarefully and make sure you look at all the factors when comparing the data. Factors that should be considered is the way you will present the data,if you could change your plans to work around the data and to consider how datacan be calculated. · Consequencesof using Alternative SourcesOnce an alternative source of information/data has been identified adecision should be made on what data should be use, using the factors mentionedpreviously as a measure to decide as well as considering the quality of theinformation, the currency of the information and the accuracy of theinformation. · Making andJustifying DecisionsAfter all the consequences of using alternative sources have beenidentified you will then be able to decide the best piece of information thatfits the scenarioIt should be easy for you to decide the most practical pieces of content,as you will have the positives and negatives of all the alternatives. The decision of the piece of information that you decide to use will haveto be justified, to ensure you fully understand the consequences of eachseparate alternative piece of information. · CommunicatingDecisions to Othersv ClientThe client is the personwho has instructed you to create this data model, in the form of the scenario.The client will set out all of the requirements, and will need to be keptinformed of any decisions which are made regarding the data model for examplethe sources of information that you are using.You will be able toupdate the client in a variety of different methods, whether it be in the formof regular emails, phone calls or biweekly meetings to update them regardingyour progress on creating their data model.
v SupervisorThe supervisor will needmore regular updates than the client, and will need to be notified of anypotential issues which are affecting the creation of the data model for examplethe sources of information or lack of features within the spreadsheet softwareto do what you wish to do.The supervisor willmostly be updated in the form of face to face communication, compared to howyou will typically communicate with the client – via email, or biweeklymeetings. v Project Sponsor/Finance ManagerThe project sponsor is the person whois managing, controlling and/or providing the budget for the project.
Yoursupervisor will usually be the person communicating with the sponsor givingthem regular updates regarding the process of the creation of the data model. How are features of spreadsheet software used to support thedecision-making process?There are many distinctive features in the various pieces ofspreadsheet software, namely Microsoft Excel and Google Sheets which have beencreated with the purpose of making it easy for businesses to manage data,without the need for large amounts of staff to write information domain.Spreadsheet software is helpful as it makes it easy forbusinesses to get shortened version of data, which will allow them to easilymake decisions. For example, a business could find information about how manycustomers have bought a product from them within the past 6 months and put thatdata in a chart to look for any abnormalities in the data for example from datasubmitted incorrectly by their staff.
KeySpreadsheet Software Functionality· Insertingand modifying dataWith all basic pieces of spreadsheet software,inserting data is a key requirement as it is one of the primary purposes of thesoftware – which is to allow people to easily create spreadsheets and allow theuser/client to modify the data which is stored on that spreadsheet over timewhile automatically altering other aspects of the software such as Graphs,while keeping validation and formatting throughout the data inserted. Every cell has a reference that can easily beidentified by looking at the axis for example the highlighted cell below is A1,the cell next to it is B1. · FormattingDataThere is a variety of ways that data can be formatted from within Exceland other spreadsheet software, an example of this could be changing the font, the size, font weight, font colour or the appearance of the cell. · FormulasFormulas are one of the key features within Excel which help users easilycreate calculations for example multiplying, diving, subtracting and addingdifferent rows together without much effort, without Formulas it would make ita lot more difficult to make calculations, create easily readable data withoutusing alternate software or even manually creating them.
This formula shows that this data is being multiplied together to showthe turnover for the month of January, rather than it being manually entered –that way if there was an anomaly with the data they won’t need to calculate itagain, it will just be recalculated by the spreadsheet software. · FunctionsFunctions are usually used with Formulas and are useful for allowing youto compare data to change the visual representation such as font colour, fontweight, etc. to identify data that may be different such as if profits arelower on one specific month you can make it read, which will make it easier forthe client to view. An example of a function as shown above is if the netprofit is less than 0 the cell format and font will be red, to show that it isa negative.
This is helpful and can help with the decision-making process as itallows this data to be immediately noticeable to the client, this will allow abusiness to identify what may have gone wrong and make any necessary changessuch as verifying the data entered. · ChartsCharts are a useful feature within spreadsheetsoftware, it allows you to create an easy to look at the graph to look at and compare your data with anyalternative pieces of information from different sources that you may have.Charts usually can be complicated to make withoutsoftware, if you have to calculate all the data together – then hand draw achart it will be more time consuming compared to how easy it is to create withspreadsheet software such as Excel (a few seconds, to create from a recommendedchart), they are harder to share, and it is easier to make mistakes which maymisrepresent your data. Analysis of Data Modelling and how it can be used in the Decision-MakingProcess The features that have previously been mentioned are basicand are now expected features for spreadsheet software, however, there are several features which are not included orrequired for spreadsheet software but enhances the experience for businessesand users of the software.
CollaborativeWorkingWith spreadsheet software such as Google Sheets, users areable to work collaboratively on spreadsheets, this will allow all the as to beexpected spreadsheet features to be used in real time and allow users to makechanges and add data in real time, whether this be inserting data, modifyingdata, creating functions, etc.This feature will massively help businesses, as traditionalsoftware such as Excel does not support collaborative working and software suchas Google Sheets will allow multiple users to update their spreadsheets in realtime, without emailing or unreliable systems of sharing your Excel file whichonly allows one user to open and modify it at the same time, or maintainingmultiple spreadsheets with the same and/or similar data on different computer.Another benefit of using cloud-based software such as Google Sheets will be that yourspreadsheet is available wherever you have access to the internet, makechanges, review data – without installing any software on your computer to viewthe spreadsheet which makes it more user friendly, however, the software does have restrictions as not all featureswhich make Excel one of the best platforms for editing spreadsheets will beavailable due to restrictions with the cloud-basedplatforms.
Goal SeekGoal Seek is a tool which is in Microsoft Excel which allows youto compare multiple items of data compares to and impacts another piece ofdata.An example of using Goal Seek is if you are polling visitorson your website, asking them about a new product that you are selling, if acertain product receives 2/3 of all of the votes which are made it will besold, or one will be given away. After the poll has ended, it ends with thevotes being mixed with 64% of thevotes being for Product 1 and theremaining 36% of votes being for Product 2.You will be able to use Goal Seek to find further informationwhich helps you analyse all of the data for example how many users would haveneeded to be converted to vote for the winning Product, if more users voted for a specific product would it have won,etc. In summary Goal Seek allows you to analyse all of the dataand ask more questions easily, without having to create complicated functionsto calculate this for you. Evaluation of Data Modelling and how it can impact the designand quality of Spreadsheets Data Modelling is a really useful tool which helps businessesmanage data, whether it be financial, customer feedback or any other type ofinformation which the company wishes to compare or manage. There are many advantages and disadvantages to usingspreadsheet software, and the different types of data which is being usedwithin Excel. Advantages· Spreadsheets are easy to create for businesses, theycan easily layout and import all of their data into Excel (or any otherspreadsheet software) with a minimal effort.
· Spreadsheets will allow you to create graphs with thedata you have imported, this will make it user-friendlyand easy to compare with other data, whether it is on an external site or asecondary piece of information that you have imported into Excel. · Spreadsheets can allow businesses to analyse data andoutput easy to read values which can give you further information which you mayotherwise be unable to find with your data for example users on your websitevoting for a new product, you can find out statistics which you are notcollecting directly from your users.Disadvantages· Human error can be a massive problem when it comes toinserting data into Excel, it can be easily rectified compared to using othermethods such as Pen and Paper to store data however if a single value isentered incorrectly it can damage decisions which can be made by a business forexample too much or too low of a value being entered it could lead to eitherincorrect product pricing, incorrect predictions or anything else that could bedirectly affected by invalid data being in the spreadsheet. · Excel is not primarily designed for collaboration, itis becoming more and more open to collaboration with platforms such as GoogleSheets however the initial conception of Excel was for an easily manageablepiece of spreadsheet software without thinking about multiple people workingtogether in real time, which can massively affect how the files can be sharedbetween multiple users without the same data being stored on multiple devices. · Spreadsheets can be inefficient for large businesses,or even for some medium-sized businesses.
Specifically, for the problems with collaboration, as medium to large sized businesses will have multiple users wantingto update the document at the same time, which won’t be possible unless theywish to use a feature limited option such as Google Sheets which could causesecurity issues if there is sensitive information in the document.