GLAXO.
XLS
This spreadsheet supports student analysis of the case,
"Glaxo Italia S.p.A.: The Zinnat Marketing Decision" (UVA -F1014 v. 3.1)
Please note:
This is a working model. Assumptions / Inputs presented can
be changed to vary the results.
Revised: March 1, 2000.
Copyright (C) 1998 by the Trustees of the University of Virginia Darden School Foundation
School Foundation
Exhibit 8
Forecast Assumptions
1990
1990
Price to
Pills sold by Glaxo
Syrup sold by Glaxo
Pills sold by Licensee
Syrup sold by Licensee
Years from Sept. 1990
Transfer price of ingredients per gram (lira)
Glaxo Price to customer - pills (lira)
Glaxo price to customer - syrup (lira)
Gross margin/direct sales, Glaxo Italia
Gross margin/ingredient sales, Glaxo Holdings
Sales force #1 -- salespeople
% time on Zinnat (if direct marketing)
% time on Zinnat (if co-marketing)
Sales force #2 -- salespeople
% time on Zinnat (if direct marketing)
% time on Zinnat (if co-marketing)
Cost per sales rep (millions of lira)
Transfer Price
Of Ingredients
First Year
Years 2+
Retail Cust.
Key Raw
Price to
Product Mix
lira
Materials (gr)
Retailer
80%
20%
40%
60%
(lira/gr)
34,400.0
29,880.0
3.0
2.5
20,984.0
18,226.8
1,566.0
1,566.0
1993
1994
1995
1996
47.0%
29.0%
0.0%
5.0%
12.0%
4.0%
25.0%
4.0%
16.7%
12.5%
1990
Market forecast of antibiotic demand (millions of units)
Market share for Zinnat, direct sales strategy
Market share for Zinnat, co-marketing strategy
% Zinnat volume sold by Glaxo, co-marketing strategy
1990
Manufacturer
Product Mix
85%
15%
40%
60%
Marginal tax rate - Italy
Marginal tax rate - UK
Expected inflation rate - Italian lira
Expected inflation rate - UK, pounds, sterling
Yearly percentage increase in cost per sales rep.
Yearly decline in market share after year 6
Savings in promotional effort direct sales vs. co-marketing
Licensee fee (% of Zinnat revenues to licensee)
Working capital required/Sales
Glaxo Holdings, interest rate and internal charge for capital
Content of
0.0%
0.0%
0.25
1,566.0
20,984.0
18,226.8
80%
1991
1992
52.3
5.1%
9.0%
45.35%
46.6
9.0%
13.0%
61.79%
46.6
9.1%
12.5%
63.78%
44.6
9.1%
11.5%
67.40%
46.3
9.1%
11.0%
71.38%
47.2
9.1%
10.0%
71.63%
1.25
1,566.0
20,984.0
18,226.8
53%
80%
2.25
1,566.0
20,984.0
18,226.8
53%
80%
3.25
1,566.0
20,984.0
18,226.8
53%
80%
4.25
1,566.0
20,984.0
18,226.8
53%
80%
5.25
1,566.0
20,984.0
18,226.8
53%
80%
6.25
1,566.0
20,984.0
18,226.8
53%
80%
320
20%
26%
320
13%
18%
105.0
350
25%
33%
400
18%
24%
400
16%
21%
400
17%
23%
440
17%
23%
117.6
131.7
147.5
165.2
185.0
Exhibit 9
Financial Forecast: Direct Sales
1990
1991
1992
1993
1994
1995
1996
1958
710
3491
700
3644
600
3656
400
3816
400
3948
350
40.28
21.4
71.34
37.8
74.46
39.5
74.70
39.6
77.97
41.3
80.67
42.8
3.2
0.7
5.1
0.4
10.2
2.7
0.7
4.5
0.4
9.5
1.8
0.7
2.2
1.2
9.3
1.8
0.8
2.4
1.2
11.4
1.6
2.5
1.3
14.0
0.7
-0.7
3.3
0.4
5.6
1.4
6.6
5.9
23.1
-1.8
19.6
18.2
17.9
21.6
15.3
24.3
17.7
23.7
19.4
23.3
0.0
0.0
9.8
-0.1
9.7
15.2
0.1
15.3
15.4
3.6
19.0
14.7
8.6
23.4
15.3
14.6
29.9
15.6
21.2
36.8
Profit: Zinnat product line
Investment in NWC
Capital Generated
-0.7
0.0
-0.7
7.9
6.7
1.2
33.5
5.2
28.3
40.6
0.5
40.1
47.7
0.0
47.6
53.5
0.5
53.0
60.1
0.4
59.7
Cumulative Capital
Used (-) or Generated (+)
-0.7
0.5
28.8
68.9
116.6
169.6
229.2
Quantities of units (000)
volume sold direct
samples
Glaxo Italia: Profit
Revenues
Gross margin
Marketing expense, direct sales:
Samples expense
Medical promotions (trials)
Seminars, congresses, etc.
Sales force training
Compensation: sales force #1
Compensation: sales force #2
Total marketing expense
Profit: Direct sales
Glaxo Holdings
Profit on sales of ingredients to Glaxo Italia
Capital charge (-), income(+)
Profit: Direct sales
IRR on Capital Employed
Payback Period
0.0
0.2
0.2
0.3
690%
1.1 years
Exhibit 10
Financial Forecast: Co-Marketing
(billions ITL)
1990
1991
1992
1993
1994
1995
1996
50
200
250
1,813
710
2,895
5,419
3,308
700
2,746
6,754
3,336
600
2,494
6,430
3,185
400
1,941
5,526
3,352
400
1,744
5,496
3,132
350
1,591
5,073
0.0
0.0
37.3
19.8
67.6
35.8
68.2
36.1
65.1
34.5
68.5
36.3
64.0
33.9
0.2
0.2
0.2
1.0
3.2
0.7
4.7
0.3
13.6
2.7
0.7
4.1
0.3
12.6
1.8
0.7
2.0
1.0
12.4
1.8
0.7
2.1
1.1
15.2
1.6
1.9
1.0
18.7
1.6
-1.6
3.3
0.4
5.2
1.3
8.7
5.9
24.8
-5.0
22.5
13.3
20.5
15.6
17.9
16.6
20.9
15.4
23.2
10.7
1.1
0.8
0.2
22.9
18.3
2.2
28.6
22.8
2.1
27.2
21.7
1.9
23.4
18.7
1.5
23.2
18.6
1.3
21.4
17.2
1.2
Capital charge (-), income(+)
Profit: Zinnat product line
Change in working capital
Capital Generated
0.0
-0.6
0.2
-0.8
-0.1
15.5
9.9
5.6
0.6
38.9
6.0
32.9
4.7
44.0
-0.1
44.2
10.2
47.1
-1.2
48.2
16.3
51.7
0.5
51.1
22.6
51.8
-1.0
52.8
Cumulative Capital
Used (-) or Generated (+)
-0.8
4.8
37.7
81.9
130.1
181.2
234.0
Quantities of units (000)
volume sold direct by Glaxo
samples distributed free
sold by licensees
Total
Glaxo Italia: Profit
Direct sales by Glaxo Italia
Gross margin, direct sales
Marketing expense, direct sales:
Samples expense
Medical promotions (trials)
Seminars, congresses, etc.
Sales force training
Compensation: sales force #1
Compensation: sales force #2
Total marketing expense
Profit: Direct sales, Glaxo Italia
Glaxo Holdings: Profit and Fees
Ingredient sales to Glaxo Italia and licensee
Profit: ingredient sales, Glaxo Hldgs.
Fee from licensee
IRR on Capital Employed
Payback Period
1013%
0.2 year
Venta Directa
EBIT (Utilidad operativa)
GLAXO
Impuesto Italia (47%)
ITALIA
Utilidad Neta
EBIT (Utilidad operativa)
GLAXO I+D
HOLDING Impuesto Reino Unido (29%)
Utilidad Neta
Utilidad Total
Depreciacion
CAPEX (Inversion de Capital)
Variacion de Capital de trabajo
Valor terminal
Flujo
Flujo Final
TIR
Tasa de descuento
VAN
IR
PAYBACK
Venta Conjunta
EBIT (Utilidad operativa)
GLAXO
Impuesto Italia (47%)
ITALIA
Utilidad Neta
EBIT (Utilidad operativa)
GLAXO I+D
HOLDING Impuesto Reino Unido (29%)
Utilidad Neta
Utilidad Total
Depreciacion
CAPEX (Inversion de Capital)
Variacion de Capital de trabajo
Valor terminal
Flujo
Flujo Final
TIR
Tasa de descuento
VAN
IR
1989
1990
(0.7)
(0.3)
(0.4)
0.0
1991
(1.8)
(0.8)
(0.9)
9.7
0.0
0.0
(0.4)
2.8
6.9
5.9
0.0
6.7
(0.4)
(0.4)
(0.8)
(0.8)
539.54%
13%
193.0
520.17
mas de 6 aos
1990
(1.6)
(0.8)
(0.9)
1.0
1991
(5.0)
(2.3)
(2.6)
20.5
0.3
0.7
(0.2)
5.9
14.5
11.9
0.2
9.9
(0.3)
(0.3)
2.0
2.0
1078.61%
13%
195.6
593.8
PAYBACK
mas de 6 aos
1992
18.2
8.6
9.7
15.3
1993
21.6
10.2
11.4
19.0
1994
24.3
11.4
12.9
23.4
1995
23.7
11.1
12.5
29.9
1996
23.3
11.0
12.4
36.8
4.4
10.9
20.5
5.5
13.5
25.0
6.8
16.6
29.5
8.7
21.2
33.8
10.7
26.1
38.5
5.2
0.5
0.0
0.5
15.3
15.3
24.4
24.4
29.4
29.4
33.2
33.2
0.4
221.4
38.0
259.4
1992
13.3
6.3
7.1
25.6
1993
15.6
7.4
8.3
28.4
1994
16.6
7.8
8.8
30.4
1995
15.4
7.3
8.2
36.2
1996
10.7
5.0
5.7
41.0
7.4
18.2
25.2
8.2
20.2
28.4
8.8
21.6
30.4
10.5
25.7
33.9
11.9
29.1
34.8
6.0
19.2
19.2
(0.1)
(1.2)
28.6
28.6
31.6
31.6
0.5
33.3
33.3
(1.0)
208.7
35.9
244.6
1997
36.5
1997
34.4 Decrecimiento de 4%
ecimiento de 4%
