Data Modelling Using Spreadsheets
Data Modelling can be used to find solutions?
stages involved in the decision-making process
There are several stages involved in the decision-making
process which will make a substantial difference in the final data model, for
example if you do not fully understand what you are creating the spreadsheet
for there could be inconsistencies with the remainder of the data model, even
if you understand all the other stages which are involved in the decision-making
The scenario is the outline for everything relating to
the task, it will state all the specifications and requirements that the client
wishes to be incorporated into the data model, or could even state that it must
be created in the form of a data model.
Understanding the scenario is key to making sure you
create the data model properly, and will
make the difference between your spreadsheet being
useful or not to the client.
The first stage of the process of understanding the
scenario is to know who will be using the spreadsheet and what the purpose of
it is, whether it be for a business to manage staff information, log incoming
orders, etc. and what should be in the spreadsheet for example data comparisons
to prove a point between external data that has been gathered and the client’s
The second stage of understanding the scenario is to
make sure the spreadsheet helps the clients
decision making process, you will be able
to do this by making sure you have considered all viable alternative solutions
and have gave them the best options for
the external sources that have been chosen.
An example of not understanding the scenario is if the
spreadsheet is being used to decide how much a new product should cost, they
will have to compare the data to that of their competitors by including
information such as how much the product costs them to create, how much competitors
charge for a similar product and show how using alternative pricing structures
will impact them – whether it will encourage people to buy their products or
information and sources
Identifying the data that you have been given, through
the scenario or additional data that you have found is a key factor when
creating a data model, you will need to make sure that you have reviewed all of
the various factors regarding the data to make sure that it is current,
reliable and does not contain any inaccurate data.
v Required Information
is the data that is required for the spreadsheet to function and work properly
in accordance with the scenario. For example, if you want to create prediction
chart to show how a product may sell, you will need to rely on past data for
example from past products sold by the company or from public information.
v Pre-existing Information
of the information is already defined and is already provided in the scenario,
which can be inserted into the data model.
example of the data which is provided by the scenario may be costs of the
product that you are creating, how much it could be sold for and then data
could then gather into a chart to show the point of how a product could sell.
v Additional Data required
some cases, additional information may be a requirement, whether due to a lack
of information being available in the scenario or due to comparisons being made
from data that is found online.
information could be found online, however with this
you 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 have
example of some of the data that you may have to find is if you are creating a
chart that historically measures weather and shows how it is changing over
time, you would be able to put this information into a graph to make this data
easy to read.
Affecting the Quality of Information
The accuracy of the data is important, as it will
potentially be used to make important decisions by the client such as pricing a
product, if the data being used to gather these results is invalid it could
lead to a product being priced either too low or unreasonably high which may
affect the sales of a product.
v Currency of Data
currency of the data relates to how recent the data was gathered, for example
using data from a few years ago won’t be reliable and more than likely is to be
out of date.
currency of data is a key factor within
your data model, it is how recent the data that has been gathered is.
the data is not recent and a few years out date it can affect how the data can
be read and how the data can be used, as a result of this it will affect the decision-making
v Accuracy of Data
accuracy of data is data which is provided in an easy to read format this can
be done by ensuring that the data contains no errors and that there are no
obvious mistakes within the database which may affect how the data could be
used, or presented.
v External Factors
external factor is a factor which affects your data and is something which is
not related to your data model but may still impact the data within the model,
for example a competitor releasing a
product of their own similar to yours, this may be an external factor affecting
The analysis of the data in your data model is one of
the most important aspects, it relates to the data that needs to be collected
and how it is processed to fit the requirements and the scenario.
Sources of Information
Once information has been analysed it will be noticed that there will be
a variety of different solutions that can be used to solve a problem, before
implementing a solution you should make sure all options are looked at
carefully 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 data
can be calculated.
of using Alternative Sources
Once an alternative source of information/data has been identified a
decision should be made on what data should be use, using the factors mentioned
previously as a measure to decide as well as considering the quality of the
information, the currency of the information and the accuracy of the
After all the consequences of using alternative sources have been
identified you will then be able to decide the best piece of information that
fits the scenario
It 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 have
to be justified, to ensure you fully understand the consequences of each
separate alternative piece of information.
Decisions to Others
The client is the person
who 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 kept
informed of any decisions which are made regarding the data model for example
the sources of information that you are using.
You will be able to
update the client in a variety of different methods, whether it be in the form
of regular emails, phone calls or biweekly meetings to update them regarding
your progress on creating their data model.
The supervisor will need
more regular updates than the client, and will need to be notified of any
potential issues which are affecting the creation of the data model for example
the sources of information or lack of features within the spreadsheet software
to do what you wish to do.
The supervisor will
mostly be updated in the form of face to face communication, compared to how
you will typically communicate with the client – via email, or biweekly
v Project Sponsor/Finance Manager
The project sponsor is the person who
is managing, controlling and/or providing the budget for the project. Your
supervisor will usually be the person communicating with the sponsor giving
them regular updates regarding the process of the creation of the data model.
How are features of spreadsheet software used to support the
There are many distinctive features in the various pieces of
spreadsheet software, namely Microsoft Excel and Google Sheets which have been
created 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 for
businesses to get shortened version of data, which will allow them to easily
make decisions. For example, a business could find information about how many
customers have bought a product from them within the past 6 months and put that
data in a chart to look for any abnormalities in the data for example from data
submitted incorrectly by their staff.
Spreadsheet Software Functionality
and modifying data
With all basic pieces of spreadsheet software,
inserting data is a key requirement as it is one of the primary purposes of the
software – which is to allow people to easily create spreadsheets and allow the
user/client to modify the data which is stored on that spreadsheet over time
while 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 be
identified by looking at the axis for example the highlighted cell below is A1,
the cell next to it is B1.
There is a variety of ways that data can be formatted from within Excel
and other spreadsheet software, an example of this could be changing the font, the size, font weight, font colour or the appearance of the cell.
Formulas are one of the key features within Excel which help users easily
create calculations for example multiplying, diving, subtracting and adding
different rows together without much effort, without Formulas it would make it
a lot more difficult to make calculations, create easily readable data without
using alternate software or even manually creating them.
This formula shows that this data is being multiplied together to show
the 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 it
again, it will just be recalculated by the spreadsheet software.
Functions are usually used with Formulas and are useful for allowing you
to compare data to change the visual representation such as font colour, font
weight, etc. to identify data that may be different such as if profits are
lower on one specific month you can make it read, which will make it easier for
the client to view.
An example of a function as shown above is if the net
profit is less than 0 the cell format and font will be red, to show that it is
a negative. This is helpful and can help with the decision-making process as it
allows this data to be immediately noticeable to the client, this will allow a
business to identify what may have gone wrong and make any necessary changes
such as verifying the data entered.
Charts are a useful feature within spreadsheet
software, it allows you to create an easy to look at the graph to look at and compare your data with any
alternative pieces of information from different sources that you may have.
Charts usually can be complicated to make without
software, if you have to calculate all the data together – then hand draw a
chart it will be more time consuming compared to how easy it is to create with
spreadsheet software such as Excel (a few seconds, to create from a recommended
chart), they are harder to share, and it is easier to make mistakes which may
misrepresent your data.
Analysis of Data Modelling and how it can be used in the Decision-Making
The features that have previously been mentioned are basic
and are now expected features for spreadsheet software, however, there are several features which are not included or
required for spreadsheet software but enhances the experience for businesses
and users of the software.
With spreadsheet software such as Google Sheets, users are
able to work collaboratively on spreadsheets, this will allow all the as to be
expected spreadsheet features to be used in real time and allow users to make
changes and add data in real time, whether this be inserting data, modifying
data, creating functions, etc.
This feature will massively help businesses, as traditional
software such as Excel does not support collaborative working and software such
as Google Sheets will allow multiple users to update their spreadsheets in real
time, without emailing or unreliable systems of sharing your Excel file which
only allows one user to open and modify it at the same time, or maintaining
multiple 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 your
spreadsheet is available wherever you have access to the internet, make
changes, review data – without installing any software on your computer to view
the spreadsheet which makes it more user friendly, however, the software does have restrictions as not all features
which make Excel one of the best platforms for editing spreadsheets will be
available due to restrictions with the cloud-based
Goal Seek is a tool which is in Microsoft Excel which allows you
to compare multiple items of data compares to and impacts another piece of
An example of using Goal Seek is if you are polling visitors
on your website, asking them about a new product that you are selling, if a
certain product receives 2/3 of all of the votes which are made it will be
sold, or one will be given away. After the poll has ended, it ends with the
votes being mixed with 64% of the
votes being for Product 1 and the
remaining 36% of votes being for Product 2.
You will be able to use Goal Seek to find further information
which helps you analyse all of the data for example how many users would have
needed to be converted to vote for the winning Product, if more users voted for a specific product would it have won,
In summary Goal Seek allows you to analyse all of the data
and ask more questions easily, without having to create complicated functions
to calculate this for you.
Evaluation of Data Modelling and how it can impact the design
and quality of Spreadsheets
Data Modelling is a really useful tool which helps businesses
manage data, whether it be financial, customer feedback or any other type of
information which the company wishes to compare or manage.
There are many advantages and disadvantages to using
spreadsheet software, and the different types of data which is being used
Spreadsheets are easy to create for businesses, they
can easily layout and import all of their data into Excel (or any other
spreadsheet software) with a minimal effort.
Spreadsheets will allow you to create graphs with the
data you have imported, this will make it user-friendly
and easy to compare with other data, whether it is on an external site or a
secondary piece of information that you have imported into Excel.
Spreadsheets can allow businesses to analyse data and
output easy to read values which can give you further information which you may
otherwise be unable to find with your data for example users on your website
voting for a new product, you can find out statistics which you are not
collecting directly from your users.
Human error can be a massive problem when it comes to
inserting data into Excel, it can be easily rectified compared to using other
methods such as Pen and Paper to store data however if a single value is
entered incorrectly it can damage decisions which can be made by a business for
example too much or too low of a value being entered it could lead to either
incorrect product pricing, incorrect predictions or anything else that could be
directly affected by invalid data being in the spreadsheet.
Excel is not primarily designed for collaboration, it
is becoming more and more open to collaboration with platforms such as Google
Sheets however the initial conception of Excel was for an easily manageable
piece of spreadsheet software without thinking about multiple people working
together in real time, which can massively affect how the files can be shared
between 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 wanting
to update the document at the same time, which won’t be possible unless they
wish to use a feature limited option such as Google Sheets which could cause
security issues if there is sensitive information in the document.