PREPARATION OF CASH BUDGET

** Use Excel to answer this problem, show formulas, and complete all parts of this question!! **

4imprint Group plc is a London-based direct marketer of promotional merchandise. It has offices in the United States, United Kingdom, Hong Kong and Europe. 4imprint USA has projected its sales for the first 8 months of 2022 as follows

January - February - March - April - May - June - July - August 210,000 - 175,000 - 130,000 - 130,000 - 150,000 - 250,000 - 200,000 - 180,000

4imprint collects 15 percent of its sales in the month of the sale, 60 percent in the month following the sale, and the remaining 25 percent in the 2 months following the sale. During January and February 2022, 4imprints sales were $210,000 and $175,000 respectively.

4imprint purchases all its raw materials 2 months in advance of its sales These purchases are equal to 68 percent of its final sales. The supplier is paid 1 month after delivery. Thus, for example, purchases for April sales are made in February and payment is made in March.

In addition, 4imprint pays $12,000 per month for rent and $18,000 each month for other miscellaneous expenditures. Tax prepayments of $22,500 are made each quarter beginning in May (i.e. next payment will be in August). The companys cash balance as of February 2022 was $28,000, and a minimum balance of $20,000 must be maintained at all times to satisfy the firms bank line of credit agreement. 4imprint has arranged with its bank for a short-term credit at an interest rate of 12 percent per annum (i.e. 1 percent per month) to be paid monthly. Borrowing to meet estimated monthly cash needs takes place at the end of the month, and interest is not paid until the end of the following month. Consequently, if the firm needed to borrow $50,000 during April, then it would pay $500 (1% of $50,000) in interest during May. Finally, 4imprint follows a policy of repaying its outstanding short-term debt an any month in which its cash balance exceeds the minimum desire balance of $20,000.

Questions

a) 4imprint needs to know its cash requirements will be for the next 6 months so that it can negotiate the terms of its short-term credit agreements with its bank, if necessary. To evaluate this problem, the firm first needs to evaluate the impact of a 20 percent variation in its monthly sales effort. Prepare a 6-month cash budget for 4imprint and use it to evaluate the firms cash needs.

b) 4imprint has $20,000 note due in June. Will the firm have sufficient cash to repay the loan?

PREPARATION OF CASH BUDGET 4imprint Group plc is a London-based direct marketer of promotional merchandise. It has offices in the United States, United Kingdom, Hong Kong and Europe. 4imprint USA has projected its sales for the first 8 months of 2022 as follows 4imprint January February March April May June July August 210,000 175,000 130,000 130,000 150,000 250,000 200,000 180,000 4imprint collects 15 percent of its sales in the month of the sale, 60 percent in the month following the sale, and the remaining 25 percent in the 2 months following the sale. During January and February 2022, 4imprint's sales were $210,000 and $175,000 respectively. 4imprint purchases all its raw materials 2 months in advance of its sales These purchases are equal to 68 percent of its final sales. The supplier is paid 1 month after delivery. Thus, for example, purchases for April sales are made in February and payment is made in March. In addition, 4imprint pays $12,000 per month for rent and $18,000 each month for other miscellaneous expenditures. Tax prepayments of $22,500 are made each quarter beginning in May (i.e. next payment will be in August). The company's cash balance as of February 2022 was $28,000, and a minimum balance of $20,000 must be maintained at all times to satisfy the firm's bank line of credit agreement. 4imprint has arranged with its bank for a short-term credit at an interest rate of 12 percent per annum (i.e. 1 percent per month) to be paid monthly. Borrowing to meet estimated monthly cash needs takes place at the end of the month, and interest is not paid until the end of the following month. Consequently, if the firm needed to borrow $50,000 during April, then it would pay $500 (1% of $50,000) in interest during May. Finally, 4imprint follows a policy of repaying its outstanding short-term debt an any month in which its cash balance exceeds the minimum desire balance of $20,000. Questions a) 4imprint needs to know its cash requirements will be for the next 6 months so that it can negotiate the terms of its short-term credit agreements with its bank, if necessary. To evaluate this problem, the firm first needs to evaluate the impact of a + 20 percent variation in its monthly sales effort. Prepare a 6-month cash budget for 4imprint and use it to evaluate the firm's cash needs. b) 4imprint has $20,000 note due in June. Will the firm have sufficient cash to repay the loan