{"text_format":"1","workbook":"xlsx","n_sheets":1,"sheets":["Sheet1"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":0,"defined_names":1,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Sheet1","index":0,"n_rows":75,"n_cols":10,"rows_kept":75,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[null,null,"Program Profitability Calculator"]
[]
[]
[null,"Steps:","Inputs:",null,null,null,null,"Scenarios:"]
[]
[null,"*** Note - the figures only calculate","Planned Sales Units % to LY:",{"$f":"IF(ISERROR((D12-E12)/E12),\"-\",(D12-E12)/E12)","v":-5.185185185185185E-2},null,null,null,"Domestic Vendors:"]
[null,"correctly if the Projected GM% is less than",null,null,null,null,null,"1)  What is the projected shortfall or debit need?"]
[null,"the GM % Plan","Metric","TY","LY"]
[null,null,"Reg Sales U",128000,135000,null,null,"2)  How many cancels are required to achieve GM % Plan?"]
[null,"1) Enter TY LY info in yellow cells only","Ticket",22,20]
[null,null,"Reg Selling Price",16,15,null,null,"3)  Number of units vendor agrees to cancel:"]
[null,null,"Cost",6.5,6]
[null,null,"Receipt Units",255000,260000,null,null,"4)  What is the new GM $ shortfall (debit need)?"]
[]
[null,"2) Enter TY / LY GM% plan","GM % Plan",0.45,0.45,null,null,"5)  What is the new cost on the balance of on-order needed to achieve GM % plan?"]
[null,null,null,null,null,null,null,235000,4887.4381095274584]
[null,null,"Gross Margin Projected:",{"$f":"D44","v":0.37256905998921158},{"$f":"E44","v":0.38973105134474328}]
[null,null,null,260000,null,null,null,"International Vendors:"]
[null,null,"Assumptions:",null,null,null,null,"1) What is the Reg Selling Price needed to achieve GM % Plan?"]
[null,"3) These are the default assumptions;","First Markdown:",0.75,null,null,null,null,15]
[null,"    they can be changed if needed","Second Markdown:",0.9,null,null,null,"2) How many TY Reg Sales Units are required to achive GM% plan?"]
[null,null,null,null,null,null,null,null,155000]
[null,null,"First Markdown Sell-Through %:",0.75,null,null,null,"3) What is the Ticket Price Required to achieve GM % Plan?"]
[null,null,"Second Markdown Sell-Through %:",1,null,null,null,null,20]
[]
[null,null,"Calculations:"]
[null,null,"TTL Program (Reg & Clr)","TY","LY",null,null,"Reg Price","TY","LY"]
[null,null,"Reg Sales $",{"$f":"D12*D14","v":2048000},{"$f":"E12*E14","v":2025000},null,null,"Reg Sell thru %",{"$f":"D12/D16","v":0.50196078431372548},{"$f":"E12/E16","v":0.51923076923076927}]
[null,null,"IMU%",{"$f":"1-(D15/D13)","v":0.70454545454545459},{"$f":"1-(E15/E13)","v":0.7},null,null,"Reg PLU Markdown $$",{"$f":"(D13-D14)*D12","v":768000},{"$f":"(E13-E14)*E12","v":675000}]
[null,null,"Receipt $$ Retail",{"$f":"D16*D13","v":5610000},{"$f":"E16*E13","v":5200000},null,null,"Reg PLU Markdown Rate",{"$f":"I32/D31","v":0.375},{"$f":"J32/E31","v":0.33333333333333331}]
[null,null,"Receipt $$ Cost",{"$f":"D16*D15","v":1657500},{"$f":"E16*E15","v":1560000},null,null,"Reg % off rate",{"$f":"1-(D14/D13)","v":0.27272727272727271},{"$f":"1-(E14/E13)","v":0.25}]
[null,null,"TTL Sales U",{"$f":"I40+D12","v":255000},{"$f":"J40+E12","v":260000},null,null,"Reg Price $$ COGS",{"$f":"D12*D15","v":832000},{"$f":"E12*E15","v":810000}]
[null,null,"TTL Sales $",{"$f":"I41+D31","v":2641725},{"$f":"J41+E31","v":2556250},null,null,"Reg Price Gross Margin $",{"$f":"D31-I35","v":1216000},{"$f":"E31-J35","v":1215000}]
[null,null,"TTL Selling Price",{"$f":"D36/D35","v":10.359705882352941},{"$f":"E36/E35","v":9.8317307692307701},null,null,"Reg Price Gross Margin %",{"$f":"I36/D31","v":0.59375},{"$f":"J36/E31","v":0.6}]
[null,null,"TTL Sell thru %",{"$f":"D35/D16","v":1},{"$f":"E35/E16","v":1}]
[null,null,"TTL Markdown $$",{"$f":"I44+I54","v":4295775},{"$f":"J44+J54","v":3843750},null,null,"TTL Clearance","TY","LY"]
[null,null,"TTL Markdown Rate",{"$f":"D39/D36","v":1.6261249751582774},{"$f":"E39/E36","v":1.5036674816625917},null,null,"Clr Sales U",{"$f":"I57+I70","v":127000},{"$f":"J57+J70","v":125000}]
[null,null,"TTL % off rate",{"$f":"1-(D37/D13)","v":0.52910427807486626},{"$f":"1-(E37/E13)","v":0.50841346153846145},null,null,"Clr Sales $",{"$f":"I58+I71","v":593725},{"$f":"J58+J71","v":531250}]
[null,null,"TTL $$ COGS (Receipt Cost)",{"$f":"D35*D15","v":1657500},{"$f":"E35*E15","v":1560000},null,null,"Clr Selling Price",{"$f":"I41/I40","v":4.6749999999999998},{"$f":"J41/J40","v":4.25}]
[null,null,"TTL Gross Margin $",{"$f":"D36-D42","v":984225},{"$f":"E36-E42","v":996250},null,null,"Clr Sell thru %",{"$f":"I40/I52","v":1},{"$f":"J40/J52","v":1}]
[null,null,"TTL Gross Margin %",{"$f":"IF(ISERROR(D43/D36),\"-\",(D43/D36))","v":0.37256905998921158},{"$f":"IF(ISERROR(E43/E36),\"-\",(E43/E36))","v":0.38973105134474328},null,null,"Clr Markdown $",{"$f":"I54+I67","v":2200275},{"$f":"J54+J67","v":1968750}]
[null,null,"TTL Gross Margin $ PLAN ",{"$f":"D46*D36","v":1188776.25},{"$f":"E46*E36","v":1150312.5},null,null,"Clr Markdown Rate",{"$f":"I44/I41","v":3.7058823529411766},{"$f":"J44/J41","v":3.7058823529411766}]
[null,null,"TTL Gross Margin % PLAN",{"$f":"D18","v":0.45},{"$f":"E18","v":0.45},null,null,"Clr % off rate",{"$f":"1-(I42/D13)","v":0.78749999999999998},{"$f":"1-(J42/E13)","v":0.78749999999999998}]
[null,null,"TTL Gross Margin $ Shortfall",{"$f":"D45-D43","v":204551.25},{"$f":"E45-E43","v":154062.5},null,null,"Clr $$ COGS",{"$f":"I40*D15","v":825500},{"$f":"J40*E15","v":750000}]
[null,null,"TTL Debits Needed ",{"$f":"D47","v":204551.25},{"$f":"E47","v":154062.5},null,null,"Clr Gross Margin $",{"$f":"I41-I47","v":-231775},{"$f":"J41-J47","v":-218750}]
[null,null,"Future Per Unit Cost Needed",{"$f":"D15-(D48/D35)","v":5.6978382352941175},{"$f":"E15-(E48/E35)","v":5.4074519230769234},null,null,"Clr Gross Margin %",{"$f":"I48/I41","v":-0.39037433155080214},{"$f":"J48/J41","v":-0.41176470588235292}]
[null,null,"Future IMU Needed",{"$f":"1-(D49/D13)","v":0.74100735294117648},{"$f":"1-(E49/E13)","v":0.72962740384615388}]
[null,null,null,null,null,null,null,{"$f":"\"1st markdown \"&D23&\" Off\"","v":"1st markdown 0.75 Off"},"TY","LY"]
[null,null,null,null,null,null,null,"BOH Inventory U",{"$f":"D16-D12","v":127000},{"$f":"E16-E12","v":125000}]
[null,null,null,null,null,null,null,"BOH Inventory $$",{"$f":"I52*D13","v":2794000},{"$f":"J52*E13","v":2500000}]
[null,null,null,null,null,null,null,"Clearance markdown ",{"$f":"(D13*$D$23)*I52","v":2095500},{"$f":"(E13*$D$23)*J52","v":1875000}]
[null,null,null,null,null,null,null,"Clr Markdown Rate",{"$f":"I54/I58","v":4},{"$f":"J54/J58","v":4}]
[null,null,null,null,null,null,null,"Selling Price",{"$f":"D13*(1-$D$23)","v":5.5},{"$f":"E13*(1-$D$23)","v":5}]
[null,null,null,null,null,null,null,"Sales U",{"$f":"I52*$D$26","v":95250},{"$f":"J52*$D$26","v":93750}]
[null,null,null,null,null,null,null,"Sales $",{"$f":"I57*I56","v":523875},{"$f":"J57*J56","v":468750}]
[null,null,null,null,null,null,null,"Clr % off Rate",{"$f":"$D$23","v":0.75},{"$f":"$D$23","v":0.75}]
[null,null,null,null,null,null,null,"Clr $$ COGS",{"$f":"I57*D15","v":619125},{"$f":"J57*E15","v":562500}]
[null,null,null,null,null,null,null,"Clr Gross Margin $",{"$f":"I58-I60","v":-95250},{"$f":"J58-J60","v":-93750}]
[null,null,null,null,null,null,null,"Clr Gross Margin %",{"$f":"I61/I58","v":-0.18181818181818182},{"$f":"J61/J58","v":-0.2}]
[]
[null,null,null,null,null,null,null,{"$f":"\"2nd markdown \"&D24&\" Off\"","v":"2nd markdown 0.9 Off"},"TY","LY"]
[null,null,null,null,null,null,null,"BOH Inventory U",{"$f":"D16-D12-I57","v":31750},{"$f":"E16-E12-J57","v":31250}]
[null,null,null,null,null,null,null,"BOH Inventory $$",{"$f":"I65*I56","v":174625},{"$f":"J65*J56","v":156250}]
[null,null,null,null,null,null,null,"Clearance markdown ",{"$f":"(I56-I69)*I70","v":104775.00000000003},{"$f":"(J56-J69)*J70","v":93750.000000000015}]
[null,null,null,null,null,null,null,"Clearance Markdown Rate",{"$f":"I67/I71","v":1.5000000000000011},{"$f":"J67/J71","v":1.5000000000000007}]
[null,null,null,null,null,null,null,"Selling Price",{"$f":"D13*(1-$D$24)","v":2.1999999999999993},{"$f":"E13*(1-$D$24)","v":1.9999999999999996}]
[null,null,null,null,null,null,null,"Sales U",{"$f":"I65*$D$27","v":31750},{"$f":"J65*$D$27","v":31250}]
[null,null,null,null,null,null,null,"Sales $",{"$f":"I70*I69","v":69849.999999999971},{"$f":"J70*J69","v":62499.999999999985}]
[null,null,null,null,null,null,null,"Clr % off Rate",{"$f":"$D$24","v":0.9},{"$f":"$D$24","v":0.9}]
[null,null,null,null,null,null,null,"Clr $$ COGS",{"$f":"I70*D15","v":206375},{"$f":"J70*E15","v":187500}]
[null,null,null,null,null,null,null,"Clr Gross Margin $",{"$f":"I71-I73","v":-136525.00000000003},{"$f":"J71-J73","v":-125000.00000000001}]
[null,null,null,null,null,null,null,"Clr Gross Margin %",{"$f":"I74/I71","v":-1.9545454545454557},{"$f":"J74/J71","v":-2.0000000000000009}]