Data Analysis and Forecasting using MS Excel: A Comprehensive Report

Desklib provides solved assignments and past papers to help students succeed.

Table of Contents
Main Body..................................................................................................................................2
References..................................................................................................................................6
List of Figures
Figure 1: Table Form.................................................................................................................2
Figure 2: Column Chart.............................................................................................................2
Figure 3: Line Chart...................................................................................................................3
Figure 4: Calculations................................................................................................................3
Figure 5: Forecast Model...........................................................................................................5

Main Body
Here, the tabular data represents the temperature, barometer, weather, visibility, wind, and
humidity of ten consecutive days. Different days contains different information about the data
and the whole data here is collected and represented in the form of a table:
Figure 1: Table Form
Charts
It is considered to be as a powerful tool that helps in displaying the data in visual form. The
two types of charts are used in order to represent the data present in the tabular form.
Column Chart
Figure 2: Column Chart

The above-made chart is the Column chart that is used to represent the data is the visualized
form (Halpern et al., 2018). Column charts help in displaying the data where vertical bars go
across the charts horizontally. Here, the x-axis is representing the days whereas the y-axis
shows the representation of the temperature.
Line Chart
Figure 3: Line Chart
The above-made chart is the Line chart that is also used to represent the data is the visualized
form (Deochand et al., 2015). Line charts help in displaying the data and data is compared in
the form of the line showing various points. Here, the x-axis is representing the days whereas
the y-axis shows the representation of the wind.
Calculations
This part contains the different calculations such as calculation of the mean, median, mode,
ranges, and standard deviation. All the calculations are done by the inbuilt formulas present
in the Excel.

Figure 4: Calculations
a). Mean
Step number 1 – Data is to be selected.
Step number 2 - Formula is to be written with the help of the inbuilt functions present in MS
Excel.
=Average (number1, number2…)
Mean (Temperature) – 4.18
Mean (Wind) – 8.72
Mean (Humidity) - 0.91
Mean (Barometer) - 1021.909
b). Median
Step number 1 – Data is to be selected
Step number 2 - Formula is to be written with the help of the inbuilt functions present in MS
Excel.
=Median (number1, number2…)

Median (Temperature) - 3
Median (Wind) is- 7
Median (Humidity) is- 0.93
Median (Barometer) is- 1023.5
iii). Mode
Step number 1 – Data is to be selected
Step number 2 - Formula is to be written with the help of the inbuilt functions present in MS
Excel.
=Mode (number1, number2…)
Mode (Temperature) - 3
Mode (Wind) - 7
Mode (Humidity) - 0.93
iv). Range
Step number 1 – Data is to be selected
Step number 2 - Formula is to be written with the help of the inbuilt functions present in MS
Excel.
Low Range Formula
=Min (number1, number2…)
High Range Formula
=Max (number1, number2…)
Low Range (Temperature) - 0
High Range (Temperature) - 8
Low Range (Wind) - 2
High Range (Wind) - 19

Low Range (Humidity) - 0.76
High Range (Humidity) - 1
Low Range (Barometer) - 1001
High Range (Barometer) - 1038
v). Standard Deviation
Step number 1 – Data is to be selected
Step number 2 - Formula is to be written with the help of the inbuilt functions present in MS
Excel.
=stdev (number1, number2…)
Standard Deviation calculated for the Temperature is- 2.72
Standard Deviation calculated for the Wind is- 6.04
Standard Deviation calculated for the Humidity is- 0.081
Standard Deviation calculated for the Humidity is- 12.547
Forecasting Model
a). The equation is given as y=mx+c. Firstly, the value of m is calculated here. The value of
m is taken as the temperature of data. Using this, forecast formula is easily used and the value
of x is inserted here.
b). C is considered a constant value. This is the value that can also be zero.
c) In this part, temperature for the 15th and the 23rd day is to be calculated using the forecast
formula.
=forecast(x, known x’s values, known y’s values)

Figure 5: Forecast Model

References
 Halpern, A.M., Frye, S.L. and Marzzacco, C.J., 2018. Scientific Data Analysis Toolkit: A
Versatile Add-in to Microsoft Excel for Windows.
 Deochand, N., Costello, M.S. and Fuqua, R.W., 2015. Phase‐change lines, scale breaks,
and trend lines using Excel 2013. Journal of Applied Behavior Analysis, 48(2), pp.478-
493.