Question

Please show excel calculations

Future value and present value concepts are applied in various ways, such as calculating growth rates, earnings per share, expected sales and revenues in the future, and so forth.

Consider the following case:

Pharmacist John S. Pemberton invented a soft drink in 1886 that eventually became not only an integral part of everyday life in the United States but also a symbol of consumerism worldwide. In 1929 the first Coca-Cola vending machines were installed in Germany, and in 1930, the German branch of the Coca-Cola Co. opened in Essen.

Coca-Cola sales in Germany were 243,000 cases in 1934, 1 million cases in 1936, and 4.5 million cases in 1939.

Germany was a growing market for Coca-Cola, along with other countries in Europe, before World War II. With the previous data given, calculate the company’s sales growth rate for each time period in the following table:

Years

Growth Rate

1934–1936   1936–1939   1934–1939   

During World War II, Coca-Cola Co. cut off all syrup sales to Germany in 1940, resulting in no sales from 1943 to 1945. If Coca-Cola’s sales had grown from 1939 to 1945 at the same rate that they grew between 1934 and 1939, its sales in 1945 would have been approximately   cases. (Hint: Use sales data from 1939 as the present value.)

Coca-Cola’s worldwide sales as of December 31, 2011, was 26.7 billion cases. Assume the following sales distribution:

Unit Case Volume

Eastern Europe	20%
Germany	16%
Spain	14%
Great Britain	12%
Italy	9%
France	8%
Other	21%

If Coca-Cola’s worldwide growth were to continue at the same growth rate as it did in Germany between 1939 and 2008, when sales grew at the rate of 10.26%, its hypothetical sales in Eastern Europe in 2053 (42 years from 2011) would be approximately billion cases. (Note: For this question, ignore other factors that affect sales.)

Answer 1

Enter the below formula in Excel

1.
=RATE(1936-1934,0,-243000,1000000)=102.860206483395%

2.
=RATE(1939-1936,0,-1,4.5)=65.0963624447529%

3.
=RATE(1939-1934,0,-243000,4500000)=79.2756261554284%

4.
=FV(RATE(1939-1934,0,-243000,4500000),6,0,-4500000)=149396355.129524

5.
=FV(10.26%,42,0,-26.7*20%)=322.921349721242