Cash Budget It is December 2017. You are a budget analyst for ACME Manufacturing
ID: 1174696 • Letter: C
Question
Cash Budget
It is December 2017. You are a budget analyst for ACME Manufacturing Inc. ACME makes a variety of garden supplies for home gardeners, tools, sprinklers, tomato frames, hoop greenhouse kits, etc. Create a cash budget for the months January through June, 2018, using the sales estimates and other information about the company given below. All numbers are in thousands. A form is provided for your cash budget.
A.Complete the cash budget for the first half of 2018 (January through June) on the form provided.
B.What will the Accounts Receivable balance be at the end of June, 2018?
C.Assume everything occurs exactly as the cash budget predicts, and ACME gets the loans forecast on the cash budget. Given the minimum cash balance of $50 is ACME assured that there will never be a cash shortfall at any time during the forecast period of January through June, 2018? Explain why or why not.
D.Why must this profitable company borrow money for a few months each year?
• The Company offers customers a 5% discount if they pay at the time of sale. About 20% of the customers take advantage of this discount. This means that for every $100 of merchandise sold ACME collects $19.00 in the sale month. This represents 20% of the sales being sold at a 5% discount or at 95% of full price ($100*0.20*0.95 = $19.00).
• Credit sales: 50% of each month’s sales are collected one month after the sale and 30% are collected two months after the sale.
• Raw materials are 60% of sales. Materials are ordered two months in advance and paid for the following month. So, materials for June are ordered in April and paid for in May.
• Manufacturing labor is 20% of sales and wages are paid for in the month of the sales. Thus, labor in January will be 20% of January sales (0.20*$300 = $60).
• Managerial salaries are $40 per month.
• Rent and lease payments total $20 per month.
• Tax payments of $35 will be made in March and May.
• The company will spend $150 for a new fabricating machine in February.
• The beginning cash balance in January is $120 and the minimum cash balance the company needs is $50. As of January 2018 there is no loan outstanding.
Please use the form for your cash budget
Month
Nov-17
Dec-17
Jan-18
Feb-18
Mar-18
Apr-18
May-18
Jun-18
Jul-18
Sales ($000s)
200
400
600
900
1,200
1,200
1,000
600
300
Cash receipts
30-Day
60-Day
Total Receipts
Materials
Labor
Salaries
40
40
Rent/Leases
20
20
Taxes
New machine
150
Total expenditures
Change in cash
Beginning cash
120
End cash wo/loan
Loan
End cash w/loan
Loan repayment
Cumulative loan
Cash surplus
Month
Nov-17
Dec-17
Jan-18
Feb-18
Mar-18
Apr-18
May-18
Jun-18
Jul-18
Sales ($000s)
200
400
600
900
1,200
1,200
1,000
600
300
Cash receipts
30-Day
60-Day
Total Receipts
Materials
Labor
Salaries
40
40
Rent/Leases
20
20
Taxes
New machine
150
Total expenditures
Change in cash
Beginning cash
120
End cash wo/loan
Loan
End cash w/loan
Loan repayment
Cumulative loan
Cash surplus
Explanation / Answer
Answer
Month Nov-17 Dec-17 Jan-18 Feb-18 Mar-18 Apr-18 May-18 Jun-18 Jul-18 Sales ($000s) 200 400 600 900 1,200 1,200 1,000 600 300 Cash receipts 38 76 114 171 228 228 190 114 57 30-Day 100 200 300 450 600 600 500 300 60-Day 60 120 180 270 360 360 300 Total Receipts 38 176 374 591 858 1098 1150 974 657 Materials 360 540 720 720 600 360 180 0 0 Labor 40 80 120 180 240 240 200 120 60 Salaries 40 40 40 40 40 40 40 40 40 Rent/Leases 20 20 20 20 20 20 20 20 20 Taxes 35 35 New machine 150 Total expenditures 460 680 900 1110 935 660 475 180 120 Change in cash -422 -504 -526 -519 -77 438 675 794 537 Beginning cash 120 50 50 50 50 111 905 End cash wo/loan -526 -519 -77 438 675 794 537 Loan 456 519 77 End cash w/loan 488 725 905 1442 Loan repayment 0 0 0 438 614 0 0 Cumulative loan 456 975 1052 614 0 0 0 Cash surplus 50 50 50 50 111 905 1442