LESSON 10AssignmentQuestion 1 (55 marks)Note:In Question 4 of Assignment 9, you completed an exercise very similar to the first part of this question with respect to preparing projections. This assignment builds upon those skills, requiring you to analyze and comment on the results.Computer questionIn early 2013, Jane Lender, an officer of the Commerce Bank, was evaluating the account of Johnson Lumber Co., which uses a line of credit to manage its seasonal requirements. This year the company needed more than its usual credit limit and, unlike previous years, the account was not paid off. Jane has been asked by her manager to study the account and prepare a report. Jane studied the file and found that the company was managed by professional managers with backgrounds in marketing and production. She also identified that the firm’s profits had been growing steadily, though not spectacularly considering the inflation rate. Jane is aware that her manager will require financial analysis to back up her report. Also, considering the executive attention span, she decided to keep her report brief. The Income Statement and Statement of Financial Position of Johnson Lumber Co. is given below.JOHNSON LUMBER CO.Income Statementfor years ended October 31, 2010-2012(thousands of dollars)201020112012Net sales € 9,000 €10,500 €13,500Cost of goods soldMaterial and labour 7,725 9,000 11,625Overhead 180 255 285 Earnings before interest and tax 1,095 1,245 1,590 Interest expenses 45 45 90 Earnings after taxes 1,050 1,200 1,500Taxes at 50% 525 600 750 Earnings after tax 525 600 750 Dividends 390 450 450 Retained earnings €135 €150 €300The net sales is arrived after a quantity discount of €90, €105, and €160 respectively i n 2010, 2011, and 2012. Overhead includes the following depreciation.Plant 120 135 165Equipment 52 60 75JOHNSON LUMBER CO.Statement of Financial Positionas at October 31, 2010-2012201020112012 ASSETSCash €271 €241 €121 Marketable securities 315 15 0 Accounts receivable 1,410 1,515 1,785 Inventories 1,515 1,725 1,965Total current assets 3,511 3,496 3,871 Loans to dealers 150 150 225Plant (net of depreciation) 1,650 1,800 2,700 Equipment (net of depreciation) 1,350 1,425 1,515 Total fixed assets 3,150 3,375 4,440Total assets € 6,661 € 6,871 €8,311 LIABILITIES AND EQUITYAccounts payable € 1,305 € 1,515 €1,680 Notes payables 150 0 0Bank loan 0 0 975Total current liabilities 1,455 1,515 2,655 Ordinary shares 1,206 1,206 1,206 Retained earnings 4,000 4,150 4,450Total Liabilities and equity € 6,661 € 6,871 €8,311 There are two sections to this question: Section 1 pertains to the financial analysis as specified in the appendix; Section 2 is an additional analysis specified in this assignment.RequiredSection 1 (5 marks)Prepare an analysis of the financial performance of the three years (2010-2012), and comment on Johnson’s current financial situation. Your analysis should be bas ed on the statement of cash flows (SCF) for the period from October 31, 2010 to October 31, 2012 and basic ratios for the three years of data, as supplied below. This section requires your skills with financial ratios and working capital management.JOHNSON LUMBER CO.Statement of cash flowstwo years ended October 31, 2012(000s)Net income from operations € 1,350 OPERATING ACTIVITIESAdjustments to convert to cash basisAccounts receivable increase (375)Inventories increase (450)Accounts payable increase 375€ (450) Depreciation 435€(15)€ 1,335 FINANCING ACTIVITIESNotes payable decrease € (150)Bank loan increase 975Dividends (900)€(75) INVESTING ACTIVITIESLoans to dealers increase €(75)Plant increase (1,350)Equipment increase (300)(1,725)Decrease in cash € (465)Cash and cash equivalents at October 31, 2010 586Cash and cash equivalents at October 31, 2012 €121RATIO ANALYSIS2010 2011 2012 Liquidity ratios:Current 2.41 2.31 1.46 Quick 1.37 1.17 0.72 Inventory turnover1 5.22 5.71 6.46 Accounts receivable turnover2 6.38 7.18 8.18 Profitability ratios:Times-interest-earned 24.33 27.67 17.67 Asset turnover3 1.35 1.53 1.62Net operating margin 0.12 0.12 0.12 Earnings power ratio 0.16 0.18 0.19 1The calculation for 2010 was based on the 2010 inventory number not the average inventory as the 0211 and 2012 were2The calculation for 2010 Accounts receivable turnover was based on the 2010 Accounts receivable number and not the average accounts receivable as the 2011 and 2012 were.3The calculation for asset turnover for 2010 was based on the Total assets for 2010 and not the average total assets as was done used for the asset turnover calculation for 2011and2012.Note:No worksheet has been provided for analyzing Section 1. You may wish to construct your own, or answer the question manually.Section 2 (50 marks total)Use a worksheet to develop pro forma financial statements for the next five years (2013-2017) for Johnson. Then perform what-if analyses with the pro forma financial statements. Modify the LXQ1 worksheet in the file I-FN1LXQ1 to complete this section.Base plan(14 marks)The following additional information is required to complete this section, using the worksheet LXQ1. (All dollar amounts are expressed in €000s.)1. The corporate tax rate is 40% effective November 1, 2006. Previously it was 50%.2. Depreciation is calculated by applying the depreciation rate on non-current assets. The averagedepreciation rate is 5%. In the year of acquisition, depreciation is allowed only for a half year, for new capital expenditures (row 34). [Note that these instructions differ from those given in Question 4 of Assignment 9.]3. Cost of goods sold is 83% of sales for the same year.4. Salaries and administration costs are 8% of sales for the same year.5. Inventory is maintained at 15% of next year’s sales.6. Accounts payable is 15% of cost of goods sold for the same year.7. Accounts receivable is 15% of sales for the same year.8. Johnson’s target level of cash and marketable securities is 2% of sales for the same year.9. Johnson projects its sales to grow at 20% per year over the preceding year for both 2013 and2014, and slow to 5% per year for 2015 to 2017.10. Interest rates are forecasted at 9% for 2013, 10% for 2014, and 12% thereafter.11. Johnson plans to incur no long-term debt for the five years starting 2013.12. No new equity issues have been planned for the five years starting 2013.13. New capital expenditures for the five years from 2013 to 2017 will be €800, €1,400, €1,000,€1,000, and €1,000 respectively.14. Dividends will be maint ained at €300 for 2013 to 2017.After entering the preceding information in the worksheet, analyze the pro forma financial information. Comment on Johnson’s financial plan, and discuss some of the financial ratios and key financial information.Procedure1. Open the file I-FN1LXQ1. The file has only one worksheet, LXQ1.2. Before making any changes to this worksheet, print out a copy (including column and rowheaders) of the worksheet and analyze it. You may find the printed copy useful in working through various parts of the question.3. Examine the layout of the worksheet. It resembles the worksheet used in Computer illustration10-1 of the Lesson Notes and Question 4 of Assignment 9. The current data pertaining to 2012 has been pre-entered in cells B13 to B20. Most parts of the pro forma financial statements and ratio analysis have also been pre-entered.4. Enter appropriate information or formulas in the following cells:B8 Corporate tax rateB9 Depreciation rateG10 to G20 The various percentagesB26 to G26 Sales growth multiple for calculating projected salesB27 to G27 Projected salesB28 to G28 Interest ratesC32 to G32 Long-term debtC33 to G33 New equityC34 to G34 New capital expendituresC35 to G35 DividendsB41 to G41 Target cash and marketable securitiesB43 to G43 Accounts receivableB48 to G48 Accounts payableC60 to G60 Depreciation5. After completing the worksheet, save a copy. This copy will be used as a basis for each of thescenarios in the next section.6. Copy and paste cells A5 to G66 into your Word document. Then copy and paste cells A69 toG76 into your Word document.7. Display the formulas in your completed worksheet. Copy and paste the formulas in cells A38to D76 to your Word document.What-if analysis(36 marks, 6 marks each)After reviewing the five-year financial forecast, Ms. Lender of the Commercial Bank makes several suggestions for Johnson.Use your completed worksheet to analyze the following independent scenarios suggested by Ms. Lender. Assume long-term debt is incurred at the beginning of a year.Note:6 marks for each part’s analysis and conclusions.a.Cut growth. By reducing the firm’s optimal capital budget, Johnson can reduce new capitalexpenditures to €200 for all years, which in turn will change the firm’s sales. Instead of the original growth rate, 2013 net sales will increase 10% over 2012, and then grow at an annual rate of only 2% thereafter.b. Finance growth through long-term debt. Instead of cutting growth, Johnson can try tofinance growth by incurring long-term debt, with year-end balances of €2,500, €2,500, €3,500, €4,000, €4,000 for 2013 to 2017 respectively.c. Finance growth through new equity financing. Rather than financing growth using long-term debt, Johnson can issue new equity of €3,500 (net proceeds) in 2013.d. Finance growth using a mix of long-term debt and new equity. Johnson can try to financegrowth by issuing new equity of €1,750 in 2013, and long-term debt with year-end balances of €1,500, €1,800, €2,500, and €3,000 in th e years 2014 to 2017 respectively.e. Finance growth by improving cash receipts. Instead of issuing new equity and/or new long-term debt, Johnson can try to finance growth by cutting the percentage of credit sales so that accounts receivable is only at 5% of its total sales for each year, and reduce inventory to 5% of the next year’s sale by reducing dealer inventories.f. Ms. Lender points out that if Johnson is successful in financing the desired growth,shareholders may want a higher level of dividends. Therefore, she suggests an analysis of the impact of a higher level of dividends on its financial plan: using the financial growth plan specified in (d), increase the total dividend payout to €450 for 2015 and beyond.Procedure1. For each of the six scenarios suggested by Ms. Lender, retrieve the copy of the worksheetsaved in step 5 of the previous section. (Base plan)2. Enter appropriate information for each scenario. Modify cell A6 to indicate “SCENARIO A,”“SCENARIO B,” and so on and cell A69 to indicate “RATIO ANALYSIS: SCENARIO A”and so on.3. Use the worksheet to provide the appropriate analysis and conclusions.4. Submit the Ratio Analysis for each scenario by pasting cells A69 to G76 into your Worddocument.Question 2 (20 marks)Multiple choice (2 marks each)Statement of cash flowsUse the information that follows for questions a – g.a.Referring to the company data above, what is the firm’s operating margin?1) 3.00%2) 4.10%3) 4.90%4) 15.31%b. Referring to the company data above, what is the market-to-book ratio of the firm’s equity?1) 0.472) 1.003) 1.164) 1.50c.Referring to the company data above, what is the firm's price-earnings ratio?1) 2.32) 3.03) 5.04) 15.0d.Referring to the company data above, what is the firm's return on common equity?1) 11%2) 22%3) 30%4) 43%e.Referring to the company data above, what is the firm's total debt-to-assets ratio?1) 0.692) 0.723) 0.764) 1.31f.Referring to the company data above, what is the times-interest-earned ratio?1) 1.002) 2.733) 3.734) 4.45g.Referring to the following company data above, what is the firm's debt-to-equity ratio?1) 1.542) 1.773) 2.204) 3.14h.Acquirer Inc. a firm specialized in buying other firms, announced that it was going to acquireSpecialize Cars Ltd., a carmaker. What type of merger would this be?1) Cross-border merger2) Conglomerate merger3) Horizontal merger4) Vertical mergeri.Which of the following firms would be a good target for a leveraged buyout (LBO)?1) A firm that uses well-known, mature production technology2) A firm that is highly levered and has difficulty meeting loan payments3) A firm that is in a new and specialized business4) A firm whose primary assets are trademarks and patentsj.External growth can occur by which of the following means?1) Increasing research and development into new products2) Purchasing another firm's debt3) Purchasing either another firm's assets or shares4) Expanding a production line by purchasing new production equipment Question 3 (5 marks)Pal-Lee Foods (PLF) is a processor of baking ingredients such as dried fruits, chocolate chips, and nuts. Wow Foods Inc. (WFI) has diversified production facilities for making a wide variety of food products that use PLF’s products. Following discussions with its investment bankers, WFI's management is considering a merger of equals to take advantage of some strategic synergies between the two companies.a.What type of merger would this be? (2 marks)b.What reasons might management of PLF and WFI have for wanting to merge their businessoperations? (3 marks)Question 4 (5 marks)One of the features that make a company a good target for a leveraged buyout is a diversified product line. Explain why this feature is important.Question 5 (15 marks)Answer the following independent questions.a.Financial ratios use the relative value of various items on the financial statements to assess afirm’s financial position. Typically, these financial ratios are divided into five types. One of these types is valuation (market-value) ratios. Identify the remaining four types of ratios and, for each, briefly describe the information it conveys. (4 marks)b.The senior management of Pyramid Inc. is currently developing its marketing strategy for thenext two years. A potential input into this process is confidential information on themarketing plans of its nearest competitor, which has been provided to Pyramid by a new staff member who recently resigned from a position with the competitor. Briefly explain why it is important for the senior management of Pyramid to consider how they have acquired this information as well as the content of the information itself in a situation such as this one.(4 marks)c.External expansion of a business can occur in several different ways. Briefly explain thedifference between an acquisition and a statutory amalgamation (merger). (3 marks)d.Briefly describe what a leveraged buyout (LBO) (also known as a management buyout) isand how an LBO is typically undertaken. (4 marks)。