please use the excel attached to solve for the surplus cash or total loans

collected in the month of the sale, net sales in the month after the sale, and late sales too months after the sale. Alpine Inc. begins production of goods two months before the anticipated sale date. Variable production costs are made up entirely of purchased materials and labor, which total 60 percent of forecasted sales 20% for materials and 40% for labor. All materials are purchased just before production begins, or two months before the sale of the finished goods. On average, Alpine pays 50% of the materials cost in the month when it receives the materials, and the remaining 50% the next month, or one month prior to the sale. Labor expenses follow a similar pattern, but only 30% is paid two months prior to the sale, while 70% is paid one month before the sale. Alpine Inc. pays fixed general and administrative expenses of approximately $900,000a month, while lease obligations amount to $360,000 per month. Both expenditures are expected to continue at the same level throughout the forecast period. The firm estimates miscellaneous expenses to be $300,000 monthly and fixed assets are currently being depreciated by $480,000 per month. The firm has $12,000,000 (book value) of bonds outstanding that carry a 10% semiannual coupon and interest is paid on January 15 and July 15 . Also, the company is planning to replace an old machine equipment in June with a new one costing $4,000,000. The old equipment has both a zero book and a zero-market value. Federal and state income taxes are expected to be $900,000 quarterly and payments must be made on the 15th of December, March, June, and September. Aipine has a policy of maintaining a minimum cash balance of $3,000,000 and this amount will be on hand on January 1,2024. Assume that you were recently hired as an assistant treasurer to Bill Austin and both you and Bill met with Ann to review the budget prior to Ann's meeting with the firm's bank on Monday. A monthly cash budget must be prepared for the period of January through June 2024. Based on the information obtained from the firm's credit department, Bill suggests that the following assumptions be used to prepare the budget. Initially disregard both interest payments on short term bank loans and the interest received from investing surplus funds. Also, assume that all cash flows occur on the 15th of each month. Finally note that collections from sales of November and December of 2023 will not be completed until January and February of 2024 respectively. Further, Bill is extremely concerned about the maximum funds shortfall during the 6 month planning period because of liquidity implications and the resulting financial distress that may occur. He is hoping that a $400.000 line of eredit (as a liquidity facility) may be sufficient to cover any expected cash shortfall. He wants to know how the cash budget would be affected if Alpine's cash flows start to cluster at the beginning of the month while collections become heaviest toward the end of the month. 1. ALPINE Incorporated Bill Austin, the treasurer of Alpine Inc., met with the president and chicf executive officer, Ann Durango where she informed Bill that because of a recent tightening of credit by the Federal Reserve, hence an expected contraction of bank loans, the firm's bank has asked each of its major loan customers for an estimate of their borrowing requirements for the remainder of 2023 and the first half of 2024. Ann had a previously scheduled meeting with the firm's banker the following Monday, so she asked Bill to come up with an estimate of the firm's probable financing requirements for submission to the bank. Due to Alpine's rapid growth over the last few years, no one had taken the time to prepare a cash budget recently. From information already available, Bill knew no loans will be needed from the bank before January 2024 , so he decided to restrict his budget to the period from January through June. As a first step, Bill obtained the following sales forecast from the marketing department. (Note that the sales figures are before any discounts; that is, they are not net of discounts) Alpine Inc.'s credit policy is 1.5/10 net 30 . Hence, a 1.5% discount is allowed if payment is made within 10 days of the sale; otherwise, payment in full is due 30 days after the date of sale. Based on previous study, Bill estimates that, generally, 25% of the firm's customers take the discount, 65% pay within 30 days and 10% pay late, with late payments averaging about 60 days after the invoice date. So, the average accounts receivables ageing (cycle) is 28 days (=0,25(10) +0.65(30)+0.10(60) ). For monthly budgeting purposes, discount sales are assumed to be