Scribd Logo
Upload

Welcome to Scribd!

  • 13 views

    MSC301 Sec04 Assignment02 EXCEL

    Uploaded by

    Asif Mahmood
    The document shows sales data for different days of the week over a 3 month period, including total sales, averages, and market shares each day. It also provides the simple and weighted averages of the market shares for each day. Finally, it gives a forecast for the coming week using the simple average method.

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd

    MSC301 Sec04 Assignment02 EXCEL

    Uploaded by

    Asif Mahmood
    13 views10 pages
    The document shows sales data for different days of the week over a 3 month period, including total sales, averages, and market shares each day. It also provides the simple and weighted averages of the market shares for each day. Finally, it gives a forecast for the coming week using the simple average method.

    Original Title

    15104113_MSC301_Sec04_Assignment02_EXCEL.xlsx

    Copyright

    © © All Rights Reserved

    Available Formats

    XLSX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document shows sales data for different days of the week over a 3 month period, including total sales, averages, and market shares each day. It also provides the simple and weighted averages of the market shares for each day. Finally, it gives a forecast for the coming week using the simple average method.

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd
    Download as xlsx, pdf, or txt
    13 views10 pages

    MSC301 Sec04 Assignment02 EXCEL

    Uploaded by

    Asif Mahmood
    The document shows sales data for different days of the week over a 3 month period, including total sales, averages, and market shares each day. It also provides the simple and weighted averages of the market shares for each day. Finally, it gives a forecast for the coming week using the simple average method.

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd
    Download as xlsx, pdf, or txt
    of 10

    Question 12

    1 2 3 4 5 6 Total 7
    Fri 149 154 152 150 159 163 927
    Sat 250 255 260 268 273 276 1582
    Sun 166 162 171 173 176 183 1031
    Total 565 571 583 591 608 622
    Avg 188.33 190.33 194.33 197.00 202.67 207.33

    1 2 3 4 5 6
    Fri 0.79 0.81 0.78 0.76 0.78 0.79
    Sat 1.33 1.34 1.34 1.36 1.35 1.33
    Sun 0.88 0.85 0.88 0.88 0.87 0.88 Answer of 12 (a)
    Total 3 3 3 3 3 3 SR
    MA (fri) 0.79 0.78 0.78 0.78 0.783
    MA (sat) 1.34 1.35 1.35 1.35 1.344
    MA (sun) 0.87 0.87 0.88 0.88 0.873

    Answer of 12 (b)
    SA (fri) 0.79 0.81 0.78 0.76 0.78 0.79 0.786
    SA (sat) 1.33 1.34 1.34 1.36 1.35 1.33 1.341
    SA (sun) 0.88 0.85 0.88 0.88 0.87 0.88 0.874

    Answer of question (c)


    Forecast of week 7 = 633.4
    using simple average method
    Fri 165.90
    Sat 283.05
    Sun 184.45
    Answer of 12 (a)

    Answer of 12 (b)
    A. In my opinion I would say it’s a non linear trend. Although it might seem linear but I think it would be trend adjusted expone

    B. Yes this would cause concern becasue I would not know the actual demand for the pain reliever.

    Day Number Sold


    1 36
    2 38 Number Sold
    3 42
    60
    4 44
    5 48 50
    6 49
    7 50 40
    8 49
    9 52 30
    10 48
    20
    11 52
    12 55
    10
    13 54
    14 56 0
    0 2 4 6 8 10 12
    15 57

    C. Trend Adjust Forecast Smoothing constant is given = 0.3


    Day Actual Demand Forecasted TAF Trend Estimate Initial forecast for week 8 is 50
    8 49 50 49.70 2
    9 52 51.70 51.79
    10 48 53.70 51.99
    11 52 53.93 53.35
    12 55 54.77 54.84
    13 54 56.09 55.46
    14 56 56.74 56.52
    15 57 57.60 57.42
    16 58.43
    MSE
    be trend adjusted exponential smoothing.

    ber Sold

    8 10 12 14 16

    ng constant is given = 0.3


    orecast for week 8 is 50
    Year
    Quarter 1 2 3
    1 11 14 17
    2 20 23 26
    3 29 32 35
    4 38 41 44
    Total 98 110 122
    Avg 32.67 36.67 40.67

    Relatives Year
    Quarter 1 2 3
    1 2.97 2.62 2.39
    2 1.63 1.59 1.56
    3 1.13 1.15 1.16
    4 0.86 0.89 0.92

    A. answer
    Simple Average Relatives
    Q1 2.66
    Q2 1.60
    Q3 1.14
    Q4 0.89
    X Y XY X^2 Estimated |(A-F)| y= mX+c
    1 50 50 1 50.16 0.16
    2 54 108 4 53.66 0.34 m=
    3 57 171 9 57.16 0.16 c=
    4 60 240 16 60.66 0.66
    5 64 320 25 64.16 0.16 So, y = 3.50X+46.66
    6 67 402 36 67.66 0.66
    7 71.5 500.5 49 71.16 0.34
    8 76 608 64 74.66 1.34
    9 79 711 81 78.16 0.84
    10 82 820 100 81.66 0.34
    11 85 935 121 85.16 0.16
    12 87 1044 144 88.66 1.66
    13 92 1196 169 92.16 0.16
    14 96 1344 196 95.66 0.34
    Total 105 1020.5 8449.5 1015 7.32

    Mean Absolute Deviation (MAD) 0.56

    Forecast for week 15 99.16 ±7.32


    Forecast for week 16 102.66 ±7.33
    Forecast for week 17 106.16 ±7.34
    Forecast for week 18 109.66 ±7.35
    3.50
    46.66

    So, y = 3.50X+46.66
    Period Forecast Estimate
    9 42
    10 43
    11 42
    12 49
    13 43
    14 44
    15 43.33
    16 52
    17 44.44
    18 44.44
    A
    Year Quarter Y1 Y2 Variation Y3 = Y2- Variation
    1 1 60 72 12 84
    2 45 51 6 57
    3 100 112 12 124
    4 75 85 10 95

    Year
    3
    B
    Year Quarter Y1 Y2 Variation Y3 = Y2- Variation
    2 1 95 85 -10 75
    2 85 75 -10 65
    3 92 85 -7 78
    4 65 50 -15 35

    1 93 102 9 111
    2 90 75 -15 60
    3 110 110 0 110
    4 90 100 10 110

    You might also like