The financial staff at Exotic Foods Inc., a food importer, wholesaler, and distributor, has estimated the following sales figures for the first half of 2018:
Month Sales Other Expenses
January $100,000 $3,000
February $120,000 $3,200
March $150,000 $3,500
April $180,000 $3,800
May $150,000 $3,500
June $120,000 $3,200
July $150,000 $3,500
August $180,000 $3,800
Actual November and December 2017 sales were $200,000 and $90,000, respectively. Cash sales are 45% of the total and the rest are on credit. About 70% of credit sales are typically collected one month after the sale and 30% the second month. Monthly inventory purchases represent 50% of the following month’s sales. The firm pays 40% of its inventory purchases in cash and the remainder in the following month. Administrative wages are expected to be 10% of the month’s sales. Commissions to sales associates are estimated to be 15% of collectable sales, but the firm has decided to include a bonus of 5% more if the sales of the current month are higher than the previous one. A major capital expenditure of $40,000 is expected in April and a quarterly dividend of $20,000 will be paid to shareholders in March and June. Monthly long-term debt interest expenses and maintenance expenses are $4,000 and $1,500, respectively. Sales taxes are 5% of quarterly sales and must be paid in January, April, and July, starting in January with $19,000. The firm has an ending cash balance of $20,000 for December 2017.
a) The firm wants to maintain a minimum cash balance of at least $15,000 with a maximum of $30,000, and will pay interest on its short-term borrowings of 5%. If the firm can earn an average rate of return of 3% on short-term investments, help the financial staff of Exotic Foods Inc. to prepare a cash budget that shows interest payments on borrowed/invested funds. Note that the firm must pay off any short-term loan outstanding before any cash surplus can be invested, and invested funds should be used instead of borrowing when needed.