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

Solved: 4BUS1152-0105-2023-24 Technology in Business Assignment Title Version

Home
questions and answers
4bus1152 0105 2023 24 technology in business assignment title version

Question

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