Question

Pro forma balance sheet Peabody & Peabody has 2015 sales of $10.3 million. It wishes to analyze expected performance and financing needs for 2017—2 years ahead. Given the following information, respond to parts a. and b. (1) The percents of sales for items that vary directly with sales are as follows: Accounts receivable; 12.2%, Inventory: 17.7%, Accounts payable, 13.6%; Net profit margin, 3.4%. (2) Marketable securities and other current liabilities are expected to remain unchanged. (3) A minimum cash balance of $476,000 is desired. (4) A new machine costing $652,000 will be acquired in 2016, and equipment costing $847,000 will be purchased in 2017. Total depreciation in 2016 is forecast as $289,000, and in 2017 $393,000 of depreciation will be taken. (5) Accruals are expected to rise to $504,000 by the end of 2017 (6) No sale or retirement of long-term debt is expected. (7) No sale or repurchase of common stock is expected. (8) The dividend payout of 50% of net profits is expected to continue (9) Sales are expected to be $11.5 million in 2016 and $11.4 million in 2017 (10) The December 31, 2015, balance sheet is here a. Prepare a pro forma balance sheet dated December 31, 2017 b. Discuss the financing changes suggested by the statement prepared in part (a). a. Prepare a pro forma balance sheet dated December 31, 2017 Complete the assets part of the pro forma balance sheet for Peabody & Peabody for December 31, 2017 below: (Round to the nearest dollar.) Pro Forma Balance Sheet Peabody & Peabody December 31, 2017 A Data Table Assets Current assets (Click on the icon located on the top-right corner of the data table below in order to copy its contents into a spreadsheet.) Cash 69 Marketable securities 69 Accounts receivable 69 69 Inventories Total current assets Net fixed assets Leonard Industries Balance Sheet December 31, 2015 Assets Liabilities and Stockholders' Equity Cash $400,000 Accounts payable Marketable securities 198,000 Accruals Accounts receivable 1,196,000 Other current liabilities Inventories 1,804,000 Total current liabilities Total current assets $3,598,000 Long-term debt Net fixed assets 3,999,000 Common stock Total liabilities and Total assets $7.597,000 stockholders' equity $1,405,000 397,000 80,300 $1,882,300 1,998,700 3,716,000 69 69 Total assets 69 $7,597,000 Print Done

Answer 1

The details required in above question are confusing:

Part a) requires pro forma balance sheet or just the assets part of pro forma balance sheet for December 2017 ?

However, presenting complete balance sheet for December 2017 for your perusal:

Part a)

2015 10300000 2016 11500000 2017 11400000 Analysis of sales and net profit 2015 Sales 10300000 Net Profit Dividend payout Retained earnings 2016 11500000 =D5*3.4% =D6*50% =D6-07 2017 11400000 =E5*3.4% =E6*50% =E6-E7 2016 2016 Amount Amount =15*13.6% 2015 Assets Amount Cash 400000 Marketable securities 198000 Accounts receivable 1196000 Inventories 1804000 Total Current assets =SUM(C16:C19) Net fixed assets 3999000 Pro forma Balance sheet Peabody & Peabody December 31, 2017 Value 2017 Amount Liabilities 476000 Accounts payable 198000 Accruals 1390800 Other current liabilities 2017800 Total current liabilities 4082600 Long term debt 4816000 Common stock Retained earnings 8898600 Total liabilities & stockholders' equity Formula 2017 Amount 476000 198000 =E5 12.2% =E5 17.7% =SUM(E16:E19) =D21+D28-D29 198000 =D5*12.2% =D5*17.7% 2015 Amount 1405000 397000 80300 =SUM(H16:H18) 1998700 3716000 80300 Formula 2017 Amount =35*13.6% 504000 80300 =SUM(J16:J18) 1998700 =3716000 =+D8+E8 =SUM(J19:J22) Value 2017 Amount 1550400 504000 80300 2134700 1998700 4105300 389300 8238700 1998700 3716000 =C21+C27-C29 Total Assets =SUM(C20:c22) =SUM(D20:D22) =SUM(E20:E22) =SUM(H19:H22) 2017 2016 652000 Year Machine acquired Equipment Depreciation 847000 393000 289000

Part b): Financing changes suggested:

As per the pro forma balance sheet, the cash balance cannot be as desired. If the company desires to have a higher cash balance of $476,000, the company needs to ensure quick recovery of receivables. The company has a huge amount blocked in receivables and inventory. It should look to implement just in time method of procuring inventory of raw materials so as to reduce cash blocked in inventory. If inventory pertains to finished goods ready for sale, the management should implement better sales practices to improve cash cycle and thereby improve liquidity.

As mentioned in the question, the company has no change in long term debt since 2 years, but it has a debt equity ratio of approximately 2.22:5 (44%) hence it is not recommended to further take any long term debts.