EXAM - PART 1 CONFIDENTIAL - DO NOT SHARE Pacific Light, Inc. Income Statement For the Period Ended Dec. 31, 2019 2020* 2019 Sales 6,148,000 Cost of Goods Sold 4,176,000 Gross Profit 1,972,000 Selling and G&A Expenses 588,000 Fixed Expenses 70,000 Depreciation Expense 478,000 EBIT 836,000 Interest Expense 186,000 Earnings Before Taxes 650,000 Taxes 195,000 Net Income 455,000 2018 5,134,000 3,422,000 1,712,000 590,000 70,000 446,000 606,000 182,000 424,000 127,200 296,800 * Forecast Notes Tax Rate 30% 30% SU22 Pacific Light, Inc. Balance Sheet As of Dec. 31, 2019 2020* Assets Cash and Equivalents Accounts Receivable Inventory Total Current Assets Plant & Equipment Accumulated Depreciation Net Fixed Assets Total Assets Liabilities and Owners' Equity Accounts Payable Short-term Notes Payable Accrued Expenses Total Current Liabilities Long-term Debt Total Liabilities Common Stock Retained Earnings Total Shareholder's Equity Total Liabilities and Owners' Equity INSTRUCTIONS Create two tabs in Excel. One for the IS and one for the BS. Recreate the above statements, using formulas and proper formatting techniques. Then proceed to develop a DFN model using the following information. The firm has forecasted sales of $6,250,000 and a tax rate of 40% for 2020. Cost of goods sold and S,G&A expense in 2020 are expected to be the average of their two-year proportion of sales. On the balance sheet, accounts receivable, inventory, accounts payable, and accrued expenses are expected to be at the two-year average of the proportion of these items in relation to sales. Cash stays the same. The firm has planned an investment of $700,000 in fixed assets in 2020, with an estimated life of 10 years and no salvage value. These fixed assets will be depreciated using the straight line depreciation method. All other financial statement items are expected to remain constant in 2020. Assume the firm pays 6% interest on short-term debt and 8% on long term debt. Assume that the dividends in 2020 will be the same as those paid in 2019. 1.What is the Discretionary Financing Needed (DFN) in 2020? Is this a surplus or deficit? 2.DFN will be absorbed by long-term debt. Make copies of both worksheets (IS and BS) and then set up an iterative worksheet to eliminate the DFN. What is the $ amount of Total Accumulated DFN? 3.Label the worksheets appropiately (you should have a total of 4 tabs) 4.SUBMTI YOUR WORKSHEET VIA CANVAS 2019 862,000 1,006,000 578,000 2,446,000 9,338,000 4,590,000 4,748,000 7,194,000 2018 678,000 730,000 600,000 2,008,000 8,644,000 4,112,000 4,532,000 6,540,000 764,000 158,000 318,000 1,240,000 2,046,000 3,286,000 1,638,000 2,270,000 3,908,000 7,194,000 540,000 198,000 228,000 966,000 1,934,000 2,900,000 1,616,000 2,024,000 3,640,000 6,540,000 FIN 335 – NPV Problem CONFIDENTIAL – DO NOT SHARE WITH ANYONE Note: use proper formatting as we learned and label your answers properly so I can spot them easily. Organize and format your worksheet the best you can so it is readable and pleasing to the eyes. Submit your worksheet via Canvas before the due time. Late submissions will NOT be accepted and earn zero points. Titan Communication, Inc. currently sells three models of smart phones to the US market. Management is planning to add a new smart phone model to their product line. The project will cost $2,700,000 in Buildings and Equipment, which will be depreciated using MACRS with a class life of 20 years (use the VDB function to compute depreciation). This project has a 5-year life. At the end of the 5 years, management anticipates that the buildings and equipment will be sold for $1,250,000. The marketing department estimates that the firm will be able to sell 12,100 units of the new smart phone at an average price of $195 per unit during the first year. Unit demand is expected to grow at a rate of 6% annually thereafter. Variable operating expenses are expected to average 60% of gross sales, and fixed costs (not including depreciation) will be $370,000 per year. The company's marginal tax rate is 22% and its WACC is 13%. A. Evaluate the project (find the NPV, IRR, MIRR, Payback Period and Discounted Payback Period). 1. Do you recommend the project to Management given your findings? Why? B. Create a Sensitivity Table (i.e. data table) for these variables (use the ranges of -30%, -20%, -10%, 0%, +10%, +20%, +30%). 1. 2. 3. 4. 5. Terminal Value of Buildings and Equipment First year phone sales (units) Price per unit Unit sales growth rate Variable costs as a % of sales C. Using the ABS / Slope / Rank functions, create a simple ranking table and highlight the two most sensitive variables (sensitive to the NPV amount). D. Using scenario manager, generate a summary for the following 3 scenarios against NPV. Variable First year unit sales Price per unit Variable cost % WORST case Probability 20% 8,100 190 62% BASE case Probability 60% 12,100 195 60% BEST case – Probability 20% 13,500 200 57% Hint: before using the scenario manager, you can make a copy of worksheet to avoid breaking formulas from parts A & B! E. Next compute the expected NPV, variance and standard deviation using the probabilities given above. 1. What is the probability that NPV ends up equal or below $0.00? 2. Based on the results of this analysis, how risky do you do think this project is? F. Create a “NPV Scenario Analysis” Histogram (based on NPV values and probabilities above).
Purchase answer to see full attachment