Business Analytics: Cox Electric Profit Calculation

This assignment focuses on profit calculation using spreadsheet models. It begins with the fundamentals of calculating profit, including revenue, variable costs, fixed costs, and contribution margin. The assignment presents a case study of Cox Electric, a company that makes electronic components,...Read More

1
Question 1
A
Step 1: Calculate Revenue
Step 2: Calculate variable costs
Step 3: Get the contribution margin
Step 4: less fixed cost from contribution margin
Profit
B
Profit = Revenue- Variable cost-
Fixed cost (Metwally, 2011)
c Profit calculation
Number
of units
Sales
per
unit Total Sales Ref
Revenue a b a*b E4=total sales
Cost of sale
Material cost a c a*c
E7= Total
material cost
Labor cost a d a*d
E8= Total labor
cost
Profit calculation
Revenue (a)=Units sold *unit
Variable costs(b) = Units
sold*material cost units sold* labour
Contribution margin (c) = a-b
fixed cost = d
Profit = c-d

2
Total variable cost (a+c)+(a*d)
E9=Total variable
cost
Contribution E4-E9
E11=Total
contribution
Less:
Fixed costs FC E14= fixed cost
Profit (loss) E11-E14 E14 = Profit
D Profit calculation
Number
of units
Sales
per
unit Total Sales Ref
Revenue 12,000 $0.65 $7,800.00 E4=total sales
Cost of sale
Material cost 12,000 $0.15 $1,800.00
E7= Total
material cost
Labor cost 12,000 $0.10 $1,200.00
E8= Total labor
cost
Total variable cost $3,000.00
E9=Total variable
cost
Contribution $4,800.00
E11=Total
contribution
Less:
Fixed costs $10,000.00 E14= fixed cost
Profit (Loss) ($5,200.00) E14 = Profit(loss)

3
Question 2
Using goal seek
Prooduction
Sales
per
unit Total Sales Ref
Revenue 25,000 $0.65 $16,250.00 E4=total sales
Cost of sale
Material cost 25,000 $0.15 $3,750.00
E7= Total material
cost
Labor cost 25,000 $0.10 $2,500.00 E8= Total labor cost
Total variable cost $6,250.00
E9=Total variable
cost
Contribution $10,000.00
E11=Total
contribution
Less:
Fixed costs $10,000.00 E14= fixed cost
Profit (Loss) $0.00 E14 = Profit
Question 3
Using goal-seek (Ayer, 2015)
Profit and loss model
Number of Non-
members Unit price Total
Revenue $50.50 $75.00 $3,787.13
Costs
Registration $50.50 $8.50 $429.21
Continental breakfast $50.50 $4.00 $201.98
Lunch $50.50 $7.00 $353.47
Parking $50.50 $5.00 $252.48
Variable costs $1,237.13
Contribution margin $2,550.00

4
Fixed costs
Rental cost for the auditorium $2,400.00
Speaker Costs $150.00
Total fixed costs $2,550.00
Profit $0.00
Question 4
Profit and loss model
Number of
Non-members Members Unit price Total
Revenue 127.00 $75.00 $9,525.00
Costs
Registration 127.00 $8.50 $1,079.50
Continental breakfast 127.00 60.00 $4.00 $748.00
Lunch 127.00 60.00 $7.00 $1,309.00
Parking 127.00 45.00 $5.00 $796.50
Refunds
Non-members 12.70 $37.50 $476.25
Variable costs $4,409.25
Contribution margin $5,115.75
Fixed costs
Rental cost for the
auditorium $2,400.00
Speaker Costs $150.00
Total fixed costs $2,550.00
Profit $2,565.75

Non-
attendance 10% 12.00% 14.00% 16.00% 18.00% 20.00% 22.00% 24.00%
Attendees
80 $930.00 $818.00 $706.00 $594.00 $482.00 $370.00 $258.00 $146.0
85 $1,147.50 $1,028.50 $909.50 $790.50 $671.50 $552.50 $433.50 $314.5
90 $1,365.00 $1,239.00 $1,113.00 $987.00 $861.00 $735.00 $609.00 $483.0
95 $1,582.50 $1,449.50 $1,316.50 $1,183.50 $1,050.50 $917.50 $784.50 $651.5
100 $1,800.00 $1,660.00 $1,520.00 $1,380.00 $1,240.00 $1,100.00 $960.00 $820.0
105 $2,017.50 $1,870.50 $1,723.50 $1,576.50 $1,429.50 $1,282.50 $1,135.50 $988.5
110 $2,235.00 $2,081.00 $1,927.00 $1,773.00 $1,619.00 $1,465.00 $1,311.00 $1,157.0
115 $2,452.50 $2,291.50 $2,130.50 $1,969.50 $1,808.50 $1,647.50 $1,486.50 $1,325.5
120 $2,670.00 $2,502.00 $2,334.00 $2,166.00 $1,998.00 $1,830.00 $1,662.00 $1,494.0
125 $2,887.50 $2,712.50 $2,537.50 $2,362.50 $2,187.50 $2,012.50 $1,837.50 $1,662.5
130 $3,105.00 $2,923.00 $2,741.00 $2,559.00 $2,377.00 $2,195.00 $2,013.00 $1,831.0
135 $3,322.50 $3,133.50 $2,944.50 $2,755.50 $2,566.50 $2,377.50 $2,188.50 $1,999.5
140 $3,540.00 $3,344.00 $3,148.00 $2,952.00 $2,756.00 $2,560.00 $2,364.00 $2,168.0
145 $3,757.50 $3,554.50 $3,351.50 $3,148.50 $2,945.50 $2,742.50 $2,539.50 $2,336.5
150 $3,975.00 $3,765.00 $3,555.00 $3,345.00 $3,135.00 $2,925.00 $2,715.00 $2,505.0
155 $4,192.50 $3,975.50 $3,758.50 $3,541.50 $3,324.50 $3,107.50 $2,890.50 $2,673.5
160 $4,410.00 $4,186.00 $3,962.00 $3,738.00 $3,514.00 $3,290.00 $3,066.00 $2,842.0

6
References
Ayer, G. (2015). Excel in Excel®!*. Energy Engineering, 112(2), 14-25. doi:
10.1080/01998595.2015.11090950
Metwally, K. (2011). Understanding Internal Markets Through Franchise Contribution Margin
Analysis. SSRN Electronic Journal. doi: 10.2139/ssrn.1770942