50.1K

Verified Solution

Link Copied!

Please provide formulas and solve for the yellow parts and if possible please provide an excel worksheet once finished

Active Life Inc., a sports equipment retailer, needs to prepare a cash budget for the first quarter of 2022. The financial staff at Active Life has forecasted the following sales figures: Actual sales in October, November, and December 2021 were $125,000,$146,000, and $125,000, respectively. Cash sales are 40% of the total, and the rest are on credit. Under the current credit policy the firm expects to collect 60% of credit sales the following month, 30% two months after, and the remainder in the third month after the sale. Each month, the firm makes inventory purchases equal to 45% of the of the next month's sales. The firm pays for 40% of its inventory purchases in the same month and 60% in the following month; nevertheless, the firm enjoys a 2% discount if it pays during the same month as the purchase. Estimated disbursements include monthly wages and other expenses representing 25% of the same month's sales; a major capital outlay of $30,000 expected in January; a dividend payment of \$25,000 in February; \$40,000 of long-term debt maturing in March; and a tax payment of $60,000 in April. The interest rate on its short-term borrowing is 7%. It has a required minimum cash balance of $10,000 every month, and has an ending eash balance of $30,000 for December 2021. a) Using the above information, create a cash budget for January to June 2022. The cash budget should account for short-term borrowing and payback of outstanding loans. Using Excel's outline feature, group the worksheet area at the top of the cash budget so that the preliminary calculations can be casily hidden or unhidden (Analysis, Identify and Access Needed Information and use information effectively to accomplish a specific purpose 12pts )

Answer & Explanation Solved by verified expert

See Answer