attached accounting/finance forcasting assignment

Question

Now that we understand why Jones Electrical Distribution is challenged with its cash flow, we need to analyze whether the company can meet Mr. Jones’ ambitious growth target. Your assignment is to create a pro forma forecast for the full year 2007 and answer the questions in the Blackboard assignment. You will need to create all three financial statements and the sources and uses table to include the pro forma 2007. Please download the attached file to use as a template. All shaded cells must be filled in. Excel File: Assignment 2 file.xlsx  Your forecast should include the following assumptions: Sales is given to you in the case text. Jones decides to resume taking the early payment discounts and therefore makes his payments within 10 days of invoice. Hence his Days Payables Outstanding is 10 and his Cost of Goods Sold as a percent of sales should reflect the discount. Operating expenses should be forecasted as a percent of sales based on Q1 2007 data. Interest expense consists of interest on both the revolving credit line and the long-term debt. Assume that interest is calculated on the beginning balances of both. For this case, you can assume annual payments of interest. There is no change to the company’s tax rate as compared to prior years. There are no further capital expenditures in 2007. Use the DSO and DIO from Q1 2007 for your full year forecast of Accounts Receivable and Inventory, respectively. There are no further changes to Accrued Expenses after Q1 2007. For the purposes of calculating the revolving credit line assume that the company needs to maintain a cash balance of at least zero. You can build this model assuming Jones takes the early pay discounts, you do not have to worry about a scenario or “toggle switch” since it was not covered in class. However, you will need to modify the assumptions to look at the results if Jones did not take the discounts, in order to answer a couple of the questions.

Forecast