Spreadsheet: 330465d1404755248-min-if-with-several-conditions-lejanco-oeldere-330458d1404754484-min-if-with-several-conditions-min-if-conditional-question
Sheet: Summary Sheet

YIAH INVENTORY ANALYSIS |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | <--DO NOT CHANGE
[NAME] SKUs YTD |  |  |  |  |  |  | AUS Top Selling SKUs 2013
Item Number | Country | Type | Retail Price | Unit Sales 2014 | Retail Sales |  | Item Number | Country | Type | Retail Price | Unit Sales 2013 | Retail Sales
 | U.S. |  |  |  |  |  |  | Australia
 | U.S. |  |  |  |  |  |  | Australia
 | U.S. |  |  |  |  |  |  | Australia
 | U.S. |  |  |  |  |  |  | Australia
 | U.S. |  |  |  |  |  |  | Australia
[NAME] SKUs YTD |  |  |  |  |  |  | [NAME] Moving SKUs AUS
Item No | Country | Type | Unit Sales 2014 | RETAIL | Retail Sales |  | Item No | Country | Type | Unit Sales 2013 | RETAIL | Retail Sales
 | U.S. |  |  |  |  |  | 302 | Australia
 | U.S. |  |  |  |  |  | 548 | Australia
 | U.S. |  |  |  |  |  | 338 | Australia
 | U.S. |  |  |  |  |  | 352 | Australia
 | U.S. |  |  |  |  |  | 891 | Australia
 | U.S. |  |  |  |  |  | 625 | Australia
 | U.S. |  |  |  |  |  | 347 | Australia
 | U.S. |  |  |  |  |  | 343 | Australia
 | U.S. |  |  |  |  |  | 617 | Australia
 | U.S. |  |  |  |  |  | 423 | Australia
 | 1
Top Inventory turnover Analysis (on hand plus expected) |  |  |  |  |  |  | Bottom Inventory Turnover Analysis (on hand plus expected)
Item No | Country | Type | Total cuml. Sales | Inventory Turnover Ratio |  |  | Item No | Country | Type | Total cuml. Sales | Inventory Turnover Ratio
 | U.S. |  |  |  |  |  |  | U.S.
 | U.S. |  |  |  |  |  |  | U.S.
 | U.S. |  |  |  |  |  |  | U.S.
 | U.S. |  |  |  |  |  |  | U.S.
 | U.S. |  |  |  |  |  |  | U.S.
[NAME] by % of Inventory Sold |  |  |  |  |  |  | [NAME] by % of Inventory Sold
Item No | Country | Type | Beginning Inventory | Total Cuml. Sale | % of inventory Sold |  | Item No | Country | Type | Beginning Inventory | Total Cuml. Sale | % of inventory Sold
 | U.S. |  |  |  |  |  |  | U.S.
 | U.S. |  |  |  |  |  |  | U.S.
 | U.S. |  |  |  |  |  |  | U.S.
 | U.S. |  |  |  |  |  |  | U.S.
 | U.S. |  |  |  |  |  |  | U.S.
Inventory on hand to Sales (Low) |  |  |  |  |  |  | Inventory on hand to sales (High)
Item No | Country | Type | Beginning Inventory | Total Cuml. Sale | Inventory on hand to Sales ratio |  | Item No | Country | Type | Beginning Inventory | Total Cuml. Sale | Inventory on hand to Sales ratio
 | U.S. |  |  |  |  |  |  | U.S.
 | U.S. |  |  |  |  |  |  | U.S.
 | U.S. |  |  |  |  |  |  | U.S.
 | U.S. |  |  |  |  |  |  | U.S.
 | U.S. |  |  |  |  |  |  | U.S.
US Top Gross Margin |  |  |  |  |  |  | US Bottom Gross Margin
Item No | Country | Type | Unit Cost | Gross Margin | Retail Sales |  | Item No | Country | Type | YTD | Gross Margin | Retail Sales
 | U.S. |  |  |  |  |  |  | U.S.
 | U.S. |  |  |  |  |  |  | U.S.
 | U.S. |  |  |  |  |  |  | U.S.
 | U.S. |  |  |  |  |  |  | U.S.
 | U.S. |  |  |  |  |  |  | U.S.
Percentile | 0.33 | 0.66 | 0.99
U.S.
Australia
U.S. Inventory by Type |  |  |  |  |  |  | AUS Inventory by Type
Percentile | 0.33 |  | 0.66 |  | 99%-100% |  | Percentile | 0.33 |  | 0.66 |  | 99%-100%
Range | 0-25 |  | 26-80 |  | 81-374 |  | Range | 0-169 |  | 169-698.1 |  | 698.1-10335
Kit | 16 | Spice | 21 | Spice | 28 |  | Kit | 32 | Spice | 29 | Spice | 28
Spice | 10 | Kit | 10 | [NAME] | 14 |  | Tea | 5 | Tea | 15 | [NAME] | 18
BUYB | 9 | Tea | 9 | Sugar | 7 |  | Other | 4 | Kit | 7 | [NAME] | 11
US Movement of Inventory
Percentile | On Hand | On hand plus expected | Total Cuml. Unit Sales**
0.25
0.5
0.75
1
Item |  | On Hand Inventory Status | On hand + Expected
523 |  |  |  |  | Assumptions
613 |  |  |  |  | SHORT | Total Cuml. Unit cells is 1 Quartile above Inventory
516 |  |  |  |  | VERY SHORT | Total Cuml. Unit cells is 2 Quartile above Inventory
333 |  |  |  |  | LONG | Inventory 1 quartile above [NAME]. Sales
486 |  |  |  |  | VERY LONG | Inventory 2 quartile above [NAME]. Sales
1494
1508
309
311
2061
317
348
1216
1522
529
226
339
302
321
345
413
328
1504
331
322
1478
1215
1214
305
605
412
1471
616
609
956
611
880
603
336
607
340
SUMMARY OUTPUT |  |  | Y=On Hand + Exp |  |  |  | Correlation
 |  |  | X=Total Cuml. Unit Sales** |  |  |  |  | Qty on Hand | On Hand + Exp | Total Cuml. Unit Sales** | Unit Cost (NON- ETO)* | Gross Margin
Regression Statistics |  |  |  |  |  |  | Qty on Hand | 1
Multiple R | 0.4442043103230219 |  |  |  |  |  | On Hand + Exp | 0.7115139771102529 | 1
R Square | 0.1973174693095515 |  |  |  |  |  | Total Cuml. Unit Sales** | 0.5126514811838535 | 0.4442043103230219 | 1
Adjusted R Square | 0.1915427748441526 |  |  |  |  |  | Unit Cost (NON- ETO)* | -0.1134462306439597 | -0.00933832284534368 | -0.1206376463090984 | 1
Standard Error | 425.2897001407899 |  |  |  |  |  | Gross Margin | 0.1269591382619311 | 0.05524277890065052 | 0.1390547458581147 | -0.7955021879451901 | 1
Observations | 141
ANOVA
 | df | SS | MS | F | [NAME]
Regression | 1 | 6180253.02149307 | 6180253.02149307 | 34.16933493050546 | 3.43652368604825e-08
Residual | 139 | 25141114.73737217 | 180871.329045843
Total | 140 | 31321367.75886524
 | Coefficients | Standard Error | t Stat | P-value | Lower 95%
Intercept | 138.1346421710137 | 55.32255384490757 | 2.496895616176059 | 0.01369609994490592 | 28.75212270610795
Total Cuml. Unit Sales** | 1.215275404038574 | 0.2079009363881773 | 5.845454210795384 | 3.436523686048148e-08 | 0.8042183099462847