Question

In: Finance

COMPSYS is a start-up computer company. This year, the first year of operations, the company expects...

COMPSYS is a start-up computer company. This year, the first year of operations, the company expects to reach a Sales Revenue of $750,000. The company expects its sales to grow at a rate of 15% per year. Its Cost of Goods Sold (COGS) is running at 34% of Sales Revenue, and is expected to remain at that rate. Its Selling Costs are 12% of Sales in the first year and are expected to increase by an additional 3% per year after that. Its General and Administrative Costs (including Research and Development) are $410,000 this year, and are scheduled to rise at 4% every year after that. Earnings 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 Before Taxes are greater than zero but less than or equal to $50,000. If earnings are greater than $50,000 per year, then the tax rate is 35%. Use IF statements for computing taxes.

a)        Design and implement a worksheet to make a 5-year 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 identifying assumptions with well-labeled separate cells for each of the growth rates and proportional factors. 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 Earnings 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 sheet 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 followed by the actual spreadsheet. The third sheet should contain the graphs you generate.

Optional (the following sections: d and e are optional but will be awarded bonus points if completed correctly)

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 perform a sensitivity analysis to see what would the NPV look like when these numbers fluctuate within a range of +/- 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 hardcoded 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 analysis.)

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 percentage; 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 in the same graph.)

Solutions

Expert Solution

COMPSYS
INCOME STATEMENT
YEAR 0 1 2 3 4 5
SALES 750000.00 862500.00 991875.00 1140656.25 1311754.69 1508517.89
Less:
COST OF GOODS SOLD 255000.00 293250.00 337237.50 387823.13 445996.59 512896.08
SELLING COST 90000.00 92700.00 95481.00 98345.43 101295.79 104334.67
GENERAL & ADMINISTRATIVE COST 410000.00 426400.00 443456.00 461194.24 479642.01 498827.69
EBT -5000.00 50150.00 115700.50 193293.46 284820.29 392459.45
TAX -1250.00 17552.50 40495.18 67652.71 99687.10 137360.81
NET INCOME -3750.00 32597.50 75205.33 125640.75 185133.19 255098.64
DISCOUNT RATE (7%) 0.934 0.873 0.816 0.763 0.713
DICOUNTED CASH FLOW -3750.00 30446.07 65654.25 102522.85 141256.62 181885.33
NPV = NET CASH INFLOW -NET CASH OUTFLOW
NPV = 518015.12
SALES GROWTH RATE 15%
COGS = 34% OF SALES
SELLING COST 3% GROWTH
GENERAL & ADMINISTRATIVE COST = 4% GROWTH
A NEGATIVE EBT REDUCES TAX AND THEREFORE FORMS A BENEFIT FOR THE ORGANISATION. THEREFORE, TAX IS DEDUCTED FROM SUCH A GAIN

Related Solutions

__________ manages basic computer operations such as start-up, controls access to system resources, and manages computer...
__________ manages basic computer operations such as start-up, controls access to system resources, and manages computer memory and files. Event data recorders Application software Software defined networking System software
Baird Company, which expects to start operations on January 1, 2018, will sell digital cameras in...
Baird Company, which expects to start operations on January 1, 2018, will sell digital cameras in shopping malls. Baird has budgeted sales as indicated in the following table. The company expects a 14 percent increase in sales per month for February and March. The ratio of cash sales to sales on account will remain stable from January through March. Required Complete the sales budget by filling in the missing amounts. Determine the amount of sales revenue Baird will report on...
Solomon Company, which expects to start operations on January 1, 2018, will sell digital cameras in...
Solomon Company, which expects to start operations on January 1, 2018, will sell digital cameras in shopping malls. Solomon has budgeted sales as indicated in the following table. The company expects a 10 percent increase in sales per month for February and March. The ratio of cash sales to sales on account will remain stable from January through March. Required Complete the sales budget by filling in the missing amounts. Sales January February March Cash sales $47,000 Sales on account...
Solomon Company, which expects to start operations on January 1, 2018, will sell digital cameras in...
Solomon Company, which expects to start operations on January 1, 2018, will sell digital cameras in shopping malls. Solomon has budgeted sales as indicated in the following table. The company expects a 15 percent increase in sales per month for February and March. The ratio of cash sales to sales on account will remain stable from January through March. Complete the sales budget by filling in the missing amounts. Determine the amount of sales revenue Solomon will report on its...
The company expects to replace the computer equipment at year 4. Determine how much the company...
The company expects to replace the computer equipment at year 4. Determine how much the company will have if they save $3,000 per month into their investment account which pays the interest rate of 3% per year, compounded semiannually? a) Assume that interperiod cash flows earn prorated compound interest. b) Assume there is no interperiod compounding.
A ) An entrepreneur invests $40,454.00 into a start-up business today. He expects the business will...
A ) An entrepreneur invests $40,454.00 into a start-up business today. He expects the business will generate $60,003.00 per year for 14.00 years, and then it will generate $137,194.00 per year for the following 16.00 years. Suppose he wants a 9.00% annual return to run the business. What is the value of this business today if his forecasts are accurate? (HINT: Discount all cash flows to today and subtract start-up investment.) B) A young graduate is planning on saving $600.00...
?Staton-Smith Software is a new? start-up company and will not pay dividends for the first five...
?Staton-Smith Software is a new? start-up company and will not pay dividends for the first five years of operation. It will then institute an annual cash dividend policy of ?$4.75 with a constant growth rate of 4?%, with the first dividend at the end of year six. The company will be in business for 25 years total. What is the? stock's price if an investor wants a. a return of 12?%? b. a return of 13?%? c. a return of...
Staton-Smith Software is a new​ start-up company and will not pay dividends for the first five...
Staton-Smith Software is a new​ start-up company and will not pay dividends for the first five years of operation. It will then institute an annual cash dividend policy of $3.75 with a constant growth rate of 6​%, with the first dividend at the end of year six. The company will be in business for 25 years total. What is the​ stock's price if an investor wants a.  a return of 10​%? b.  a return of 15​%? c.  a return of...
A company began operations at the start of 2015. During the year, it made cash sales...
A company began operations at the start of 2015. During the year, it made cash sales of $150,000 and credit sales totaling $500,000. $420,000 in cash from these credit sales was collected during the year. The company purchased land for $60,000 for a new location. Expenses totaled $339,000, of which $300,000 was paid in cash. Dividends of $10,000 were paid to stockholders.   What was net income for 2015? A. $311,000 B. $270,000 C. $301,000 D. $350,000 E. None of the...
Lanni Products is a start up computer software development firm. It currently owns computer equipment worth...
Lanni Products is a start up computer software development firm. It currently owns computer equipment worth $30,000 and has cash on hand of $20,000 contributed by Lanni’s owners. For each of the following transactions, identify the real and/or financial assets that trade hands. Are any financial assets created or destroyed in the transaction? a. Lanni takes out a bank loan. It receives $50,000 in cash and signs a note promising to pay back the loan over three years. b. Lanni...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT