4BUS1152-0105-2023-24
Technology in Business
Assignment Title
Version Number
and
Date of Release
Assignment Weight
Individual or
Group Work
Submission
Date and Time
Extensions
Submission
Method
Assignment Type
and Format
Assignment Detail
ASG1 -Using a spreadsheet to forecast sale data
Version 1
Release Date: Tuesday 13 February 2024
20% of the Module Grade
Individual Work
No extensions are allowed
Submit a single spreadsheet file, either:
Microsoft Excel with a file extension of .XLSX
Apple Numbers with a file extension of .numbers
no other spreadsheet format is acceptable
With a filename that follows the format:
Module code - ASG1 - student ID . XLSX
E.g. 4BUS1152-ASG1-12345678.XLSX
Substitute 12345678 with your real student ID
online, via the ASG1 Canvas assignment submission point
Using a spreadsheet, Microsoft Excel or Apple Numbers, perform statistical analysis on
the data sets provided (four products A, B, C and D) to create a one year future forecast
using the correct statistical method for only product A and B.
A. Finding your data.
1. Open the "ASG1 Data Set Allocation for Students" PDF file.
This file has not formatted to print on a single sheet of A4. It is best viewed on
ASG1 - Spreadsheet and Statistics
screen.
Locate your Student ID and make a note of the Data Set code number.
2. Open the "ASG1 Data Sets" PDF file.
This file has not formatted to print on a single sheet of A4. It is best viewed on
screen.
Each product has sales data that exhibit one of the 4 data patterns and has been
colour coded to help you identify the data pattern:
A. Constant Mean (green)
B. Trended: either Positive (blue) or Negative (red)
C. Step Change
D. Seasonal (yellow) with either Constant Mean or Trended underlying pattern
Locate your data tables for each of your 4 products (A, B, C and D).
You will need to type out the data manually in your assignment spreadsheet.
Page 1 of 3 Page 2 of 3
B. Create your spreadsheet.
Using the spreadsheet skills taught during this module, create a single spreadsheet file
to complete the following:
1. Raw Data Tab
Create a spreadsheet tab to hold the raw data for all 4 products (A, B, C and D)
Manually enter the data from your allocated data set.
Rename the tab with an appropriate name.
This will be the only place where raw data values will be held in your spreadsheet.
2. Product Tabs
Create a separate spreadsheet tab for each product (A, B, C and D) and rename the
tabs with appropriate names
3. For each product (A, B, C and D) on their own spreadsheet tab
a. create a "long" table and a "seasonality" table, using cell references; do NOT
use copy-and-paste. Annotate the table correctly.
b. Create charts for each table. Annotate the chart correctly.
4. Product A (Constant Mean)
a. Replicate the long table, using cell references; NOT copy-and-paste.
Calculate the Naïve, Moving Average (2, 3 and 4 QTRs) and Simple Average of
the past sales data.
b. Create a new "long" table with the old data and the predicted future data (from
the Simple Average method); the future data must be placed in a column to the
right of the old data.
Use cell references; do NOT use copy-and-paste. Annotate the table correctly.
c. Generate a chart, from the above table, to show the old data series as well as
the future data series, as predicted by the Simple Average method.
Format the future sales series as a dotted line to indicate that it is a prediction.
Annotate the chart correctly.
5. Product B (Trended)
a. Replicate the long table, using cell references; NOT copy-and-paste.
Calculate CORREL, SLOPE and INTERCEPT
b. Using the X-series of sequential numbers provided, create a new column of
predicted past data using regression with the equation of a straight line
y = mx + c
The predicted past data in the column to the right of the real past data.
c. Extending the X-series sequence of numbers for other year, predict the future
sales data using regression with the equation of a straight line y = mx + c
The predicted future data in the column to the right of the predicted past data.
d. Generate a chart, from the above table, showing the old (real) data series, the
old predicted sales data (by regression) and the future predicted data (by
regression). Format both the predicted past and predicted future sales series
as dotted lines.
6. Product C (Step Change)
a. Create a chart from the long table. Annotate the chart correctly.
b. Duplicate the chart (this is OK, as the new chart still picks up data from the
table) and squash the chart horizontally to accentuate the step change.
c. Create a new long table, but just for the data after the step change, using cell
references; NOT copy-and-paste.
d. Create a chart for the table above. Annotate the chart correctly.
7. Product D (Seasonal)
a. There is no further action required on this table.
4BUS1152 - TiB Marking Scheme
An indicative Marking Scheme may be subject to change
ASG1 - Spreadsheet and Statistics
Student
Sheets: Raw Data and Product Sheets
Raw Data found on one sheet and...
...it's the first one in the list
Each Product on their own sheet (tab)
Renaming the Sheet (tab) names so they have meaningful names
All data ultimately flows FROM the raw data
Tables
Layout, Annotations, Aesthetics and Calculations
Single Series
Mulit-series
Single Series - data points after step-change only
Charts
Layout, Annotations, Aesthetics and Calculations
Single Series
Mulit-series
Chart with all data points - normal width
Chart with all data points - Shrunk width
Chart with pre-step-change data points removed
Calculate Past Forecast
Layout, Annotations, Aesthetics and Calculations
Naïve Method (block size = 1)
Moving Average (block size = 2)
Moving Average (block size = 3)
Moving Average (block size = 4)
Simple Average (block size = all)
Regression - creating the values
CORREL function
Interpretation of R (narrative)
SLOPE function
INTERCEPT function
Regression - using cell addresses
Using m (slope)
as absolute cell address
Using c (intercept)
accentuate the step change
as absolute cell address
Using X from a numeric series
as a relative cell address
Calculating the Future Forecast
Layout, Annotations, Aesthetics and Calculations
Tables - single series
Charts - single series
4BUS1067-0906-2019-20-ASG5 Marking Scheme
Raw Data
141
Marks
arcentage Section
26% 36
21% 29
22% 31
22%
10%
31
14
1
4
5
25
20
4
4
1
20
4
4
1
1
10
2
2
2
2
1
1
1
1
1
1
1
1
1
1
1
10
0%
0%
0%
0%
0%
0%
.
0
0
Product A
Constant Mean
0
0
0
0
0
☐
☐
☐
0
Product B
Trended
0
0
0
0
U
0
☐
☐
[
■
■
■
0
Product C
Step-Change
0
0
0
0
0
0
L
☐
r
Product D
Seasonal
0
0
0
0
0
0
0
n
Page 3 of 3
