Aveena Company is a small firm that produces a range of low-priced small to medium-sized data storage devices for a well-known online seller of computer equipment. Aveena's Income Statement as of 12/31/2018 is shown here: Aveena Company Income Statement (in $000) 2018 $2,567 $1.711 $856 Sales Cost of Goods Sold Gross Profit (Margin) Less: Operating Expenses Selling expense General $ Admin exp Lease expense Depreciation expense Total Operating Expenses Operating Income (EBIT) Less: Interest expense Income before taxes (EBT) Less: Taxes Net Income $108 $187 $35 $223 $553 $303 $91 $212 $64 $148 $1.95 Earnings per share (EPS) Dividends per shapre (DPS) $0.97 Aveena is in the early stages of preparing its 2019 Pro Forma Income Statement and is considering an expansion in the range of products it carries which will require the firm to secure additional capital financing to support this expansion. The Chief Operating Officer (COO) has asked you, as Aveena's financial analyst intern, to prepare a projection for the 2019 1/S based on the 2019 projections listed below. The firm is considering raising the additional financing either through additional long term debt (option A) or selling additional common stock that has been authorized but not issued (option B). One of the items the COO wishes to investigate is the firms Cost of Good Sold. Cost of Goods sold includes all the direct and indirect costs associated with the manufacture of the firm's product line. These costs include all raw materials, labor, and manufacturing overhead that is directly associated with product manufacture. Aveena's product line is currently manufactured in a plant in Southeast Asia, in order to minimize production cost and leverage the technology manufacturing and assembly expertise available in SE Asia. The COO has stated that he believes Aveena's primary market competition is currently operating at a COGS rate of 63%. Assignment: 1. Prepare a pro forma 2019 Income Statement (Option A) based on the following projections: a. A projected sales increase of 20% based on the product line expansion b. 2019 expense projections: i. COGS will remain at the 2018 % ii. Selling expense will increase 15% iii. General/admin expenses will increase 5% iv. Additional financing through long-term debt at 4% interest will result in an increase in interest expense. Aveena anticipates needing an additional $2,000,000 of long-term debt to support the sales projection. V. Tax rate 30% c. 76,000 shares of stock are currently issued d. Dividend payout ratio = 50% 2. Prepare a "what-if" Pro Forma 1/S for option B based on the following scenario: a. Instead of increasing long term debt, the firm raises the additional capital by issuing and additional 5,000 shares of common stock b. Calculate the revised net income, EPS and DPS All other projections as indicated in #1 remain. C. 3. Provide a Cost of Goods illustration of the impact on Net Income if the firm was able to lower COGS to the competitor's average, under Option A financing. Illustrate the change in Net Income on both a $ and % change basis. 4. Based on your analysis provide a 300-400 word narrative analysis for the COO with your recommendation to include the following: a. Analysis of impact on firm income statement comparing option A and B including impact on shareholders and the financial leverage of the firm (see Chapter 2, Financial Leverage). What happens under option A if the projected sales increase does not materialize and 2019 Sales is the same or less than 2018 Sales? b. Discuss the impact on Net Income from the change in COGS, and suggest items that the COO should examine in order to reduce Aveena's current COGS. In your analysis use $ values and % change values to support your statements. Prepare your analysis in Word or similar program. Then, Insert the analysis into your spreadsheet. You can: 1. Use "Insert-textbox", resize textbox and paste the narrative into textbox. 2. Use the "Insert - Object" Excel feature to directly insert the Word file into your spreadsheet. Excel set up for this problem: . Notice the image below, how I set up the columns for the pro forma I/S Notice that the account values are listed just once. No need to prepare a full l/S for each option, the account titles do not change, just the $ values Use Excel formulas to derive updated values and calculations Aveena Company Income Statement (in $000) 2018 2019 Pro Forma Income Statements Option A Option B COGS $2,567 $1.711 $856 Sales Cost of Goods Sold Gross Profit (Margin) Less: Operating Expenses Selling expense General $ Admin exp Lease expense Depreciation expense Total Operating Expenses Operating Income (EBIT) Less: Interest expense Income before taxes (EBT) Less: Taxes Net Income $108 $187 $35 $223 $553 $303 $91 $212 $64 $148 Earnings per share (EPS) Dividends per shapre (DPS) $1.95 $0.97 50%