{"text_format":"3","workbook":"xlsx","n_sheets":2,"sheets":["Budget","Macro"],"not_rendered":{"macros":1},"read":"whole","date_system":"1900"}

{"sheet":"Budget","index":0,"n_rows":34,"n_cols":14,"rows_kept":34}
["Vous devez planifier la production mensuelle d'une usine de vibrateurs pour la période du 1er juin 2015 au 31 mai 2016."]
["La capacité de production est de 2000 vibrateurs par mois."]
["Les ventes sont décrites dans le tableau suivant."]
["Les stocks sont nuls au 31 mai 2015."]
["Faites la planification en utilisant des formules qui permettront de copier-coller les prévisions pour les mois futurs."]
["Vous devez produire le plus tard possible, pour éviter la constitution de stocks inutiles, mais sans perdre de ventes."]
[]
[null,"Mois","Ventes"]
[6,"juin",1800]
[7,"juillet",1300]
[8,"août",1000]
[9,"septembre",1000]
[10,"octobre",1000]
[11,"novembre",3500]
[12,"décembre",4200]
[1,"janvier",1500]
[2,"février",2500]
[3,"mars",1800]
[4,"avril",1700]
[5,"mai",2100]
[null,"Total",{"$f":"SUM(C9:C20)","v":23400}]
[]
[null,"Date","2015-06-01",{"$f":"EDATE(C23,1)","v":"2015-07-01"},"2015-08-01","2015-09-01","2015-10-01","2015-11-01","2015-12-01","2016-01-01","2016-02-01","2016-03-01","2016-04-01","2016-05-01"]
[null,"Mois",{"$f":"MONTH(C23)","v":6},{"$f":"MONTH(D23)","v":7},{"$f":"MONTH(E23)","v":8},{"$f":"MONTH(F23)","v":9},{"$f":"MONTH(G23)","v":10},{"$f":"MONTH(H23)","v":11},12,{"$f":"MONTH(J23)","v":1},2,{"$f":"MONTH(L23)","v":3},4,{"$f":"MONTH(N23)","v":5}]
[null,"Ventes",{"$f":"VLOOKUP(C24,$A$9:$C$20,3,FALSE)","v":1800},{"$f":"VLOOKUP(D24,$A$9:$C$20,3,FALSE)","v":1300},1000,1000,1000,3500,4200,1500,2500,1800,1700,2100]
[null,"Production de base (vente ou max)",{"$f":"MIN(C25,2000)","v":1800},{"$f":"MIN(D25,2000)","v":1300},{"$f":"MIN(E25,2000)","v":1000},{"$f":"MIN(F25,2000)","v":1000},{"$f":"MIN(G25,2000)","v":1000},{"$f":"MIN(H25,2000)","v":2000},2000,{"$f":"MIN(J25,2000)","v":1500},2000,{"$f":"MIN(L25,2000)","v":1800},1700,{"$f":"MIN(N25,2000)","v":2000}]
[null,"Surplus de capacité de production",{"$f":"MAX(2000-C26,0)","v":200},{"$f":"MAX(2000-D26,0)","v":700},{"$f":"MAX(2000-E26,0)","v":1000},{"$f":"MAX(2000-F26,0)","v":1000},{"$f":"MAX(2000-G26,0)","v":1000},{"$f":"MAX(2000-H26,0)","v":0},{"$f":"MAX(2000-I26,0)","v":0},{"$f":"MAX(2000-J26,0)","v":500},{"$f":"MAX(2000-K26,0)","v":0},{"$f":"MAX(2000-L26,0)","v":200},{"$f":"MAX(2000-M26,0)","v":300},{"$f":"MAX(2000-N26,0)","v":0}]
[null,"Déficit de capacité de production",{"$f":"MIN(C26-C25,0)","v":0},{"$f":"MIN(D26-D25,0)","v":0},0,0,0,-1500,-2200,0,-500,0,0,-100]
[null,"Production pour stockage",{"$f":"MIN(C27,ABS(D30))","v":0},700,1000,1000,1000,0,0,500,0,0,100,0]
[null,"Déficit total à prévoir",{"$f":"D30+C28+C29","v":0},{"$f":"E30+D28+D29","v":0},{"$f":"F30+E28+E29","v":-700},-1700,-2700,-3700,-2200,0,-500,0,0,-100]
[null,"Stock début",0,{"$f":"C34","v":0},700,1700,2700,3700,2200,0,500,0,0,100]
[null,"Production totale",{"$f":"C26+C29","v":1800},{"$f":"D26+D29","v":2000},2000,2000,2000,2000,2000,2000,2000,1800,1800,2000]
[null,"Ventes",{"$f":"C25","v":1800},{"$f":"D25","v":1300},1000,1000,1000,3500,4200,1500,2500,1800,1700,2100]
[null,"Stock fin",{"$f":"C31+C32-C33","v":0},{"$f":"D31+D32-D33","v":700},{"$f":"E31+E32-E33","v":1700},{"$f":"F31+F32-F33","v":2700},{"$f":"G31+G32-G33","v":3700},{"$f":"H31+H32-H33","v":2200},0,{"$f":"J31+J32-J33","v":500},0,{"$f":"L31+L32-L33","v":0},100,{"$f":"N31+N32-N33","v":0}]

{"sheet":"Macro","index":1,"n_rows":6,"n_cols":2,"rows_kept":6}
["Faites une macro qui décale toutes les colonnes du budget vers la gauche, en lui donnant le raccourci CTRL-q."]
["C'est-à-dire que lorsque le premier mois du budget est terminé dans la vie réelle, le deuxième mois devient le premier, le 3e devient le 2e, et ainsi de suite."]
["Un nouveau 12e mois doit s'ajouter à la fin."]
["Exécutez-la."]
[]
[null,"CTRL-q"]