Sem III Unit 4 ITSB Assignment 10 2024 NEP Student

ST.
XAVIER’S COLLEGE (AUTONOMOUS), KOLKATA

Department of Information Technology
B. Com Semester III
IT Skills for Business Practical 2024 under NEP 2020
Unit 4: Advanced Data Analysis Techniques

Topics Covered: Time Series Analysis and Forecasting, Forecast Sheet
Assignment 10
The aim of this practical assignment is to develop proficiency in using spreadsheet applications for time
series analysis and forecasting. Students will learn to apply various methods to analyze historical data,
create forecasts, and interpret results using Excel's Forecast Sheet feature. The assignment will cover the
following.
• Understanding Time Series Analysis: Gain a comprehensive understanding of time series concepts
and their importance in data analysis and forecasting.
• Data Preparation: Learn to prepare and structure data appropriately for time series analysis and
forecasting.
• Creating Forecasts: Use Excel's Forecast Sheet tool to generate forecasts based on historical data,
including trend analysis and seasonality.
• Interpreting Forecast Results: Analyze and interpret forecast outputs to make data-driven
decisions and predictions about future trends.
• Visualizing Data: Create visual representations of time series data and forecast results to effectively
communicate findings and insights.
Open a blank workbook and save it as Spreadsheet10_rollno.xlsx in your folder.
1. A sales network analyses data on sales of goods by stores located in cities with a
population of fewer than 50,000 people. The period is for 2021-2024. Identify the
main development trend. Add the following data in timeseries_rollno sheet.
Year Quarter Sales
2021 1 $165000.00
2 $253000.00
3 $316000.00
4 $287000.00
2022 1 $257000.00
2 $308000.00
3 $376000.00
4 $351000.00
a. Append columns smoothed levels and Standard Errors. Apply exponential
smoothing along with data visualization for time series analysis. [Damping factor is the
coefficient of exponential smoothing (default is 0.3)]
b. Forecast sales using time-series data. Add a trendline to show actual product sales
volume. Configure the parameters of the trend line. Select polynomial trend that
minimizes the error of the forecast model. Set order of the polynomial to 6. Display
equation and R-Squared value on chart. Format the trendline.
c. Use linear trendline to forecast sales. Set the number of periods to 4 to see the
forecast on the chart. Display equation and R-Squared value on chart. Format the
trendline.
d. Append two columns for Forecast; Use growth function to predict the exponential
dependence and trend function to predict linear dependence.
2. Add the following data in forecast_linear_rollno sheet.
Period Sales
Page 1 of 2
ST. XAVIER’S COLLEGE (AUTONOMOUS), KOLKATA
Department of Information Technology
B. Com Semester III
IT Skills for Business Practical 2024 under NEP 2020
1 20
2 32
3 51
4 43
5 62
6 63
7 82
8 75
9 92
10 89
a. Use FORECAST.LINEAR function to predict future sales value for next three periods.
b. Visually display the linear trend (scatter plot with straight lines and markers ) along with a
linear trend.
3. Add the following data in forecast_ets_rollno sheet.
Period Visitors
1 74
2 59
3 51
4 42
5 83
6 67
7 54
8 44
9 87
10 68
11 55
12 49
a. Use FORECAST.ETS function (Exponential Triple Smoothing) to predict no. of
visitors for the next four periods (future value).
b. Visually display the seasonal pattern(scatter plot with straight lines and markers).
c. Use FORECAST.ETS.SEASONALITY function to find the length of the seasonal pattern.
4. Consider the same data inserted in forecast_ets_rollno sheet.
a. Use Forecast Sheet tool to automatically create a visual forecast worksheet (default
confidence level 95%; period ends at 16; set seasonality either detect automatically or set manually).
b. Predict minimum and maximum no. of visitors during the period 13 with 95%
confidence.
Reference Links:
https://support.microsoft.com/en-us/office/create-a-forecast-in-excel-for-windows-22c500da-6da7-
45e5-bfdc-60a7062329fd
*********************************************************************************************************
Page 2 of 2

Sem III Unit 4 ITSB Assignment 10 2024 NEP Student

Uploaded by

Copyright:

Available Formats

Sem III Unit 4 ITSB Assignment 10 2024 NEP Student

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Sem III Unit 4 ITSB Assignment 10 2024 NEP Student

Uploaded by

Copyright:

Available Formats

ST.

XAVIER’S COLLEGE (AUTONOMOUS), KOLKATA

Unit 4: Advanced Data Analysis Techniques

You might also like