1. The financial staff at Exotic Foods Inc., a food importer, wholesaler, and di
ID: 2526126 • Letter: 1
Question
1. 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.
*Need to create an Excel Cash Budget
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
Explanation / Answer
EXOTIC FOODS INC. Cash budget for six months ending June 30, 2018 January February March April May June Total Beginning cash balance 20000 26150 28800 15500 15250 30433 20000 Collections from sales 112650 107350 130200 158550 161550 141450 811750 Sales tax (5% of sales) 5000 6000 7500 9000 7500 6000 41000 Cash available for disbursements 137650 139500 166500 183050 184300 177883 872750 Cash disbursements: For purchases 54000 66000 81000 84000 69000 66000 420000 For operating expenses 33000 39200 48500 57800 41000 33200 252700 For interest 4000 4000 4000 4000 4000 4000 24000 For maintenance 1500 1500 1500 1500 1500 1500 9000 For sales tax 19000 18500 37500 For capital expenditure 40000 40000 For dividends 20000 20000 40000 Total disbursements 111500 110700 155000 205800 115500 124700 823200 Cash surplus / (deficit) 26150 28800 11500 -22750 68800 53183 49550 Minimum cash balance 15000 15000 15000 15000 15000 15000 15000 Excess / (shortfall) 11150 13800 -3500 -37750 53800 38183 34550 Financing: Borrowing / repayment 4000 38000 -38000 4000 Interest -367 -367 Total financing 0 0 4000 38000 -38367 0 3633 Ending Cash balance 26150 28800 15500 15250 30433 53183 53183 Sales Budget January February March April May June Total Budgeted Sales 100000 120000 150000 180000 150000 120000 820000 Cash sales (45%) 45000 54000 67500 81000 67500 54000 369000 Credit Sales 55000 66000 82500 99000 82500 66000 451000 Collection budget January February March April May June Total Cash sales 45000 54000 67500 81000 67500 54000 369000 Credit Sales November sales 33000 33000 December sales 34650 14850 49500 January sales 38500 16500 55000 February sales 46200 19800 66000 March sales 57750 24750 82500 April sales 69300 29700 99000 May sales 57750 57750 June sales 0 Total collections 112650 107350 130200 158550 161550 141450 811750 Receivables 69850 82500 102300 123750 112200 90750 90750 Purchase budget January February March April May June Total Budgeted sales 100000 120000 150000 180000 150000 120000 820000 Purchases 60000 75000 90000 75000 60000 75000 435000 (50% of next month's sales) Payment for purchases: December purchases 30000 30000 January purchases 24000 36000 60000 February purchases 30000 45000 75000 March purchases 36000 54000 90000 April purchases 30000 45000 75000 May purchases 24000 36000 60000 June purchases 30000 30000 Total payments for purchases 54000 66000 81000 84000 69000 66000 420000 Payables 36000 45000 54000 45000 36000 45000 45000 Operating expenses budget January February March April May June Total Administrative wages (10% of sales) 10000 12000 15000 18000 15000 12000 82000 Sales commission (15% of sales) 15000 18000 22500 27000 22500 18000 123000 Bonus 5000 6000 7500 9000 27500 Other expenses 3000 3200 3500 3800 3500 3200 20200 Total operating expenses 33000 39200 48500 57800 41000 33200 252700
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.