Question: What is the highest estimated end of month line of credit balance and the month?
Please show all work thank you.
Burtch Company, a retailer, buys merchandise from its suppliers to sell to its customers. Facing declining profitability, Burtch Company is planning an addition to its product line. The marketing manager, Marie Patel, is spearheading the new product's planning. Marie believes that the proposed new product will be a good fit with Burch Company's current product line. Working with John Forsyth, Burch Company - Manager of Analytics, Marie has developed forecasts of the proposed new product's monthly unit sales, selling price per unit, purchase price per unit, and fixed costs. Appendix A summarizes these forecasts. Sales are comprised of cash sales (10% of total sales) and credit sales (90% of total sales). John Bentley, the credit manager, believes that credit sales will be collected as follows: 50% in the month of sale, 30% in the month following the month of sale, 14% collected in the second month following the month of sale, and 6% never collected. Burch Company recognizes estimated bad debt expense at the time of sale. Monthly purchases are comprised of 80% for this month's sales and 20% for next month's sales. There will be no inventory on hand at the start of January. Therefore, 100% of January's sales will be purchased in January. In January Burch Company will also purchase 20% of February's sales. Payments to the merchandise supplier will be 60% in the month of purchase and 40% in the month following the month of purchase. Burtch Company uses the FIFO (first in first out) method of inventory valuation. Depreciation comprises 40% of the fixed facilities costs. All other fixed facilities costs and all the Selling General and Administration (SG&) fixed costs are cash expenses and are paid on June 30 for the entire year. Burtch Company has $1,000,000 to start this project. Burtch Company has access to a line of credit with the Eastern Bank. The line of credit has an annual interest rate of 12%. The Eastern Bank requires that Burtch Company maintain a minimum cash balance of $1,000,000. Interest on the outstanding line of credit balance is due and paid on the first day of each month. Appendix A October January 490,000 January 509,000 $152.00 63.90 February 497,000 $152.00 65.90 March 512,000 $152.00 66.00 April 496,000 $151.00 64.30 May 514,000 $152.00 64.30 June 511,000 $151.00 65.70 July 487,000 $149.00 66.40 August 511,000 $149.00 66.80 September 490,000 $153.00 65.20 503,000 $153.00 62.00 November 513,000 $153.00 66.00 December 499,000 $152.00 65.50 Unit Sales Price Purchase Cost Fixed Cost - Facilities - Selling, General, and Administrative $10,800,000 $11,800,000 16,000,000 16,900,000 $11,600,000 16,900,000 $11,300,000 $11,600,000 $11,400,000 $12,000,000 $10,800,000 16,100,000 17,000,000 16,200,000 16,700,000 16,700,000 $10,800,000 16,400,000 $11,700,000 $11,900,000 $10,600,000 16,800,000 16,200,000 16,900,000
