Active Life Inc., a sports equipment retailer, needs to prepare a cash budget for the first quarter of $2021.$ The financial staff at Active Life has forecasted the following sales figures:

January February March April May

$$100,000,$$$150,000,$$$300,000,$$$50,000,$$$150,000$

Actual sales in October, November, and December $2020$ 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 cash balance of $$30,000$ for December $2020.$

a$)$ Using the above information, create a cash budget for January to June $2021.$ 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 easily hidden or unhidden $($Analysis$,$ Identify and Access Needed Information and use information effectively to accomplish a specific purpose $12pts).$

b$)$ Ms$.$ Elaine Benes, Active Life's CFO, is considering three credit 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 $($Identify and Acoess Needed Information $4$ pts$).$

Optional: The CFO has asked you to use the Scenario Manager to see which proposal has the lowest total interest cost.

c$)$ Ms$.$ 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. $($Identify and Access Needed Information $4pt.$

Optional: The CFO has asked you to use the Scenario Manager to see what credit proposal has the lowest total interest cost.

d$)$ Interpretation: please describe where you obtained information to develop analyses and interpret the results. Please include your written description in the "interpretation" tab. If you do not complete this section with your words or leave the interpretation tab as a blank, you receive $0$ point in this HW$.$

PLEASE PROVIDE FORMULAS.