parts D &E

COMPSYS is a start-up computer company. This year, the lirst year of operations, the company expects to reach a Sales Revenve of $750,000. The company expects its sales to grow at a rate of 15% per yoar. its Cost of Goods Sold (COGS) is running at 34% of Sales Revenue, and is expected to remain at that rate. It Selling Costs are 12% of Sales in the first year and are expected to increase by an addtional 3% per year after that (increases to 15% the following year). Its General and Administrative Costs (including Research and Development) are $410,000 the first year, and are scheduled to rise at 4% every year after that. Eamings Before Taxes (EBT) is Sales Revenue less Cost of Goods Sold. Selling Costs, and General and Administrative Costs. Taxes are 25\% of Earnings Before Taxes, if the Earnings Belore Taxes are greater than zero but less than or equal to $50,000. If eamings are greater than $50,000 per year, then the tax rate is 35%. Use IF statements for computing taxes. a) Design and implement a worksheot to make a Syear income statement projection for COMPSYS starting from this year. First, plan the format and layout of your worksheet areas. You should have separate areas for documenting the spreadsheet, indicating areas of the worksheet, and identilying assumptions with welllabeled separate cells for each of the growth rates and proportional Gactors. You should have a separate row for each item in the income statement and a separate column for each year. The final row should be for Eamings After Taxes. Use the Fill operation wherever possible. b) Calculate the Net Present Value (NPV) of the Earnings After Taxes for the 5 years. Use a Discount Rate of 7%. Provide a label to indicate the results and place the value at the bottom of your worksheet. c) Format your spreadsheet in an attractive manner. The first sheot should contain a brief documentation of the software package that you develop. The second sheet should be the EXCEL model. Here, the assumptions (given in the initial paragraph) should be first stated lollowed by the actual spreadsheet. The third sheet should contain the graphs you generate. d) The management feels that the estimate for the first year's Sales Revenue, Cost of Goods Sold, Selling Costs, and General and Administrative Expenses may not be as certain. Since these four items are critical to the success of the operations, the management would like you to perlorm a sensitivity analysis to see what would the NPV look like when these numbers fluctuate within a range of +i- 20% of the estimate. That is, provide estimates of costs and revenues for this range which should be 80,85,90, 95,100,105,110,115, and 120% of the standard estimates. (Hint When you are formulating for these four items, add a certainty factor to the formulas. The certainty factor should not be hardooded in the formulas. Always keep the input assumptions in a separate area in your worksheet. Use the Create Data Table under Data to perform the sensitivity andlysis.) e) Produce the following graphs to present to the management: Sales and total expenses over the five-year period. Mark where the business starts to make a profit: - The impact of fluctuations of the initial estimates for the cost of goods sold peroentage; sales revenue; and the general and administrative expenses on the NPV, (Hint: The horizontal axis should be from 80% to 120% of the original estimates. whilst the vertical axis should be the NPV, Remember, the 100% point is your original estimates. The curves for sales, cost of goods sold, and the general and administrative expenses should be on the same graph.)