Using the PowerPoint Lecture ?Creating Projections,? create a new set of projections for Years X E and X +1 E.

For this Homework Assignment, your work should be completed on the Week 7 Homework Worksheet (an Excel File - Provided as a separate file in this Week 7 Content Folder). Please make sure you put your name in the appropriate box on the worksheet!

All historical information remains the same (through Year X-1).

im going to attach the excel document you must use the same document so i can see the formulas used

Use this new set of assumptions:

• Revenue growth:

30% Year X E

30% Year X + 1 E

• 1% improvement in Gross Margin for Year X E (from year X-1). No further change in the Gross Margin for Year X+1.
• 1% improvement in Selling in Year X E. Same level in Year X+1 E.
• G&A remains constanton a common size basis.
• $10,000 increase in R&D in X E.
• Same R&D level in Year X+1 E.
• Basic Shares outstanding ? 3,000,000.
• A new section of the existing plant is constructed for $300,000. This new construction is depreciated over 30 years using the straight line method. All other depreciation remains the same for the years X E and X+1 E.
• The plant is financed with a $300,000 10 year bond with a fixed interest payment of 10% annually.
• The Company, for the first time, grants 500,000 options to various members ofmanagement with an exercise price of $1.00. These options are granted on the first day of Year X E.
• There are no other options, warrants or convertible securities.
• The average stock price for Year X E and Year X+1 E is projected to be $5.00.
• All other interest payments, interest income and tax rates remain the same.

Your Name Here Revenue Cost of Goods Sold Gross Profit $ Year X - 1 1,440,000 921,600 518,400 Year X E 100.0% 64.0% 36.0% Selling General and Administrative Research and Development Depreciation and Amortization Total Operating Expenses 158,400 108,000 40,000 30,000 336,400 23.4% Operating Income 182,000 12.6% Interest costs Interest income 5,000 4,000 0.3% Pre-Tax Income Tax (35%) Net Income 12.6% $ 181,000 63,350 117,650 $ 0.04 Earnings Per Share - Basic Earnings Per Share - Diluted Shares Outstanding Shares For Diluted A Options Ex Price Stock Price Proceeds New Share structure Buy Back Shares Shares for Diluted Calculations Revenue Gross Margin Depreciation and Amortization 11.0% 7.5% 2.8% 2.1% 0.3% 4.4% 8.2% 3,000,000 $ $ $ 500,000 1.00 5.00 500,000 3,500,000 100,000 3,400,000 $ Year X E - Operating Income Operating Margin Interest Expense Net Income Basic EPS Diluted EPS Shares used to calculate Diluted EPS - $ $ $ - Year X + 1 E Year X + 1 E $ - - $ $ $ - Your Name Here Revenue Cost of Goods Sold Gross Profit $ Year X - 1 1,440,000 921,600 518,400 Selling General and Administrative Research and Development Depreciation and Amortization Total Operating Expenses 158,400 108,000 40,000 30,000 336,400 Operating Income 100.0% $ 64.0% 36.0% Year X E 1,872,000 1,179,360 692,640 100.0% 63.0% 37.0% 12.0% 23.4% 224,640 108,000 50,000 30,000 412,640 182,000 12.6% 280,000 15.0% Interest costs Interest income 5,000 4,000 0.3% 35,000 4,000 1.9% Pre-Tax Income Tax (35%) Net Income 12.6% $ 249,000 87,150 161,850 13.3% $ 181,000 63,350 117,650 $ 0.04 $ 0.05 Earnings Per Share - Basic Earnings Per Share - Diluted Shares Outstanding Shares For Diluted A Options Ex Price Stock Price Proceeds New Share structure Buy Back Shares Shares for Diluted Calculations Revenue Gross Margin Depreciation and Amortization $ $ $ 11.0% 7.5% 2.8% 2.1% 0.3% 4.4% 8.2% 3,000,000 3,000,000 500,000 1.00 5.00 500000 1.00 5.00 500,000 3,500,000 100,000 3,400,000 500000 3500000 100000 3400000 $ Year X E 1,872,000 30,000 5.8% 2.7% 1.6% 22.0% 0.2% 4.7% 8.6% Operating Income Operating Margin Interest Expense Net Income Basic EPS Diluted EPS Shares used to calculate Diluted EPS 280,000 $ $ $ 35,000 161,850 0.05 0.05 3,400,000 Year X + 1 E $ 2,433,600 1,533,168 900,432 100.0% 63.0% 37.0% 292,032 108,000 50,000 30,000 480,032 12.0% 420,400 17.3% 35,000 4,000 1.4% 16.0% $ 389,400 136,290 253,110 $ 0.08 3,000,000 500000 1.00 5.00 500000 3500000 100000 3400000 Year X + 1 E $ 2,433,600 30,000 4.4% 2.1% 1.2% 19.7% 0.2% 5.6% 10.4% 420,400 $ $ $ 35,000 253,110 0.08 0.07 3,400,000 Your Name Here Revenue Cost of Goods Sold Gross Profit $ Year X - 1 1,440,000 921,600 518,400 Selling General and Administrative Research and Development Depreciation and Amortization Total Operating Expenses 158,400 108,000 40,000 30,000 336,400 Operating Income 100.0% $ 64.0% 36.0% Year X E 1,872,000 1,179,360 692,640 100.0% 63.0% 37.0% 12.0% 23.4% 224,640 108,000 50,000 30,000 412,640 182,000 12.6% 280,000 15.0% Interest costs Interest income 5,000 4,000 0.3% 35,000 4,000 1.9% Pre-Tax Income Tax (35%) Net Income 12.6% $ 249,000 87,150 161,850 13.3% $ 181,000 63,350 117,650 $ 0.04 $ 0.05 Earnings Per Share - Basic Earnings Per Share - Diluted Shares Outstanding Shares For Diluted A Options Ex Price Stock Price Proceeds New Share structure Buy Back Shares Shares for Diluted Calculations Revenue Gross Margin Depreciation and Amortization $ $ $ 11.0% 7.5% 2.8% 2.1% 0.3% 4.4% 8.2% 3,000,000 3,000,000 500,000 1.00 5.00 500000 1.00 5.00 500,000 3,500,000 100,000 3,400,000 500000 3500000 100000 3400000 $ Year X E 1,872,000 30,000 5.8% 2.7% 1.6% 22.0% 0.2% 4.7% 8.6% Operating Income Operating Margin Interest Expense Net Income Basic EPS Diluted EPS Shares used to calculate Diluted EPS 280,000 $ $ $ 35,000 161,850 0.05 0.05 3,400,000 Year X + 1 E $ 2,433,600 1,533,168 900,432 100.0% 63.0% 37.0% 292,032 108,000 50,000 30,000 480,032 12.0% 420,400 17.3% 35,000 4,000 1.4% 16.0% $ 389,400 136,290 253,110 $ 0.08 3,000,000 500000 1.00 5.00 500000 3500000 100000 3400000 Year X + 1 E $ 2,433,600 30,000 4.4% 2.1% 1.2% 19.7% 0.2% 5.6% 10.4% 420,400 $ $ $ 35,000 253,110 0.08 0.07 3,400,000