ANSWER EXCEL FORMAT PLEASE

MINI CASE: GILEAD SCIENCE CORPORATION

The Gilead Science Corporation spent several years working on developing a product that can be used to provide a healthy supplement to a variety of food products. The benefits of a new supplement have been cited in studies of the brain, eyes, and the immune system. Unfortunately, it is difficult to consume enough of any one supplement to get the benefits that most consumers demand. To counter this constraint, Gilead Science Corp. and several competitors have been able to develop a similar product with various benefits. However, none have been able to produce the ultimate solution.

Gilead Science Corp.s initial product was designed to supply additives to dairy products and yogurt. For example, the ventures new product was added to cottage cheese and fruit-flavored yogurts to enhance the health benefits of those products. After the long product development period, Gilead Science Corp. began operations in 2014. Income statement and balance sheet results for 2015, the first full year of operations, have been prepared.

Gilead Science Corp., however, is concerned with forecasting its financial statements for next year because it is uncertain as to the amount of additional financing of assets that will be needed as the venture ramps up sales next year. Gilead Science Corp. expects to introduce a new product that offers the taste of chocolate candies. Not only will consumers get the satisfaction of the taste of chocolate candies they will benefit from the vitamin enhancement. What are considered to be conservative, sales are expected to increase 50 percent next year (2016) even though the new product will come on line in mid-year. An additional 80 percent increase in sales is expected the following year (2017), 100 percent in (2018), 50 percent in (2019), before leveling off to a projected future annual growth rate of 20 percent beginning with (2020).

______________________________

GILEAD SCIENCE CORPORATION

Income Statement for December 31, 2015

(Thousands of Dollars)

__________________________________

Sales $15,000

Operating expenses -13,000

EBIT 2,000

Interest 400

EBT 1,600

Taxes (40%) 640

Net income 960

Cash dividends (40%) 384

Added retained earnings $576

GILEAD SCIENCE CORPORATION

Balance Sheet as of December 31, 2015

(Thousands of Dollars) ________________________________________________________________________

Cash & marketable securities $ 1,000 Accounts payable $ 1,600

Accounts receivable 2,000 Bank Loan 1,800

Inventories 2,200 Accrued liabilities 1,200

Total current assets 5,200 Total current liabilities 4,600

Long-term debt 2,200

Fixed assets, net 6,800 Common stock 2,400

Total assets $12,000 Retained earnings 2,800

Total liabilities & equity $12,000 ________________________________________________________________________

A.Estimate the additional funds needed (AFN) for 2016, using the formula method based on percent of sales relationships.

B.Estimate the AFN for GILEAD SCIENCE for 2017.

C.Prepare pro forma income and balance sheet statements for 2016 before obtaining any additional financing. Why does the AFN from the spreadsheet projections differ from the AFN estimated in Part A?

D.Prepare a second iteration of your pro forma financial statements for 2016 if the initial AFN estimate is to be financed by additional long-term funds at an 10 percent interest rate.

E.Prepare pro forma financial statements for 2017 that build on to the pro forma results obtained in Part D.

F. Prepare projected income statements, balance sheets, and statements of cash flow for GILEAD SCIENCE for 2016 and 2017 before obtaining of any additional financing. What are the amounts of additional funds needed?

G. Assume that sales are expected to grow 100 percent percent in 2018 (over the 2017 sales level), 50 percent in 2019, and 20 percent in 2020. Extend your projected financial statements prepared in Part E to include years 2018, 2019, and 2020. What will be the maximum amount of additional funds needed during your five-year forecast?

H. Assume that you will acquire the amount funds needed in Part F by selling or issuing more common stock and by borrowing from lenders at an 10 percent interest rate. Prepare a second round of projected five-year financial statements showing that the initial financing needed will be obtained equally each year by issuing new stock (50 percent) and by borrowing from lenders (50 percent).