1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 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 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 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 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108
| Option Explicit
Sub Formules()
With Feuil1
.Activate
'DONNEES EXPLOITATION EN PROPRE
'données volume d'activité
' Volume ventes menu base
Range("B4") = WorksheetFunction.RandBetween(10, 12) * 1000
' Volume ventes menu complet
Range("C4") = WorksheetFunction.RandBetween(20, 25) * 100
'données taux de marge
' Taux de marge menu base
Range("B5") = WorksheetFunction.RandBetween(5, 20) / 100
' Taux de marge menu complet
Range("C5") = WorksheetFunction.RandBetween(8, 30) / 100
'données charges indirectes TOTALES
'amortissements
Range("B7") = WorksheetFunction.RandBetween(10, 20) * 1000
'assurance
Range("B8") = WorksheetFunction.RandBetween(20, 25) * 100
'Frais de personnel
Range("B9") = WorksheetFunction.RandBetween(20, 25) * 100
'part fixe Frais de personnel
Range("C9") = Range("B9") * (WorksheetFunction.RandBetween(40, 60)) / 100
'part variable Frais de personnel
Range("D9") = Range("B9") - Range("C9")
'Honoraire comptable
Range("B10") = WorksheetFunction.RandBetween(30, 40) * 100
'part fixe honoraire comptable
Range("C10") = Range("B10") * (WorksheetFunction.RandBetween(50, 80)) / 100
'part variable honoraire comptable
Range("D10") = Range("B10") - Range("C10")
'données approvisionnement
'stoks matières
'Volume stocks initiaux
Range("B14") = WorksheetFunction.RandBetween(5, 10) * 100
'Coef par rapport période en cours
Range("C14") = WorksheetFunction.RandBetween(50, 120) / 100
'Part alimentaire menu base
Range("B15") = WorksheetFunction.RandBetween(2, 4)
'Part alimentaire supplément dessert
Range("F15") = WorksheetFunction.RandBetween(10, 18) / 10
'Prix kit emballage
Range("B16") = WorksheetFunction.RandBetween(1, 2)
'Prix boite patissière
Range("F16") = WorksheetFunction.RandBetween(10, 50) / 100
'Volume achats approv
Range("B17") = WorksheetFunction.RandBetween(15, 17) * 1000
'Frais personnel
'Nbr heures/semaine
Range("B19") = WorksheetFunction.RandBetween(1, 2) * 10
'% temps travail hebdomadaire dédié à la vente
Range("E19") = (WorksheetFunction.RandBetween(15, 60)) / 100
'Taux charges patronales
Range("C20") = WorksheetFunction.RandBetween(10, 20) / 100
'Prime pour 100 menus
Range("B21") = WorksheetFunction.RandBetween(1, 2)
'Nombre total menus à vendre pour toucher la prime
Range("G21") = WorksheetFunction.RandBetween(10, 17) * 1000
'DONNEES EXPLOITATION FRANCHISE
'données volume d'activité
' Volume ventes menu base
Range("G4") = WorksheetFunction.RandBetween(11, 15) * 1000
' Volume ventes menu complet
Range("H4") = WorksheetFunction.RandBetween(20, 40) * 100
' Taux de marge menu base
Range("G5") = WorksheetFunction.RandBetween(90, 150) / 100
' Taux de marge menu complet
Range("H5") = WorksheetFunction.RandBetween(200, 250) / 100
'données charges indirectes TOTALES
'Location food truck
Range("G7") = WorksheetFunction.RandBetween(8, 15) * 1000
'part fixe Location food truck
Range("H7") = Range("G7") * (WorksheetFunction.RandBetween(30, 60)) / 100
'part variable Location food truck
Range("I7") = Range("G7") - Range("H7")
'assurance
Range("G8") = WorksheetFunction.RandBetween(20, 25) * 100
'Redevance franchise
Range("G9") = WorksheetFunction.RandBetween(45, 55) * 100
'données approvisionnement
'stoks matières
'Volume stocks initiaux
Range("B25") = WorksheetFunction.RandBetween(10, 30) * 100
'Prix stocks initiaux
Range("C25") = WorksheetFunction.RandBetween(50, 120) / 100
'Part alimentaire menu base
Range("B26") = WorksheetFunction.RandBetween(1, 3)
'Part alimentaire supplément dessert
Range("F26") = WorksheetFunction.RandBetween(5, 15) / 100
'Prix kit emballage
Range("B27") = WorksheetFunction.RandBetween(50, 100) / 100
'Prix boite patissière
Range("F27") = WorksheetFunction.RandBetween(5, 30) / 100
'Volume achats approv
Range("B28") = WorksheetFunction.RandBetween(20, 25) * 1000
'Frais personnel
'Nbr heures/semaine
Range("B30") = WorksheetFunction.RandBetween(2, 4) * 10
'% temps travail hebdomadaire dédié à la vente
Range("E30") = (WorksheetFunction.RandBetween(45, 75)) / 100
'Taux charges patronales
Range("C31") = WorksheetFunction.RandBetween(10, 15) / 100
'revenu attendu par entrepreneur
'Résultat net global attendu
Range("B33") = WorksheetFunction.RandBetween(100, 180) * 1000
End With
End Sub |
Partager