The Excel file, LLBean.xls (download from the class folder) contains demand and forecast data for 84 items. Suppose that these are the data that LL Bean will use to plan their next season. Consider an item that retails for $45 and costs LL Bean $25. The liquidation price for this item is estimated to be $12.5. The sales forecast for this item is 11,000 units. What order quantity should LL Bean choose for this item? (Follow the method outlined in the L.L.Bean case using the Actual/Forecast ratios.) Hint: There are many ways to arrive at the correct answer. One way involves the use of the PERCENTILE function in Excel. Even if you have not used this function before, it is easy to learn.

AutoSave a Off L_L_Bean - Compatibility Mode - Excel Search (Alt+ Justin Prasad JP File Home Insert Draw Page Layout Formulas Data Review View Help Comments Share v Insert Arial 25 10 Y ' ' A A == X 9 General ab EM 0 47 O 92 Delete Paste 40 00 Analyze y y y $ %) % y y Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter Select Format Data Undo Clipboard Font Alignment Number Cells Editing Analysis K L M M N 0 P R S S T guBHbbB C40 Xfx 3323 B D D E F G . 1 J 1 Sample Data for L L Bean containing demand forecasts and actual demand for 85 items 2 Item Actual 3 Number Forecast Demand 4 1 1153 768 5 2 205 33 6 3 1135 928 7 4 4 285 359 8 8 5 1930 2417 9 6 1800 1962 10 7 202 11 8 705 725 12 9 754 1043 13 10 466 176 14 11 805 533 15 12 1212 1340 16 13 740 1067 17 14 1103 508 18 15 1504 1890 19 16 1567 1387 20 17 1028 807 21 18 1981 235 22 19 1608 1256 20 1523 765 24 21 351 541 Sheet2 + - Ready * Accessibility: Unavailable 3 a + 100% AutoSave Of H LLBean - Compatibility Mode - Excel Search (Alt+ Justin Prasad JP File Home Insert Draw Page Layout Formulas Data Review View Help Comments Share X T v Arial == 10 - A A ' ' Insert DO 25 General ab ITI AY 0 LO 9X Delete Paste DOM U 60 y y $ %, 800 y Conditional Format as Cell Formatting Table Table Styles Styles v Sort & Find & Filter Select Analyze Data Y Format Cells Undo Clipboard Font Alignment Number Editing Analysis C40 VX fx 3323 A E F G H 1 J K L M M N 0 P R S T T 1523 351 840 1012 114 1864 3986 2627 2703 4750 3177 3740 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 19 B 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 16 Sheet2 2062 D 765 541 419 1223 77 1807 3433 2935 2952 4722 3582 3574 2668 2619 6131 3109 2976 3325 2349 4589 2153 2699 2764 4962 3771 2222 2542 4897 2588 3150 3323 2575 4858 2483 2734 2970 4543 3395 2696 + Ready Accessibility: Unavailable + 100% AutoSave Off LL Bean - Compatibility Mode - Excel Search Alt+ Justin Prasad JP File Home Insert Draw Page Layout Formulas Data Review View Help Comments Share LUI! Insert y X 25 Arial 10 - A A = = ce General TI D AY 2X Delete Paste I U OnA y 00 = $ % 8.8 y y Conditional Format as Cell Formatting Table Table Styles Styles Sort & Find & Filter Select Analyze Data Y Format Cells Undo Clipboard Font Alignment Number Editing Analysis C40 V X fx 3323 A E F G H 1 J J K L M N 0 Q R S T 46 47 48 49 50 51 52 53 54 55 56 67 68 59 60 61 62 63 64 65 66 67 68 69 70 711 B 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 C 45431 3395 2686 23111 4544 2823 2095 2529 51691 6678 7986 73151 9083 9043 8680 5099 5946 6522 9942 8203 7502 8939 7753 7522 D 4962 3771 2322 3089 5416 2700 2069 3206 5701 7456) 8954 6135 9183 10514 8661 6533 7080 6088 12060 7351 8736 11138 8288 7275 7522 102.16 9099 Sheet2 02181 + Ready Accessibility: Unavailable u + 100% AutoSave Off LL_Bean - Compatibility Mode - Excel Search (Alt+O) Justin Prasad JP File Home Insert Draw Page Layout Formulas Data Review View Help Comments Share X ULL y Arial 10 - A ' ' Insert = = y General ab CO) 49 9X Delete Paste U A += 60 $ % 98 Y Z Sort & Find & Filter Select 00 0 Analyze y Conditional Format as Cell Formatting Table Table Styles Styles Format Data Undo Clipboard Font Alignment Number Cells Editing Analysis C40 VIX fx 3323 A E F G H 1 J K L M M N N 0 R S T A 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 B 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 C 8939 77531 75221 90991 9218 6067 7495 7937 9753 6902 7089 8948 11850 106291 10933 156711 11207 14436 16147 16890 18652 D 111381 8288 7275 7522 10215 5452 8772 6956 9403 6849 6303 7872 15300 9323 11407 18126 10337 10700 17391 16892 17873 Sheet2 + Ready Accessibility: Unavailable + 100%