Ellie Mosk, CEO of X-Space Industries, decided to expand the company’s product o
ID: 2334633 • Letter: E
Question
Ellie Mosk, CEO of X-Space Industries, decided to expand the company’s product offering beyond the core model rocket business. After investigation, she decided to set up a separate division to design and manufacture products for the drone market. Several companies were interested in having X-Space develop these drones, and financial results, to date, have been encouraging. Revenue was $4 million, gross margins have been running about 40%, and the customer sales and support costs were $1 million. However, there is a growing concern that some customers require a disproportionate share of the sales and support resources, and the true profitability of the customers is unknown. Data were collected to support an analysis of customer profitability:
Required:
1. Management felt the easiest way to allocate the sales and support costs was based on the total revenue. Using total revenue as the allocation base, determine the profitability of each of the five customers.
2. Management felt that because the data revealed some customers require a disproportionate share of sales and support resources, activity-based costing should be used to determine customer profitability. Use ABC to prepare a customer profitability analysis.
Activity Cost Driver Total Cost Sales visits Sales visit days $ 489,000 Product modifications Number of modifications 273,000 Phone calls Number of minutes 93,300 E-mail/electronic communications Number of communications 175,000 $ 1,030,300Explanation / Answer
Requirement 1
Apportionment of Support cost on the basis of Total Revenue
Customer
Total Revenues
Share of Support cost based on revenue
A
$ 403,000.00
$ 99,594.84*
B
$ 503,000.00
$ 124,308.20
C
$ 603,000.00
$ 149,021.56
D
$ 1,130,000.00
$ 279,260.97
E
$ 1,530,000.00
$ 378,114.42
Total
$ 4,169,000.00
$ 1,030,300.00
*Total Sales and Support cost/Total Revenue x Respective revenue by Customer.
Customer Profitability Analysis
Customer
Gross Profit
Allocated Support Cost
Profit after Support Cost
Profit %
A
$ 153,000.00
$ 99,594.84
$ 53,405.16
9.29%
B
$ 203,000.00
$ 124,308.20
$ 78,691.80
13.69%
C
$ 233,000.00
$ 149,021.56
$ 83,978.44
14.61%
D
$ 423,000.00
$ 279,260.97
$ 143,739.03
25.01%
E
$ 593,000.00
$ 378,114.42
$ 214,885.58
37.39%
Total
$ 1,605,000.00
$ 1,030,300.00
$ 574,700.00
100.00%
Requirement 2
Cost Drivers Rate
Activity
Cost Drivers
Total Activity Amount (A)
Level of Activity (B)
Activity Rate (C=A/B)
Sales Visits
Sales Visit days
$ 489,000.00
270.00
$ 1,811.11
Per Sales visit
Product Modifications
Number of Modifications
$ 273,000.00
200.00
$ 1,365.00
Per Modification
Phone calls
Number of phone calls
$ 93,300.00
8010.00
$ 11.65
Per Call
E mails/Electronic communications
Number of communications
$ 175,000.00
7140.00
$ 24.51
Per Communication
$ 1,030,300.00
Customer A
Activity
Activity Rate (A)
Activity level (B)
Activity Cost (A*B)
Sales Visits
$ 1,811.11
15
$ 27,166.67
Product Modifications
$ 1,365.00
15
$ 20,475.00
Phone calls
$ 11.65
1160
$ 13,511.61
E mails/Electronic communications
$ 24.51
625
$ 15,318.63
Total Cost Allocated
$ 76,471.90
Customer B
Activity
Activity Rate (A)
Activity level (B)
Activity Cost (A*B)
Sales Visits
$ 1,811.11
25
$ 45,277.78
Product Modifications
$ 1,365.00
15
$ 20,475.00
Phone calls
$ 11.65
1250
$ 14,559.93
E mails/Electronic communications
$ 24.51
875
$ 21,446.08
Total Cost Allocated
$ 101,758.78
Customer C
Activity
Activity Rate (A)
Activity level (B)
Activity Cost (A*B)
Sales Visits
$ 1,811.11
40
$ 72,444.44
Product Modifications
$ 1,365.00
40
$ 54,600.00
Phone calls
$ 11.65
1500
$ 17,471.91
E mails/Electronic communications
$ 24.51
1130
$ 27,696.08
Total Cost Allocated
$ 172,212.43
Customer D
Activity
Activity Rate (A)
Activity level (B)
Activity Cost (A*B)
Sales Visits
$ 1,811.11
90
$ 163,000.00
Product Modifications
$ 1,365.00
60
$ 81,900.00
Phone calls
$ 11.65
1850
$ 21,548.69
E mails/Electronic communications
$ 24.51
2130
$ 52,205.88
Total Cost Allocated
$ 318,654.57
Customer E
Activity
Activity Rate (A)
Activity level (B)
Activity Cost (A*B)
Sales Visits
$ 1,811.11
100
$ 181,111.11
Product Modifications
$ 1,365.00
70
$ 95,550.00
Phone calls
$ 11.65
2250
$ 26,207.87
E mails/Electronic communications
$ 24.51
2380
$ 58,333.33
Total Cost Allocated
$ 361,202.31
Overheads Cost Allocation
Customer A
Customer B
Customer C
Customer D
Customer E
Total Cost
Activity
Activity Cost
Activity Cost
Activity Cost
Activity Cost
Activity Cost
Sales Visits
$ 27,166.67
$ 45,277.78
$ 72,444.44
$ 163,000.00
$ 181,111.11
$ 489,000.00
Product Modifications
$ 20,475.00
$ 20,475.00
$ 54,600.00
$ 81,900.00
$ 95,550.00
$ 273,000.00
Phone calls
$ 13,511.61
$ 14,559.93
$ 17,471.91
$ 21,548.69
$ 26,207.87
$ 93,300.00
E mails/Electronic communications
$ 15,318.63
$ 21,446.08
$ 27,696.08
$ 52,205.88
$ 58,333.33
$ 175,000.00
$ 76,471.90
$ 101,758.78
$ 172,212.43
$ 318,654.57
$ 361,202.31
$ 1,030,300.00
Customer Profitability Analysis
Customer
Gross Profit
ABC COST
Profit after Support Cost
Profit %
A
$ 153,000.00
$ 76,471.90
$ 76,528.10
13.32%
B
$ 203,000.00
$ 101,758.78
$ 101,241.22
17.62%
C
$ 233,000.00
$ 172,212.43
$ 60,787.57
10.58%
D
$ 423,000.00
$ 318,654.57
$ 104,345.43
18.16%
E
$ 593,000.00
$ 361,202.31
$ 231,797.69
40.33%
Total
$ 1,605,000.00
$ 1,030,300.00
$ 574,700.00
100.00%
As per Analysis Customer Customer E is considered most profitable under ABC System.
Due to Cost allocation on the basis of revenue Customer A seems to produce 9% revenue but in fact Customer A gives 13% of Overall profits. See below table to understand this better
Customer
Profit % on allocating cost o the basis of Revenue
Profit % on ABC system
A
9.29%
13.32%
B
13.69%
17.62%
C
14.61%
10.58%
D
25.01%
18.16%
E
37.39%
40.33%
Total
100.00%
100.00%
It is clearly seen that ABC is better method and shows correct results.
Customer
Total Revenues
Share of Support cost based on revenue
A
$ 403,000.00
$ 99,594.84*
B
$ 503,000.00
$ 124,308.20
C
$ 603,000.00
$ 149,021.56
D
$ 1,130,000.00
$ 279,260.97
E
$ 1,530,000.00
$ 378,114.42
Total
$ 4,169,000.00
$ 1,030,300.00
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.