Scribd
Upload
42 views

Operating+Leverage +exercise

The document appears to be a financial analysis of a company's break-even point. It includes a data table with variables such as sales price, variable costs, fixed costs, and quantity sold. It then shows various "what if" scenarios analyzing how changes to factors like quantity sold, variable costs, or fixed costs would affect the company's operating income.

Uploaded by

Ani
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
42 views

Operating+Leverage +exercise

The document appears to be a financial analysis of a company's break-even point. It includes a data table with variables such as sales price, variable costs, fixed costs, and quantity sold. It then shows various "what if" scenarios analyzing how changes to factors like quantity sold, variable costs, or fixed costs would affect the company's operating income.

Uploaded by

Ani
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
You are on page 1/ 126

-48,000

2,000
52,000
102,000
152,000
202,000
0
300
600
900
1,200
1,500
1,800
2,100
2,400
2,700
3,000
3,300
3,600
3,900

Break-even point
4,200
4,500
4,800

Nb. units sold


5,100

3877
5,400
5,700
6,000
6,300

units
THE AUTOMATED PEN CORP.

6,600
6,900
7,200
7,500
7,800
8,100
8,400
8,700
Sales revenue = Quantity * $20

9,000
9,300
9,600
5 Sales revenue 0 1,800 3,600
6 EBIT -60,000 -58,750 -57,500
7 Total cost 60,000 60,550 61,100
8 Total variable cost 0 550 1,100
9 Fixed cost 60,000 60,000 60,000

1 Quantity sold 0 100 200


2 Sales - Total cost > 0 0 0 0
3 Sales - Total cost < 0 60,000 58,750 57,500
4 Min Sales & Total cost 0 1,800 3,600
P.

DATA TABLE
Selling price
Variable cost per unit
Fixed cost
Quantity sold
Operating Income

s revenue = Quantity * $20


WHAT IF?
Quantity sold = 3 600 units
Quantity sold = 3 960 units
Operating Income
Quantity sold = 10 000 units
Quantity sold = 11 000 units
The fixed cost increases by 10%, up to
$55,440 and the quantity sold is 10,000
6,600
6,900
7,200
7,500
7,800
8,100
8,400
8,700
9,000
9,300
9,600
9,900

units.
The variable cost increases by 10%, up
to $5.50 and the quantity sold is 10,000
units.
Total cost = $50,400 + Qty * $5
The selling price decreases by 10%,
down to $18 and the quantity sold is
10,000 units.
5,400 7,200### 10,800 12,600
-56,250 -55,000### -52,500 -51,250
61,650 62,200### 63,300 63,850
1,650 2,200### 3,300 3,850
60,000 60,000### 60,000 60,000

300 400### 600 700


0 0 0 0 0
56,250 55,000### 52,500 51,250
5,400 7,200### 10,800 12,600
Instructions: test each of the "What if?" scenarios
BLE 1 - change the necessary number(s) in the Data table
$ 18.00 2 - keep track of the Operating Income (which is automatically
computed in cell K9) in the "What if?" table, column K.
$ 5.50 Example: what if The Automated Pen Corp. sells 3,600 units?
1 - plug in 3600 in cell K8
$ 60,000 2 - notice the Operating Income is now $3,600 and keep track of this
10,000 units value in cell K14
Note: don't forget to reset the data table before testing the next
$ 65,000 scenario.
When you are done, please move to the Flexible Pen Corp. tab.

Operating Income
$ 3,600
$ 9,000
ting Income
$ 99,600
$ 114,600
55440
$ 94,560

$ 89,560

l cost = $50,400 + Qty * $5


$ 69,560
+10%

+10%
14,400### 18,000 19,800 21,600
-50,000### -47,500 -46,250 -45,000
64,400### 65,500 66,050 66,600
4,400### 5,500 6,050 6,600
60,000### 60,000 60,000 60,000

800### 1,000 1,100 1,200


0 0 0 0 0
50,000### 47,500 46,250 45,000
14,400### 18,000 19,800 21,600
What if?" scenarios
ber(s) in the Data table
Income (which is automatically
hat if?" table, column K.
ed Pen Corp. sells 3,600 units?
e is now $3,600 and keep track of this

data table before testing the next


ove to the Flexible Pen Corp. tab.

+10%

+10%
23,400 25,200 27,000 28,800 30,600
-43,750 -42,500 -41,250 -40,000 -38,750
67,150 67,700 68,250 68,800 69,350
7,150 7,700 8,250 8,800 9,350
60,000 60,000 60,000 60,000 60,000

1,300 1,400 1,500 1,600 1,700


0 0 0 0 0
43,750 42,500 41,250 40,000 38,750
23,400 25,200 27,000 28,800 30,600
32,400 34,200 36,000 37,800 39,600 41,400 43,200
-37,500 -36,250 -35,000 -33,750 -32,500 -31,250 -30,000
69,900 70,450 71,000 71,550 72,100 72,650 73,200
9,900 10,450 11,000 11,550 12,100 12,650 13,200
60,000 60,000 60,000 60,000 60,000 60,000 60,000

1,800 1,900 2,000 2,100 2,200 2,300 2,400


0 0 0 0 0 0 0
37,500 36,250 35,000 33,750 32,500 31,250 30,000
32,400 34,200 36,000 37,800 39,600 41,400 43,200
45,000 46,800 48,600 50,400 52,200 54,000 55,800 57,600
-28,750 -27,500 -26,250 -25,000 -23,750 -22,500 -21,250 -20,000
73,750 74,300 74,850 75,400 75,950 76,500 77,050 77,600
13,750 14,300 14,850 15,400 15,950 16,500 17,050 17,600
60,000 60,000 60,000 60,000 60,000 60,000 60,000 60,000

2,500 2,600 2,700 2,800 2,900 3,000 3,100 3,200


0 0 0 0 0 0 0 0
28,750 27,500 26,250 25,000 23,750 22,500 21,250 20,000
45,000 46,800 48,600 50,400 52,200 54,000 55,800 57,600
59,400 61,200 63,000 64,800 66,600 68,400 70,200 72,000
-18,750 -17,500 -16,250 -15,000 -13,750 -12,500 -11,250 -10,000
78,150 78,700 79,250 79,800 80,350 80,900 81,450 82,000
18,150 18,700 19,250 19,800 20,350 20,900 21,450 22,000
60,000 60,000 60,000 60,000 60,000 60,000 60,000 60,000

3,300 3,400 3,500 3,600 3,700 3,800 3,900 4,000


0 0 0 0 0 0 0 0
18,750 17,500 16,250 15,000 13,750 12,500 11,250 10,000
59,400 61,200 63,000 64,800 66,600 68,400 70,200 72,000
73,800 75,600 77,400 79,200 81,000 82,800 84,600 86,400
-8,750 -7,500 -6,250 -5,000 -3,750 -2,500 -1,250 0
82,550 83,100 83,650 84,200 84,750 85,300 85,850 86,400
22,550 23,100 23,650 24,200 24,750 25,300 25,850 26,400
60,000 60,000 60,000 60,000 60,000 60,000 60,000 60,000

4,100 4,200 4,300 4,400 4,500 4,600 4,700 4,800


0 0 0 0 0 0 0 0
8,750 7,500 6,250 5,000 3,750 2,500 1,250 0
73,800 75,600 77,400 79,200 81,000 82,800 84,600 86,400
88,200 90,000 91,800 93,600 95,400 97,200 99,000 100,800
1,250 2,500 3,750 5,000 6,250 7,500 8,750 10,000
86,950 87,500 88,050 88,600 89,150 89,700 90,250 90,800
26,950 27,500 28,050 28,600 29,150 29,700 30,250 30,800
60,000 60,000 60,000 60,000 60,000 60,000 60,000 60,000

4,900 5,000 5,100 5,200 5,300 5,400 5,500 5,600


1,250 2,500 3,750 5,000 6,250 7,500 8,750 10,000
0 0 0 0 0 0 0 0
86,950 87,500 88,050 88,600 89,150 89,700 90,250 90,800
102,600 104,400 106,200 108,000 109,800 111,600 113,400 115,200
11,250 12,500 13,750 15,000 16,250 17,500 18,750 20,000
91,350 91,900 92,450 93,000 93,550 94,100 94,650 95,200
31,350 31,900 32,450 33,000 33,550 34,100 34,650 35,200
60,000 60,000 60,000 60,000 60,000 60,000 60,000 60,000

5,700 5,800 5,900 6,000 6,100 6,200 6,300 6,400


11,250 12,500 13,750 15,000 16,250 17,500 18,750 20,000
0 0 0 0 0 0 0 0
91,350 91,900 92,450 93,000 93,550 94,100 94,650 95,200
117,000 118,800 120,600 122,400 124,200 126,000 127,800 129,600
21,250 22,500 23,750 25,000 26,250 27,500 28,750 30,000
95,750 96,300 96,850 97,400 97,950 98,500 99,050 99,600
35,750 36,300 36,850 37,400 37,950 38,500 39,050 39,600
60,000 60,000 60,000 60,000 60,000 60,000 60,000 60,000

6,500 6,600 6,700 6,800 6,900 7,000 7,100 7,200


21,250 22,500 23,750 25,000 26,250 27,500 28,750 30,000
0 0 0 0 0 0 0 0
95,750 96,300 96,850 97,400 97,950 98,500 99,050 99,600
131,400 133,200 135,000 136,800 138,600 140,400 142,200 144,000
31,250 32,500 33,750 35,000 36,250 37,500 38,750 40,000
100,150 100,700 101,250 101,800 102,350 102,900 103,450 104,000
40,150 40,700 41,250 41,800 42,350 42,900 43,450 44,000
60,000 60,000 60,000 60,000 60,000 60,000 60,000 60,000

7,300 7,400 7,500 7,600 7,700 7,800 7,900 8,000


31,250 32,500 33,750 35,000 36,250 37,500 38,750 40,000
0 0 0 0 0 0 0 0
100,150 100,700 101,250 101,800 102,350 102,900 103,450 104,000
145,800 147,600 149,400 151,200 153,000 154,800 156,600 158,400
41,250 42,500 43,750 45,000 46,250 47,500 48,750 50,000
104,550 105,100 105,650 106,200 106,750 107,300 107,850 108,400
44,550 45,100 45,650 46,200 46,750 47,300 47,850 48,400
60,000 60,000 60,000 60,000 60,000 60,000 60,000 60,000

8,100 8,200 8,300 8,400 8,500 8,600 8,700 8,800


41,250 42,500 43,750 45,000 46,250 47,500 48,750 50,000
0 0 0 0 0 0 0 0
104,550 105,100 105,650 106,200 106,750 107,300 107,850 108,400
160,200 162,000 163,800 165,600 167,400 169,200 171,000 172,800
51,250 52,500 53,750 55,000 56,250 57,500 58,750 60,000
108,950 109,500 110,050 110,600 111,150 111,700 112,250 112,800
48,950 49,500 50,050 50,600 51,150 51,700 52,250 52,800
60,000 60,000 60,000 60,000 60,000 60,000 60,000 60,000

8,900 9,000 9,100 9,200 9,300 9,400 9,500 9,600


51,250 52,500 53,750 55,000 56,250 57,500 58,750 60,000
0 0 0 0 0 0 0 0
108,950 109,500 110,050 110,600 111,150 111,700 112,250 112,800
174,600 176,400 178,200 180,000 181,800
61,250 62,500 63,750 65,000 66,250
113,350 113,900 114,450 115,000 115,550
53,350 53,900 54,450 55,000 55,550
60,000 60,000 60,000 60,000 60,000

9,700 9,800 9,900 10,000 10,100


61,250 62,500 63,750 65,000 66,250
0 0 0 0 0
113,350 113,900 114,450 115,000 115,550
THE FLEXIBLE PEN CORP.

202,000

152,000
Sales revenue = Quantity * $20

102,000

Total cost = $16,800 + Quantity * $15

52,000

2,000
0
300
600
900
1,200
1,500
1,800
2,100
2,400
2,700
3,000
3,300
3,600
3,900
4,200
4,500
4,800
5,100
5,400
5,700
6,000
6,300
6,600
6,900
7,200
7,500
7,800
8,100
8,400
8,700
9,000
9,300
9,600
-48,000

Nb. units sold

Break-even point: 2100 units


5 Sales revenue 0 2,300 4,600
6 EBIT -19,000 -18,350 -17,700
7 Total cost 19,000 20,650 22,300
8 Total variable cost 0 1,650 3,300
9 Fixed cost 19,000 19,000 19,000

1 Quantity sold 0 100 200


2 Sales - Total cost > 0 0 0 0
3 Sales - Total cost < 0 19,000 18,350 17,700
4 Min Sales & Total cost 0 2,300 4,600
DATA TABLE
Selling price
Variable cost per unit
Fixed cost
antity * $20 Quantity sold
Operating Income
Operating Income

al cost = $16,800 + Quantity * $15

WHAT IF?
Quantity sold = 3 600 units
Quantity sold = 3 960 units
Quantity sold = 10 000 units
Quantity sold = 11 000 units
The fixed cost increases by 10%, up to
$18,480 and the quantity sold is 10,000
6,600
6,900
7,200
7,500
7,800
8,100
8,400
8,700
9,000
9,300
9,600
9,900

units.
The variable cost increases by 10%, up
to $16.50 and the quantity sold is
10,000 units.
The selling price decreases by 10%,
down to $18 and the quantity sold is
10,000 units.
6,900 9,200### 13,800 16,100
-17,050 -16,400### -15,100 -14,450
23,950 25,600### 28,900 30,550
4,950 6,600### 9,900 11,550
19,000 19,000### 19,000 19,000

300 400### 600 700


0 0 0 0 0
17,050 16,400### 15,100 14,450
6,900 9,200### 13,800 16,100
ABLE
$ 23.00
Instructions: test each of the "What if?" scenarios with
$ 16.50 the Flexible Pen Corp. the same way as you just did
with the Automated Pen Corp.
$ 19,000 When you are done, please move to the Summary
12,000 units results tab.
$ 59,000

Operating Income
$ 1,200
+10%
$ 1,650
$ 33,200
+10%
$ 38,200

$ 36,520

$ 20,020

$ (3,480)
18,400### 23,000 25,300 27,600 29,900
-13,800### -12,500 -11,850 -11,200 -10,550
32,200### 35,500 37,150 38,800 40,450
13,200### 16,500 18,150 19,800 21,450
19,000### 19,000 19,000 19,000 19,000

800### 1,000 1,100 1,200 1,300


0 0 0 0 0 0
13,800### 12,500 11,850 11,200 10,550
18,400### 23,000 25,300 27,600 29,900
32,200 34,500 36,800 39,100 41,400 43,700
-9,900 -9,250 -8,600 -7,950 -7,300 -6,650
42,100 43,750 45,400 47,050 48,700 50,350
23,100 24,750 26,400 28,050 29,700 31,350
19,000 19,000 19,000 19,000 19,000 19,000

1,400 1,500 1,600 1,700 1,800 1,900


0 0 0 0 0 0
9,900 9,250 8,600 7,950 7,300 6,650
32,200 34,500 36,800 39,100 41,400 43,700
46,000 48,300 50,600 52,900 55,200 57,500 59,800 62,100
-6,000 -5,350 -4,700 -4,050 -3,400 -2,750 -2,100 -1,450
52,000 53,650 55,300 56,950 58,600 60,250 61,900 63,550
33,000 34,650 36,300 37,950 39,600 41,250 42,900 44,550
19,000 19,000 19,000 19,000 19,000 19,000 19,000 19,000

2,000 2,100 2,200 2,300 2,400 2,500 2,600 2,700


0 0 0 0 0 0 0 0
6,000 5,350 4,700 4,050 3,400 2,750 2,100 1,450
46,000 48,300 50,600 52,900 55,200 57,500 59,800 62,100
64,400 66,700 69,000 71,300 73,600 75,900 78,200 80,500
-800 -150 500 1,150 1,800 2,450 3,100 3,750
65,200 66,850 68,500 70,150 71,800 73,450 75,100 76,750
46,200 47,850 49,500 51,150 52,800 54,450 56,100 57,750
19,000 19,000 19,000 19,000 19,000 19,000 19,000 19,000

2,800 2,900 3,000 3,100 3,200 3,300 3,400 3,500


0 0 500 1,150 1,800 2,450 3,100 3,750
800 150 0 0 0 0 0 0
64,400 66,700 68,500 70,150 71,800 73,450 75,100 76,750
82,800 85,100 87,400 89,700 92,000 94,300 96,600 98,900
4,400 5,050 5,700 6,350 7,000 7,650 8,300 8,950
78,400 80,050 81,700 83,350 85,000 86,650 88,300 89,950
59,400 61,050 62,700 64,350 66,000 67,650 69,300 70,950
19,000 19,000 19,000 19,000 19,000 19,000 19,000 19,000

3,600 3,700 3,800 3,900 4,000 4,100 4,200 4,300


4,400 5,050 5,700 6,350 7,000 7,650 8,300 8,950
0 0 0 0 0 0 0 0
78,400 80,050 81,700 83,350 85,000 86,650 88,300 89,950
101,200 103,500 105,800 108,100 110,400 112,700 115,000 117,300
9,600 10,250 10,900 11,550 12,200 12,850 13,500 14,150
91,600 93,250 94,900 96,550 98,200 99,850 101,500 103,150
72,600 74,250 75,900 77,550 79,200 80,850 82,500 84,150
19,000 19,000 19,000 19,000 19,000 19,000 19,000 19,000

4,400 4,500 4,600 4,700 4,800 4,900 5,000 5,100


9,600 10,250 10,900 11,550 12,200 12,850 13,500 14,150
0 0 0 0 0 0 0 0
91,600 93,250 94,900 96,550 98,200 99,850 101,500 103,150
119,600 121,900 124,200 126,500 128,800 131,100 133,400 135,700
14,800 15,450 16,100 16,750 17,400 18,050 18,700 19,350
104,800 106,450 108,100 109,750 111,400 113,050 114,700 116,350
85,800 87,450 89,100 90,750 92,400 94,050 95,700 97,350
19,000 19,000 19,000 19,000 19,000 19,000 19,000 19,000

5,200 5,300 5,400 5,500 5,600 5,700 5,800 5,900


14,800 15,450 16,100 16,750 17,400 18,050 18,700 19,350
0 0 0 0 0 0 0 0
104,800 106,450 108,100 109,750 111,400 113,050 114,700 116,350
138,000 140,300 142,600 144,900 147,200 149,500 151,800 154,100
20,000 20,650 21,300 21,950 22,600 23,250 23,900 24,550
118,000 119,650 121,300 122,950 124,600 126,250 127,900 129,550
99,000 100,650 102,300 103,950 105,600 107,250 108,900 110,550
19,000 19,000 19,000 19,000 19,000 19,000 19,000 19,000

6,000 6,100 6,200 6,300 6,400 6,500 6,600 6,700


20,000 20,650 21,300 21,950 22,600 23,250 23,900 24,550
0 0 0 0 0 0 0 0
118,000 119,650 121,300 122,950 124,600 126,250 127,900 129,550
156,400 158,700 161,000 163,300 165,600 167,900 170,200 172,500
25,200 25,850 26,500 27,150 27,800 28,450 29,100 29,750
131,200 132,850 134,500 136,150 137,800 139,450 141,100 142,750
112,200 113,850 115,500 117,150 118,800 120,450 122,100 123,750
19,000 19,000 19,000 19,000 19,000 19,000 19,000 19,000

6,800 6,900 7,000 7,100 7,200 7,300 7,400 7,500


25,200 25,850 26,500 27,150 27,800 28,450 29,100 29,750
0 0 0 0 0 0 0 0
131,200 132,850 134,500 136,150 137,800 139,450 141,100 142,750
174,800 177,100 179,400 181,700 184,000 186,300 188,600 190,900
30,400 31,050 31,700 32,350 33,000 33,650 34,300 34,950
144,400 146,050 147,700 149,350 151,000 152,650 154,300 155,950
125,400 127,050 128,700 130,350 132,000 133,650 135,300 136,950
19,000 19,000 19,000 19,000 19,000 19,000 19,000 19,000

7,600 7,700 7,800 7,900 8,000 8,100 8,200 8,300


30,400 31,050 31,700 32,350 33,000 33,650 34,300 34,950
0 0 0 0 0 0 0 0
144,400 146,050 147,700 149,350 151,000 152,650 154,300 155,950
193,200 195,500 197,800 200,100 202,400 204,700 207,000 209,300
35,600 36,250 36,900 37,550 38,200 38,850 39,500 40,150
157,600 159,250 160,900 162,550 164,200 165,850 167,500 169,150
138,600 140,250 141,900 143,550 145,200 146,850 148,500 150,150
19,000 19,000 19,000 19,000 19,000 19,000 19,000 19,000

8,400 8,500 8,600 8,700 8,800 8,900 9,000 9,100


35,600 36,250 36,900 37,550 38,200 38,850 39,500 40,150
0 0 0 0 0 0 0 0
157,600 159,250 160,900 162,550 164,200 165,850 167,500 169,150
211,600 213,900 216,200 218,500 220,800 223,100 225,400 227,700
40,800 41,450 42,100 42,750 43,400 44,050 44,700 45,350
170,800 172,450 174,100 175,750 177,400 179,050 180,700 182,350
151,800 153,450 155,100 156,750 158,400 160,050 161,700 163,350
19,000 19,000 19,000 19,000 19,000 19,000 19,000 19,000

9,200 9,300 9,400 9,500 9,600 9,700 9,800 9,900


40,800 41,450 42,100 42,750 43,400 44,050 44,700 45,350
0 0 0 0 0 0 0 0
170,800 172,450 174,100 175,750 177,400 179,050 180,700 182,350
230,000 232,300
46,000 46,650
184,000 185,650
165,000 166,650
19,000 19,000

10,000 10,100
46,000 46,650
0 0
184,000 185,650
SUMMARY RESULTS: OPERATING INCOME OF BOTH COMPANIES UNDER DIFFERENT SCENARIOS

Observe this table (filled in with the Operating Income you computed in the previous tabs).

The Automated Pen Corp. The Flexible Pen Corp. Give a thought to the following questions below :

1) Which company is systematically the most profitable one?

2) A 10% increase in the quantity sold systematically brings more Operating Income. But do the Operating
Sc. 1 Quantity sold = 3 600 units $ 3,600 $ 1,200 Income grow faster when the companies are "close to" or "far from" their breakeven point?
+10%
Sc. 2 Quantity sold = 3 960 units $ 9,000 $ 1,650 3) The Automated Pen Corp.

a. Is the Automated Pen Corp. more sensitive to a fixed cost increase or a variable cost increase?

Sc. 3 Quantity sold = 10 000 units $ 99,600 $ 33,200 b. What are the keys to maintain high profits in this company: high volumes? low costs? high prices?

+10%
Sc. 4 Quantity sold = 11 000 units $ 114,600 $ 38,200

4) The Flexible Pen Corp.

Sc. 5 The fixed cost increases by 10% and the


quantity sold is 10,000 units. $ 94,560 $ 36,520 a. Is the Automated Pen Corp. more sensitive to a fixed cost increase or a variable cost increase?

Sc. 6 The variable cost increases by 10% and the


quantity sold is 10,000 units. $ 89,560 $ 20,020 b. What are the keys to maintain high profits in this company: high volumes? low costs? high prices?

Sc. 7 The selling price decreases by 10% and the


quantity sold is 10,000 units. $ 69,560 $ (3,480)

You might also like