Hi there,

I need some help with my assignment. Please find attached.

Supply Chain Fundamentals 1. Graded Assignment: Fast Fashion Retailer The Fast Fashion Group (FFG) is a fast fashion retailer company located in Spain and is one of the largest retailers in the country (their overall sales has grown by about 50% in five years). FFG sells clothes and accessories. The customer is at the heart of its business model, which strives to link customer demand to manufacturing, and manufacturing to distribution. Maria, the Supply Chain manager of the company, is concerned about fast moving products and high volatility. Since the number of SKUs has grown dramatically in the last year, she believes that the SKUs should be segmented and has asked you to run an ABC analysis on a sample of 60 SKUs (all of them are jeans). Part 1 In the linked file (please see attached Excel-1), you will find the information you need regarding the unit cost, unit sales price, weekly sales volume, and weekly sales standard deviation for each SKU. All currencies are in Euros.Which SKU has the most volatile demand (highest coefficient of variation)? The coefficient of variation is defined as Cv=/. Just enter the SKU_ID. Part 2 When Mara saw the high volatility of this item, she asks you to analyze the volatility of a sample of 60 SKUs (different jeans models) in terms of Coefficient of Variation (CV). In particular, she wants to know the percentage of SKUs that have a CV greater than or equal to 0.45. Just enter a value and do not include the % sign. For example, if you think the value is 35.54%, just enter 35.5 (round to nearest 1 decimal places): Part 3 In addition to the volatility, you also propose to analyze the fast-moving items. You believe that the fast moving, highly volatile SKUs should be managed separately with more time spent on improving the forecasting capabilities. You select the criteria for fast moving to be selling 36 or more units per week and the criteria for highly volatile as having a CV greater than or equal to 0.45. What percentage of the total profit is contributed by these Fast Moving and Highly Volatile SKUs (both criteria satisfied)? Just enter a value and do not include the % sign. For example, if you think the value is 35.54%, just enter 35.5 (round to nearest 1 decimal places): Part 4 Let's run now the ABC analysis based on profitability. What percentage of the total profit is contributed by the top 11 SKUs of the 60, as ranked by profit? Just enter the value and do not include the % sign. For example, if you think the value is 35.54%, just enter 35.5 (round to nearest 1 decimal place): Part 5 What percentage of the total profit is contributed by the bottom 26 SKUs of the 60, ranked by profit? Just enter the value and do not include the % sign. For example, if you think the value is 35.54%, just enter 35.5 (round to nearest 1 decimal place): Part 6 Demand uncertainty, particularly in the fast fashion industry, is a significant concern. For this reason, Mara, the Supply Chain Manager of the Fast Fashion Group (FFG) has decided to pay attention to the distribution of the demand of several of their items, and she asks for your help. She wants you to start analyzing one of their basics jeans, the "regular fit straight blue jeans" (SKU #J65991X). The weekly demand for this item is found to be normally distributed, with a mean of 50 units and a standard deviation of 23 units. (Note that this SKU is not in the data set.) If you stocked 66 units of the \"regular fit straight blue jeans\" (SKU #J65991X) for the first week, what is the probability that the demand during week 1 exceeds the stocking level? Enter the probability as a fraction, e.g. as a number between 0 and 1 with 4 decimal digits (e.g. for 12.34%, enter 0.1234 as your answer). Part 7 How many \"regular fit straight blue jeans\" (SKU #J65991X) do you need to have in stock to reduce the probability that the demand exceeds the stocking level to 16.0%? Enter your answer as an integer. Part 8 A few regular customers have asked FFG for ultra stretch blue jeans. Mara has agreed to stock it, even though sales for these items have been slow. After analyzing past data, you have found that a Poisson distribution with a mean of 2.1 closely fits the weekly sales pattern for this model of jeans. The cost of the SKU #J19901X (ultra stretch blue jeans) is $18.25 and the unit sales price is $39.99. What is the probability of selling 0 ultra stretch blue jeans (SKU #J19901X) in a week? Enter the probability as a fraction, e.g. as a number between 0 and 1 with 4 decimal digits (e.g. for 12.34%, enter 0.1234 as your answer). Part 9 After seeing the previous result, Mara decided to stock only 5 units of ultra stretch blue jeans every week. What is the probability that your demand for SKU #J19901X exceeds the level set by Mara of 5 units? Enter the probability as a fraction, e.g. as a number between 0 and 1 with 4 decimal digits (e.g. for 12.34%, enter 0.1234 as your answer). Part 10 What is the probability that your demand for SKU #J19901X will be no less than 1 unit and no more than 5 units? Enter the probability as a fraction, e.g. as a number between 0 and 1 with 4 decimal digits (e.g. for 12.34%, enter 0.1234 as your answer). 2. Graded Assignment: Forecasting Fenway Franks Hot dogs are an integral part of the experience of a Red Sox baseball game in Boston. The hot dogs, or frankfurters, that are sold at the ball park are called Fenway Franks. During the regular season, a total of 81 games are played at Fenway Park, each one bringing around 37 thousand fans. Some games bring more fans than others: attendance depends on many factors, from the weather to the reputation of the opposing team. Demand for hot dogs depends not only on attendance, but also on many other factors, such as temperature or the time of the day when the game takes place. Although it is not a simple task, estimating the demand for hot dogs during each one of the 81 games of a season is very important for concessions at Fenway. On the one hand, underestimating the demand would mean - besides unsatisfied and hungry fans - lost profit: the cost of a lost sale is estimated at $3. On the other hand, overestimating the demand would mean an excess of unsold hot dogs at the end of the game: the cost of an unsold hot dog, including ingredients, energy and labor, is estimated at $2. A year ago, a group of concessions at Fenway hired three consulting firms for preparing mathematical models, one by each firm, to predict the demand for hot dogs during each game of the season based on multiple factors that are known as late as just two days before the game: air temperature, rain, the opposing team, time of day for the game, injured players who would be absent, and dozens of other factors. After a year of frantic data crunching, the consultants have presented their final models to the Fenway concessions. You have been hired as an independent expert to evaluate the quality of the forecasts produced by their proposed models. Due to intellectual property issues, you are not allowed to see the actual models, only the forecasts they produced. They are, for your purposes, to be treated as black boxes. As input data, you have been given the forecast that each model yielded for hot dogs demand in each game in the two most recent seasons. You have also been given by the concessions a detailed record of the actual demand for hot dogs in each of the 81 games of these two seasons. Your task now is to help the Fenway Park concessions evaluate and compare the quality of these competing forecasting approaches. Please enter all your numerical answers with at least 4 significant figures. Part 1 Download the spreadsheet with the data (Excel - 2). Recall that the definition of the error term is the Actual demand minus the Forecasted demand. For the data you have been given... ...what is the mean deviation (MD) for Model 1? ...what is the mean deviation (MD) for Model 2? ...what is the mean deviation (MD) for Model 3? Part 2 For the data you have been given... ...what is the mean absolute deviation (MAD) for Model 1? ...what is the mean absolute deviation (MAD) for Model 2? ...what is the mean absolute deviation (MAD) for Model 3? Part 3 For the data you have been given... ...what is the root mean square error (RMSE) for Model 1? ...what is the root mean square error (RMSE) for Model 2? ...what is the root mean square error (RMSE) for Model 3? Part 4 Based on the data of the actual demand, and the estimates of each model, we can calculate for each model the money that would have been lost in terms of lost sales and also overage costs, over the two year period, compared to an ideal situation where all demand is perfectly satisfied with no lost sales and no overage. How much money, in dollars, would have been lost over the last two years in both lost sales and overage costs... ...if we had used Model 1 to estimate demand? ...if we had used Model 2 to estimate demand? ...if we had used Model 3 to estimate demand? Part 5 Based on your calculations, which models do you think tend to over-forecast demand? Select all correct answers Model 1 tends to over-forecast demand Model 2 tends to over-forecast demand Model 3 tends to over-forecast demand None of the models tend to over-forecast Based on your calculations, which models do you think tend to under-forecast demand? Select all correct answers Model 1 tends to under-forecast demand Model 2 tends to under-forecast demand Model 3 tends to under-forecast demand None of the models tend to under-forecast Part 6 Of the three proposed models, which one displays the most bias? Select the best answer Model 1 is most biased Model 2 is most biased Model 3 is most biased Of the three proposed models, which one displays the least bias? Select the best answer Model 1 is least biased Model 2 is least biased Model 3 is least biased Part 7 Of the three proposed models, which model is the most accurate? Select the best answer Model 1 is the most accurate Model 2 is the most accurate Model 3 is the most accurate Of the three proposed models, which model is the least accurate? Select the best answer Model 1 is the least accurate Model 2 is the least accurate Model 3 is the least accurate Part 8 Considering both the estimated lost sale costs and the estimated overage costs that would have resulted from using each of the three models, and assuming that the past is representative of the future... ...which model would you most recommend to be used in the future by the Fenway Park concessions, in order to maximize profits? Select the best answer Model 1 is the best Model 2 is the best Model 3 is the best ...and which model would you least recommend? Select the best answer Model 1 is the worst Model 2 is the worst Model 3 is the worst Data on SKUs SKU J67011 J84412 J34113 J82314 J37715 J77216 J22917 J74918 J24619 J96720 J50921 J49022 J40323 J41824 J16025 J27426 J76827 J73128 J77929 J71530 J91931 J38732 J20533 J68134 J52335 J42936 J97437 J83538 J22939 J51640 J42641 J20842 J74443 J36144 J22045 J31946 J24347 J89148 J69649 J36950 J39851 J13852 J25353 J27454 Cost 9.86 22 7.22 25.28 24.2 18.29 12.97 17.47 14.93 16 26.7 25.57 25.79 14.82 13.4 9.35 29.36 14.86 13.59 27.15 17.86 12.86 10.9 7.1 20.61 21.16 5.1 18.94 24.83 28.2 27.03 24.35 12.17 28.38 7.17 18.79 16.16 21.57 22.71 19.27 6.71 28.06 5.1 22.41 Price 41.12 32.45 45.01 59.78 37.85 52.95 41.15 46.2 44.24 28.12 57.98 47.15 65.32 32.55 33.57 49.31 53.87 30.27 31.94 55.23 55.27 22.99 34.04 25.39 35.21 49.54 20.85 35.41 50.12 52.56 57.85 42.71 30.73 67.84 29.18 58.64 48.68 31.71 49.59 30.93 45.07 52.22 38.86 38.58 Sales 24.4 51.8 22.7 73.9 14.4 34.5 42.5 73.5 42.2 82.7 21.8 11 36.5 43.9 53 72.9 7.2 7.8 48.4 7.2 42.7 21.7 36.5 8.2 65.6 27.7 56.6 58.5 45.7 49.6 29 65.1 76.6 2.9 5.3 63.4 83.6 23.2 65.5 80.5 6.5 68.2 33.5 29.7 Dev 17.6 47.6 8.3 37.3 5.2 11.9 33.2 49.6 19.6 67.3 5.7 1.7 11.8 16.4 44 32.1 6.7 3.4 17.2 3.3 5.4 17 4.2 4.8 51 24 6.3 30.7 16.1 27.4 9.7 52.6 64.4 0.5 2.2 50.1 12.9 3.6 47.7 30.6 6.1 8.9 25.1 12.4 J34955 J70156 J75657 J22158 J98759 J77860 J90661 J20562 J55263 J79964 J41865 J72166 J55467 J66868 J78369 J24770 11.99 11.41 25.03 6.68 13.76 11.95 17.11 9 27.68 5.5 12.16 20.28 29.98 24.55 12.44 27.96 24.71 30.22 52.62 29.84 49.44 25.65 27.2 45.68 59.08 36.06 38.26 32.08 55.51 34.62 33.23 42.27 5.8 13.7 62.9 67.2 75.5 63.8 71.8 56.9 28.2 38.9 34.2 22.5 57.8 9.8 6.8 80.8 1.8 10.8 34 45.2 24.6 20.3 24.7 29.1 23.9 31.2 23.1 17.9 21.4 2.7 4.1 13.6 Data on SKUs Threshold for Threshold for Threshold for Threshold for Q2 Q3 Q4 Q5 0.45 36 11 26 Games Game 1 Game 2 Game 3 Game 4 Game 5 Game 6 Game 7 Game 8 Game 9 Game 10 Game 11 Game 12 Game 13 Game 14 Game 15 Game 16 Game 17 Game 18 Game 19 Game 20 Game 21 Game 22 Game 23 Game 24 Game 25 Game 26 Game 27 Game 28 Game 29 Game 30 Game 31 Game 32 Game 33 Game 34 Game 35 Game 36 Game 37 Game 38 Game 39 Game 40 Game 41 Game 42 Game 43 Game 44 Game 45 Game 46 Game 47 Actual 3885 3351 3790 2814 3207 3683 3510 2599 3883 3201 3083 3351 3809 2814 3826 3785 3047 2657 2868 3036 3751 3092 3441 2628 3771 3173 3221 3947 3687 3410 2866 3249 2952 3344 3288 3163 3878 3381 3459 3884 3983 3290 2743 3234 3038 3246 3516 Model 1 Model 2 Model 3 3791 3855 3601 2999 3396 3411 3322 4075 3599 2289 3034 2801 3211 3568 3192 3740 3730 3456 3446 3526 3480 2235 2607 2588 3570 4109 3714 3138 3304 3158 3056 3361 2891 3263 3651 3044 3575 3795 3531 2748 3128 2554 3533 4106 3861 3425 4094 3524 2823 3041 2936 2242 2638 2635 2903 3305 2708 2720 3017 2790 3710 3803 3512 3059 3109 2823 2944 3513 3210 2131 2812 2445 3236 3839 3563 3193 3456 2954 2812 3348 3264 3415 4298 3677 3205 4098 3444 2899 3394 3254 2881 2912 2797 2996 3683 2955 2667 2940 2662 3391 3583 3094 3289 3556 3305 2808 3200 3075 3572 4069 3838 3348 3699 3206 3422 3868 3487 3355 3912 3884 3541 4176 3905 3134 3456 3281 2539 2829 2592 2857 3558 2947 2908 3159 3071 2749 3567 3271 2980 3648 3447 Game 48 Game 49 Game 50 Game 51 Game 52 Game 53 Game 54 Game 55 Game 56 Game 57 Game 58 Game 59 Game 60 Game 61 Game 62 Game 63 Game 64 Game 65 Game 66 Game 67 Game 68 Game 69 Game 70 Game 71 Game 72 Game 73 Game 74 Game 75 Game 76 Game 77 Game 78 Game 79 Game 80 Game 81 Game 1 Game 2 Game 3 Game 4 Game 5 Game 6 Game 7 Game 8 Game 9 Game 10 Game 11 Game 12 Game 13 Game 14 2993 2581 3173 3964 3804 3317 2860 3789 2657 4027 4015 3334 2604 2634 2763 3127 3151 3642 4086 3516 3292 3659 3301 3399 3028 3051 3446 2731 3511 3512 3846 3471 3019 2700 3084 3077 2778 3449 2897 2943 4074 3474 3476 2774 2644 3169 3935 2882 2914 2335 2867 3457 3342 3025 2562 3256 2318 4000 3702 3282 2090 2101 2594 3021 3031 3316 3615 3461 3101 3290 2785 3210 2757 2799 3200 2424 3278 3310 3721 3299 2893 2632 2738 2577 2421 2941 2907 2811 3606 3368 3237 2570 2261 2642 3703 2756 2991 2625 3579 4020 4065 3658 3115 3834 2643 4451 4246 3532 2792 2798 3135 3158 3267 3980 4235 3505 3434 4021 3502 3744 3455 3026 3488 2759 3689 3832 4279 3615 3188 2700 3177 3351 3105 3536 3137 3332 4461 3493 3687 3154 2635 3331 4097 3121 2772 2435 3168 3833 3545 3130 2630 3700 2640 4034 3854 3101 2377 2587 2625 2849 3207 3472 4076 3429 3026 3396 3265 3149 2946 3004 3321 2746 3338 3215 3907 3320 2810 2605 3023 2813 2691 3505 2632 2942 3941 3508 3231 2647 2661 3098 3898 2780 Game 15 Game 16 Game 17 Game 18 Game 19 Game 20 Game 21 Game 22 Game 23 Game 24 Game 25 Game 26 Game 27 Game 28 Game 29 Game 30 Game 31 Game 32 Game 33 Game 34 Game 35 Game 36 Game 37 Game 38 Game 39 Game 40 Game 41 Game 42 Game 43 Game 44 Game 45 Game 46 Game 47 Game 48 Game 49 Game 50 Game 51 Game 52 Game 53 Game 54 Game 55 Game 56 Game 57 Game 58 Game 59 Game 60 Game 61 Game 62 3672 3288 3254 3198 3357 3834 3501 3253 3966 3151 3473 3234 2664 3506 2955 3275 3186 4075 3946 3660 3393 3742 3528 3795 2878 2974 2740 3352 2833 2637 3705 3021 3636 3425 3097 3008 3726 2813 2588 3727 3954 3724 3123 3385 2904 3224 2961 4083 3218 2852 3248 2784 3055 3318 3439 2952 3557 3097 3222 3093 2422 3500 2863 2912 2656 3768 3802 3637 3152 3528 3039 3618 2889 2469 2479 3088 2473 2500 3231 3066 3159 3074 3093 2974 3302 2542 2571 3441 3461 3361 2971 3340 2634 3119 2766 3752 3922 3702 3268 3500 3613 3902 3543 3246 4261 3133 3857 3489 3003 3663 3265 3283 3233 4179 4192 3888 3427 3760 3617 4191 2971 2949 3018 3431 2857 3020 4026 3018 3799 3454 3105 3236 3772 3020 2610 4142 3959 4025 3464 3402 2965 3577 3332 4073 3485 2998 3304 3212 3339 3581 3288 3180 4015 3023 3187 3190 2374 3431 2935 2982 3068 3959 3679 3521 3211 3705 3336 3594 2832 2933 2780 3355 2598 2683 3520 2789 3652 3149 2802 2998 3443 2752 2306 3490 3770 3452 2995 3245 2755 3203 2776 3837 Game 63 Game 64 Game 65 Game 66 Game 67 Game 68 Game 69 Game 70 Game 71 Game 72 Game 73 Game 74 Game 75 Game 76 Game 77 Game 78 Game 79 Game 80 Game 81 3257 4021 2686 3405 2810 2609 2761 3453 3267 3363 3478 3748 3276 2675 3427 2821 2739 2747 3588 3096 3757 2321 3245 2504 2121 2424 3084 2914 2888 3026 3598 2975 2338 3472 2493 2667 2455 3573 3635 4449 2825 3788 3098 2706 2994 3855 3358 3481 3688 4182 3564 3107 3807 2802 3176 2807 3682 3203 3837 2597 3095 2606 2605 2733 3206 3032 3415 3309 3568 3024 2735 3139 2841 2466 2766 3567