Cox Electric
Parameters
Fixed Cost 10,000
Material Cost per Unit 0.15
Labor Cost per Unit 0.10
Revenue per Unit 0.65
Number of Units Produced 12,000
1 a) Give a mathematical notation for calculating profit.
Model
Profit = Revenue - Fixed Cost - Variable Cost
P(x) = R(x) - FC - VC(x)
b) Implement you model from part a in Excel using the principles of good spreadsheet design.
No. of units assumption 100,000
Total revenue 65,000.00
Total fixed cost - 10,000.00
Total variable cost - 25,000.00
Profit earned 30,000.00
c) What is the resulting profit?
Method
Unit Total
Unit Sold 12,000
Price 0.65
Total Revenue 7,800
Total Variable Expenses 0.25 3,000
Contribution Margin 0.40 4,800
Fixed Expenses 10,000
Profit/Loss - 5,200
2 a) One way data table
Quantity - 5,200
-
10,000
20,000
30,000
40,000
50,000
60,000
70,000
80,000
90,000
100,000
Profit at different levels of quantity.
The production volume occur between the quantities of 20,000 - 30,000 pcs.
c) Make a graph for the breakeven point
Units Revenue Fixed Total Expenses Profit
- - 10,000 10,000 - 10,000
10,000 6,500 10,000 12,500 - 6,000
20,000 13,000 10,000 15,000 - 2,000
30,000 19,500 10,000 17,500 2,000
40,000 26,000 10,000 20,000 6,000
50,000 32,500 10,000 22,500 10,000
60,000 39,000 10,000 25,000 14,000
70,000 45,500 10,000 27,500 18,000
80,000 52,000 10,000 30,000 22,000
90,000 58,500 10,000 32,500 26,000
100,000 65,000 10,000 35,000 30,000
COX Electric - Breakeven Point Chart
Revenue Fixed Total Expenses
70,000
60,000
50,000
40,000
30,000
20,000
10,000
-
GOAL SEEK FUNCTION IS APPLIED
Cox Electric
Parameters
Fixed Cost 10,000
Material Cost per Unit 0.15
Labor Cost per Unit 0.10
Revenue per Unit 0.65
Number of Units Produced 25,000
1 a) Give a mathematical notation for calculating profit.
Model
Profit = Revenue - Fixed Cost - Variable Cost
P(x) = R(x) - FC - VC(x)
b) Implement you model from part a in Excel using the principles of good spreadsheet design.
No. of units assumption 100,000
Total revenue 65,000.00
Total fixed cost - 10,000.00
Total variable cost - 25,000.00
Profit earned 30,000.00
c) What is the resulting profit?
Method
Unit Total
Unit Sold 25,000
Price 0.65
Total Revenue 16,250
Total Variable Expenses 0.25 6,250
Contribution Margin 0.40 10,000
Fixed Expenses 10,000
Profit/Loss -
2 a) One way data table
Quantity -
- - 10,000
10,000 - 6,000
20,000 - 2,000
30,000 2,000
40,000 6,000
50,000 10,000
60,000 14,000
70,000 18,000
80,000 22,000
90,000 26,000
100,000 30,000
Profit at different levels of quantity.
The production volume occur between the quantities of 20,000 - 30,000 pcs.
c) Make a graph for the breakeven point
Units Revenue Fixed Total Expenses Profit
- - 10,000 10,000 - 10,000
10,000 6,500 10,000 12,500 - 6,000
20,000 13,000 10,000 15,000 - 2,000
30,000 19,500 10,000 17,500 2,000
40,000 26,000 10,000 20,000 6,000
50,000 32,500 10,000 22,500 10,000
60,000 39,000 10,000 25,000 14,000
70,000 45,500 10,000 27,500 18,000
80,000 52,000 10,000 30,000 22,000
90,000 58,500 10,000 32,500 26,000
100,000 65,000 10,000 35,000 30,000
COX Electric - Breakeven Point Chart
Revenue Fixed Total Expenses
70,000
60,000
50,000
40,000
30,000
20,000
10,000
-
University of Cincinnati Center for Business Analytics
Parameters
Fixed Costs:
Rental Cost for the Auditorium 150
Speaker Cost 2,400
Variable Cost of 10 members 1,225
3,775
Variable Costs:
Registration Processing 8.50
Continental Breakfast 4
Lunch 7
Parking 5
24.50
Revenue for non-members 75
1 a) Calculate profit or loss based on the number of nonmember registrants.
Method
Unit Total
Attendees -
Price 75
Revenue -
Variable Expenses 24.50 -
Contribution Margin 50.50 -
Fixed Expenses 3,775
Profit/Loss - 3,775
University of Cincinnati Center for Business Analytics
Parameters
Fixed Costs:
Rental Cost for the Auditorium 150
Speaker Cost 2,400
Variable Cost of 10 members 1,225
3,775
Variable Costs:
Registration Processing 8.50
Continental Breakfast 4
Lunch 7
Parking 5
24.50
Revenue for non-members 75
1 b) Use goal seek to find the number of nonmember registrants that will make the event break even.
Method
Unit Total
Attendees 75
Price 75
Revenue 5,606
Variable Expenses 24.50 1,831
Contribution Margin 50.50 3,775
Fixed Expenses 3,775
Profit/Loss -
University of Cincinnati Center for Business Analytics
PARAMETERS:
No. of non-members registered 127
No. of members registered 50
% of no show for non-members 0.10
% of no show for members 0.25
% of refund for non-members 0.50
Fixed costs: 3,775
Rental cost 150
Speaker cost 2,400
Variable cost of 10 members 1,225
Variable costs: 25
Registration processing 9
Continental breakfast 4
Lunch 7
Parking 5
Revenue for non-members 75
a) What is the profit?
Total revenue from non-members 9,525
Total fixed cost - 3,775
Total variable cost - 3,112
Total refund from non-members - 476
Cost saved from parking due to non-members and members 126
Total profit earned 2,288
No. of non-members who did not show up 13
No. of members who did not show up 13
Total number of participants who did not show up 25
Refund from non-members who did not show - 476
b) Two way data table
2,288 10% 12% 14% 16% 18% 20%
80
85
90
95
100
105
110
115
120
125
130
135
140
145
150
155
160
Row input cell: Percentage of non-members to now show up.
Column input cell: Total non-members who registered.
22% 24% 26% 28% 30%
