Always create a new Excel file for your homework assignments unless the directions say otherwise. It is never okay to email or share your file with another student. It's okay to discuss homework but all students must work on and submit their own files. No group files Active Life Inc., a sports equipment retailer, needs to prepare a cash budget for the first quarter of 2023. The financial staff at Active Life has forecasted the following sales figures January $100,000 February March April May $150,000 $300,000 $250,000 $150,000 Actual sales in October, November, and December 2022 were $125,000, $145,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. The percent totals must add up 100%. To get this, subtract the cash sales from 100% Then use the remaining number and multiply the first, second, and third month to get the correct percent. For this credit policy, the first month percent would be 36% 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. For the payment calculation, you'll need an input cell for discount. For example, if purchases are 10,000, 40% will be paid in that month with a 2 percent discount. First calculate the amount to be paid (410000) 4,000 and then calculate the discount (4.000 02 80) so during month payment would be 3,920, 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 cash balance of $30,000 for December 2022 A. Using the above information, create a cash budget for January to June 2023. The cash budget should account for short-term borrowing and payback of outstanding loans. Use calculations so formulas will update B. Using Excel's outline feature, group the worksheet area at the top of the cash budget so that the preliminary calculations can be easily hidden or unhidden Check figures; March first month payments 54,000, Feb purchases: 135,000, March payments during month 44.100 January Ending Cash 32,820 March Cum Borrowing 62,785. Once these check figures match, name the cells with Total Interest Paid and Discount (you come up with appropriate names). Make a copy of this sheet Name one sheet Credit payments (C) and the other one Credit policies (D). Complete C on Credit payments sheet and D on Credit policies sheet. C. Elaine Benes, Active Life's CFO, is considering three credit payment proposals from the firm's supplier. In the first proposal the firm will pay 75% of its purchases in the same month and 25% in the following month, in the second proposal the firm will pay half in the same month and half in the following month; in the third proposal the firm will pay 25% of its purchases in the same month and 75% in the following month. Suppliers have offered 4%, 3%, and 2% discounts over the payments made during the same month of the purchase if the firm pays according to the first, second, and third proposals. respectively. The CFO has asked you to use the Scenario Manager to see which proposal has the lowest total interest cost (include the current policy as a scenario as well so you'll have 4 scenarios). Be sure to name the cells used as changing cells. Name the sheet Credit Payment Summary. Check figure Payment Policy 1 Total Interest 707 D. Benes is now considering three credit policies from the firm's customers. In the first policy the firm will sell 60% on cash and will collect 60% of the balance during the first month, and the remaining balance during the second month. In the second policy, 50% of sales will be on cash, and the firm will collect 50%, 30%, and 20% of credit sales during the first, second, and third months, respectively. The last policy consists of 40% sales on cash, and 40%, 30%, and 30% of the remaining balance will be collected during the first, second, and third months, respectively. The CFO has asked you to use the Scenario Manager to see what credit proposal has the lowest total interest cost (include the current policy as a scenario as well so you'll have 4 scenarios). Be sure to name the cells used as changing cells Check figure: Credit Policy 1 Total Interest 30