Prepare and submit an estimated balance sheet and income statement as of June 30, 2000, for Crown Electrical Supply based on the information from the discussion material of lesson 5 of this course manual. Keep the bank loan at $75,000 (remember this is a "what if" analysis) and use cash as the plug figure. After the liabilities/net worth and all the assets except for cash have been estimated, subtract the total of all the assets other than cash from total liabilities and net worth to obtain the estimated cash balance.
Using the same approach-that of preparing a pro forma balance sheet as of a specific point in time to anticipate future conditions-prepare and submit a pro forma balance sheet as of the end of January, 2000. However, this time let the cash figure be $30,000 as the minimum cash figure and use accounts payable as the plug figure. Then answer the question: How much must accounts payable be in order for Crown Electrical Supply to maintain the minimum of $30,000 cash in the bank? What would this mean in terms of how many days' purchases would remain unpaid (accounts payable in days' purchases)? Assume purchases in December 1999 were $136,500.
HERE IS WHAT YOU WILL NEED FROM LESSON 5
For part III of the instructor-graded assignment, use a sales estimate for the first six months of 2000 of $210,000 per month for the first three months of the six-month period, approximately the same rate of sales per month as currently being experienced. For the second three months of the six month period use a sales estimate of $225,000 per month, an increase of 7% over the present monthly sales rate. This would be a total of $1,305,000 for the entire six month period. Cost of goods sold were estimated to be 65 percent of sales.
In order see the effect of a reduction in inventories, use a purchases figure that is $36,000 less than the cost of goods sold during the period. Each month, purchases will be $6,000 less than the cost of goods sold. Merchandise will be purchased on open account, and all accounts payable will be paid within 30 days of the purchase. All sales are made on credit. It is estimated that at the end of each month, two-thirds of the month's sales will remain uncollected (the collection period will be approximately twenty days). Accordingly, accounts receivable collections each month will consist of the uncollected accounts receivable as of the beginning of the month (the end of the previous month) plus one-third of the sales during that month.
Cash operating expenses, not including depreciation, were expected to be 20.2 percent of sales, and selling and administrative expenses were expected to be 8.2 percent of sales. These expenses would be paid as incurred. Quarterly tax payments of $12,500 would be paid on March 31 and on June 30 (two payments of $12,500 each). Crown anticipated paying $18,750 in dividends to common shareholders on April 30, 2000.
Examination of Crown's financial statements in lesson 3 shows that Crown did not have enough cash to make the first $25,000 payment on the bank loan when it became due at the end of January 2000. Crown's current bank balance of $32,900 was only $2,900 higher than the $30,000 figure it considered to be the minimum cash balance needed to operate the company. Obviously it was time for Crown to do some cash planning.
We saw in lesson 3 that Crown invested into additional inventory and accounts receivable all of its profits, all of the $75,000 it had borrowed from the bank, and a great deal of additional trade credit (in the form of accounts payable). One would expect to have to increase accounts receivable and inventory when sales increase substantially. The normal increases alone would require additional funds, but Crown's inventory increased by more than its sales. Consequently, it required even more funds to finance this inventory increase than would otherwise be necessary. In doing part I of the instructor-graded assignment to be submitted for this lesson we see that this "additional" increase in inventory required substantially more cash than it would have had inventory merely increased by the same proportion as sales.
The solution is in an excel sheet that shows the calculations and preparation of budgets, income statement, and balance sheet for Crown Electrical Supply