To create the pro forma operating statement we used ratios of COGS/Sales and Operating Expense/Sales for years 2004, 2005, and 2006. We then took the average of these ratios and multiplied them by sales for 2007 to come up with 2007 COGS and Operating Expenses. To calculate interest expense we included the outstanding loan Jones has with Verden and the new line of credit he would be taking out with Southern Bank and Trust. We calculated Verden’s interest expense by multiplying the rate of 8% by the outstanding amount of $134, 000.
We also assumed Jones would use the maximum amount of $350, 000 on his line of credit to support his rapidly growing business. In addition to the $350, 000 limit, the bank also limits his ability to withdraw on his line to 75% of Accounts Receivable and 50% of Inventory. Based on 2007 projections of these balances, the latter restriction will not inhibit his ability to take advantage of the full $350, 000 line of credit. To calculate the income taxes we found the ratio of Provisions for Income Tax/Net Income before Taxes for the years 2004-2006 and took the average ratio. We used the average of 34% as the tax rate to find the Provisions for Income Tax in 2007.
Balance Sheet Assets: To calculate the Cash, Accounts Receivable, Inventory and Property & Equipment for 2007 on the pro forma Balance Sheet, we took the ratios from 2004-2006 relative to sales for their respective year. By averaging these ratios and multiplying by the expected 2007 sales, we were able to find the 2007 Cash, Accounts Receivable, Inventory and Property and Equipment. To calculate Accumulated Depreciation, we assumed that the increase in $8, 000 between the end of 2006 and the first quarter of 2007 would remain constant throughout the rest of the year resulting in a $32, 000 increase in Accumulated Depreciation in 2007.
Liabilities:
Our calculations for the liabilities and net worth can be broken down into the following explanations. To calculate Accounts Payable and Accrued Expenses we found the ratio of these compared to Sales for the years 2004-2006 and then took an average of all years. We took this average and multiplied it by the Sales of 2007 to find the balances of these accounts in 2007. To calculate the current line of credit payable we used goal seek to figure out how much money Jones would have to borrow in order to balance his total assets and liabilities. After conducting this analysis we found out that Jones will need a line of credit payable of $410, 540.
Therefore, Jones’ line of credit payable will be $350, 000 as that is the maximum amount Southern Bank & Trust will allow Jones to take out. Long Term Debt is $110, 000 at 2007 year end. Back in 2003, Jones bought out Verden from the company for $250, 000. Under their agreement, Jones would pay Verden the $250, 000 in installments of $2, 000 per month plus interest of 8% per year. In year 2006, the loan outstanding was $134, 000 so in 2007 he should pay another installment of $24, 000 making the year end balance $110, 000. Therefore, current long term debt is $24, 000. The Net Worth account is essentially retained earnings. To calculate this amount we took the Net Worth balance in 2006 of $243, 000 and added Net Income from 2007 of $36, 000 which is $279, 000 at year end of 2007. This is consistent with how Net Worth is calculated in the years from 2004-2006
Case Discussion Question No, the line of credit of $350, 000 will not meet Jones’ foreseeable requirements as he needs $410, 540 under the current $2. 7 million estimated sales. This is assuming that Jones Electrical Distribution has rapid sales growth and Jones takes advantage of the trade discounts, thus requiring very large financing. Without a larger loan Jones will be short on financing by $57, 540. Assuming that Jones doesn’t have the rapid sales growth as anticipated his financing requirements would change. If for example, there was no sales growth and remained at 2006 sales level of $2, 242, 000, then his financing requirements would be $245, 000 meaning he may not even need to open the new line of credit with the new bank.
