Question

2020 25 Staff Hourly Rates (changes on 1 Jan each year) Staff 2018 2019 Bar Staff A $ 21 $ 23 $ Bar Staff B $ 21 $ 23 $ Bar Staff C $ 21 $ 23 $ Bar Staff D $ 26 $ 27 $ Bar Staff E $ 30 $ 32 S Security Staff A $ 20 $ 21 $ Security Staff B $ 20 $ 21 $ Security Staff C $ 25 $ 26 $ Cleaner A $ 20 $ 21 $ Fixed Costs Per Month (changes on 1 Jan each year) 2018 2019 Lease $ 5,100 $ 7,200 $ Utilities $ 3,200 $ 3,700 $ Licensing and Insurance $ 4,500 $ 4,600 $ Other Costs $ 11,000 $ 14,000 $ 25 25 28 34 2020 23,500 8,700 9,540 27,000 22 22 27 22 Thu Fri DJ Per night Entry Fee after 11pm) $ 500 $ 15 $ 600 $ 30 $ 800 $ 30 $ 500 $ 15 Retail Price $ $ $ $ Sat Sales Products Beer Wine Spirits Cocktails Non Alco Water Chips Nuts Sun Cost price 10.00 $ 12.00 $ 16.00 $ 19.00 $ 11.00 $ 4.00 $ 4.00 $ 4.50 $ 2.00 4.80 8.00 4.75 4.40 Profit Margin % Profit 80% $ 60% $ 50% $ 75% $ 60% $ 50% $ 50% $ 50% $ Profit on $1 of Products 8.00 $ 0.80 7.20$ 0.60 8.00 $ 0.50 14.25$ 0.75 6.60 $ 0.60 2.00 $ 0.50 2.00$ 0.50 2.25$ 0.50 2.00 $ $ $ $ 2.00 2.25
What are the formulas I need to find total product profit, total staff cost and gross nightly profit?

Answer 1

B10 fx =E2*0.8+F2*0.6+G2*0.5+H2*0.75+12*0.6+J2*0.5+K2*0.5+L2*0.5 B E L o R Nuts D Playing Patron Revenue Beer 100 5279870 141 12053952 273 8355967 67 1165839 92 1020274 F Wine 212 250 800 316 330 G Spirits 131 159 149 174 181 128 159 162 135 145 H 1 J K Cocktails Non Alco Water Chips 108 173 125 130 186 148 152 116 138 181 166 160 198 189 102 126 197 110 163 108 M N O P Bar Staff A Bar Staff B Staff Security Cleaner Dj 112 120 126 240 160 142 - 126 240 160 169 - 126 240 160 178 126 240 160 174 - 126 240 160 500 500 500 120 500 500 A 1 Date Day 2 05-07-2018 Thu 3 06-07-2018 Fri 4 05-07-2018 Sat 5 06-07-2018 Sun 6 05-07-2018 Thu 7 8 9 10 Total Product Profit Per Night 11 Total Product Profit 12 13 14 17 15 16 16 17 18 19 696 4525.75 Total Staff Cost Per Night Total Staff Cost 1146 5370 Total Nightly Gross Profit Total Gross Profit 5279420 27875057.8

11 =SUM(E2:E6) *0.8+SUM(F2:F6) *0.6+SUM(G2:56) *0.5+SUM(H2:H6) *0.75+SUM(12:16) *0.6+SUM(12:L6) *0.5 А B E L Q Date Day 05-07-2018 Thu 06-07-2018 Fri 05-07-2018 Sat 06-07-2018 Sun 05-07-2018 Thu Playing Patron Revenue Beer 100 5279870 141 12053952 273 8355967 67 1165839 92 1020274 F Wine 212 250 800 316 330 G Spirits 131 159 149 174 181 128 159 162 135 145 H J K Cocktails Non Alco Water Chips Nuts 108 173 125 130 186 148 152 116 138 181 166 160 198 189 102 126 197 110 163 108 M N 0 P Bar Staff A Bar Staff B Staff Security Cleaner Dj 112 120 126 240 160 142 - 126 240 160 169 - 126 240 160 178 120 126 240 160 174 - 126 240 160 500 500 500 500 500 Total Product Profit Per Night Total Product Profit 696 4525.75 Total Staff Cost Per Night Total Staff Cost 1146 5370 Total Nightly Gross Profit 5279420

M10 X =SUM(M2:02) B E L O Nuts D Playing Patron Revenue Beer 100 5279870 141 12053952 273 8355967 67 1165839 92 1020274 F Wine 212 250 800 316 330 131 149 181 159 135 Spirits 159 174 128 162 145 H K Cocktails Non Alco Water Chips 108 173 125 130 186 148 152 116 138 181 166 160 198 189 102 126 197 110 163 108 M N O P Bar Staff A Bar Staff B Staff Security Cleaner Dj 112 120 126 240 160 142 - 126 240 160 126 240 160 178 120 126 240 160 174 - 126 240 160 169 500 500 500 500 500 А 1 Date Day 2 05-07-2018 Thu 3 06-07-2018 Fri 4 05-07-2018 Sat 5 06-07-2018 Sun 6 05-07-2018 Thu 7 8 9 10 Total Product Profit Per Night 11 Total Product Profit 12 13 14 15 16 17 18 696 4525.75 Total Staff Cost Per : ht Total Staff Cost 1146 5370 Total Nightly Gross Profit Total Gross Profit 5279420 27875057.8

711 : =SUM(M2:06) А B E L 0 F Date D Playing Patron Revenue Beer 100 5279870 141 12053952 273 8355967 67 1165839 92 1020274 Day 05-07-2018 Thu 06-07-2018 Fri 05-07-2018 Sat 06-07-2018 Sun | 05-07-2018 Thu F Wine 212 250 800 316 330 G H J к Spirits Cocktails Non Alco Water Chips Nuts 131 159 108 173 125 130 149 174 186 148 152 116 181 128 138 181 166 160 159 162 198 189 102 126 135 145 197 110 163 108 M N o P Bar Staff A Bar Staff B Staff Security Cleaner Dj 112 120 126 240 160 142 240 160 169 126 240 160 178 120 126 240 160 174 - 126 240 160 126 500 500 500 500 500 696 Total Product Profit Per Night Total Product Profit Total Staff Cost Per Night Total Staff Cost 1146 5370 4525.75 Total Nightly Gross Profit Total Gross Profit 5279420 27875057.8

F15 3 X fx =D2+B10-M10 B E F L Q R K Chips Nuts 130 D Playing Patron Revenue Beer 100 5279870 141 12053952 273 8355967 67 1165839 92 1020274 G H 1 J Spirits Cocktails Non Alco Water 131 159 108 173 125 149 174 186 148 152 181 128 138 181 166 159 162 198 189 102 135 145 197 110 163 Wine 212 250 800 316 330 116 M IN Р Bar Staff A Bar Staff B Staff Security Cleaner Di 112 120 126 240 160 142 126 240 160 169 - 126 240 160 178 120 126 240 160 174 - 126 240 160 160 126 500 500 500 500 500 108 А 1 1 Date Day 2 05-07-2018 Thu 3 3 06-07-2018 Fri 4 4. 05-07-2018 Sat 5 06-07-2018 Sun 6 05-07-2018 Thu 7 8 9 10 Total Product Profit Per Night 11 Total Product Profit 12 12 13 14 151 16 17 18 696 4525.75 Total Staff Cost Per Night' Total Staff Cost 1146 5370 Total Nightly Gross Profit Total Gross Profit 5279420 27875057.8

16 fic А B E F L Q R Date Nuts =SUM(D2:06) +B11-M11 C D Playing Patron Revenue Beer 100 5279870 141 12053952 273 8355967 67 1165839 92 1020274 Day 05-07-2018 Thu 06-07-2018 Fri 05-07-2018 Sat 06-07-2018 Sun 05-07-2018 Thu G H J K Spirits Cocktails Non Alco Water Chips 131 159 108 173 125 130 149 174 186 148 152 116 181 128 138 181 166 160 159 162 198 189 102 126 135 145 197 110 163 108 Wine 212 250 800 316 330 142 - M N P P Bar Staff A Bar Staff B Staff Security Cleaner Dj 112 120 126 240 160 126 240 160 169 - 126 240 160 178 120 126 240 160 174 - 126 240 160 500 500 500 500 500 Total Product Profit Per Night Total Product Profit 696 4525.75 Total Staff Cost Per Night Total Staff Cost 1146 5370 Total Nightly Gross Profit Total Gross Profit 5279420 27875057.8

Notes

• I used Randome numbers not the exact same numbers in the picture but the formula will be the same.
• For the Gross Profit You can substract others costs too , You just have to choose the cell from the respected table.