Selling Price of 375ml Go Juice Super Drink $ 1.95
Factory Supervisors Wages per week $ 1,200.00
Lease Cost Plant and Machinery per week $ 28,800.00
Lease Costs Factory per week $ 21,500.00
Crushing and Mixing Machine Power costs per machine per hour $ 92.65
Packaging Machine Power costs per machine per hour $ 256.40
Cost of Oranges per Tonne $ 482.17
Number of Drinks Produced per Crushing & Mixing machine per hour 740
Number of Crushing & Mixing Machines 3
Number of Packaging Machines 1
Number of 375 ml Drinks that can be produced per tonne of oranges 810
Millilitres of Guarana Chemical Mix added per 375ml Drink 8
Cost of 10 Litre Tin of Guarana Chemical Mix $ 215.01
Crushing & Mixing Machine Operators wages per hour $ 33.75
Packaging Machine Operators Wages per Hour $ 32.00
Number of Packaging Machine Operators 1
Number of Crushing & Mixing Machine Operators 3
Cost of 375ml Go Juice Super Drink Packs per carton $ 56.00
Number of 375ml Go Juice Super Drink Packs per carton 500
Number of Normal Hours in a week per the award 40
Overtime Rate (eg Time and a half enter 1.5, Double Time enter 2) 1.5
The Target Profit for the week $45,000.00
Note: one Packaging Machine can package the output from three crushing and mixing machines per hour
achines per hour
Number of Drinks Fixed Costs Total Costs Sales
-
1,000
2,000
3,000
4,000
5,000
6,000
7,000
8,000
9,000
10,000
Formulas
Variable costs per drink in normal hours
Variable Costs per Drink in Overtime
The breakeven point in the number of 375ml drinks per week ignoring overtime
If the selling price per drinks changes, while all other data remains the same, what
A formula that calculates the profit or loss in normal hours
I need to calculate the number of drinks that must be sold to achieve that target profit of $45,000.
A formular that will warn me that a target profit I keyed in will require more that 15 hours overtime.
High Guys,
I need some help writing a few formulas in the attached spreadsheet. The Data_Input shee
I need to write the following formulas in the calculation sheet.
1 Fixed costs per week
2 Variable costs per drink in normal hours
3 Variable Costs per Drink in Overtime
4 The breakeven point in the number of 375ml drinks per week Inoring overtime
5 If the selling price per drinks changes, while all other data remains the same, what is the m
6 A formula that calculates the profit or loss in normal hours
7 Graph_Data sheet will need formulas to allow me to graph it
8 I need to calculate the number of drinks that must be sold to achieve that target profit of $4
9 A formular that will warn me that a target profit I keyed in will require more that 15 hours ov
Thank You for Your Held in advance
Runner77
Caculation
spreadsheet. The Data_Input sheet where all the information will come from I have set-up Name ranges for each category.
er week Inoring overtime
data remains the same, what is the minimum selling price per drink below which overtime will be required to breakeven. I will need an If state
sold to achieve that target profit of $45,000.
in will require more that 15 hours overtime.
for each category.
o breakeven. I will need an If statement to test whether overtime is required.
Task (1)Breakeven point in Number of 375ml Go Juice drinks per week
Task (2) Overtime will be required if the selling price drops below
Task (3) The graph table is on the graph data worksheet
Task (4) The fraph is on the Graph Worksheet
Task (5) Number of drinks to earn the Target Profit for the week
Task (6) Warning if Target Profit will require > than 15 hours overtime