You have just been contracted as a budget consultant by LBJ Company, a distribut
ID: 2459315 • Letter: Y
Question
You have just been contracted as a budget consultant by LBJ Company, a distributor of bracelets to various retail outlets across the country. The company has done very little in the way of budgeting and at certain times of the year has experienced a shortage of cash. You have decided to prepare a cash budget for the upcoming fourth quarter in order to show management the benefits that can be gained from proper cash planning. You have worked with accounting and other areas to gather the information assembled below. The company sells many styles of bracelets, but all are sold for the same $10 price. Actual sales of bracelets for the last three months and budgeted sales for the next six months follow:
months follow:
July (actual)
20,000
August (actual)
26,000
September (actual)
40,000
October (budget)
70,000
November (budget)
110,000
December (budget)
60,000
January (budget)
30,000
February (budget)
28,000
March (budget)
25,000
The concentration of sales in the fourth quarter is due to the Christmas holiday. Sufficient inventory should be on hand at the end of each month to supply 40% of the bracelets sold in the following month.
Suppliers are paid $4 for each bracelet. Fifty-percent of a month's purchases is paid for in the month of purchase; the other 50% is paid for in the following month. All sales are on credit with no discounts. The company has found, however, that only 20% of a month's sales are collected in the month of sale. An additional 70% is collected in the following month, and the remaining 10% is collected in the second month following sale. Bad debts have been negligible.
Monthly operating expenses for the company are given below:
Variable expenses:
Sales commissions 4% of sales
Fixed expenses:
Advertising $220,000
Rent $20,000
Salaries $110,000
Utilities $10,000
Insurance $5,000
Depreciation $18,000
Insurance is paid on an annual basis, in January of each year.
The company plans to purchase $22,000 in new equipment during October and $50,000 in new equipment during November; both purchases will be for cash. The company declares dividends of $20,000 each quarter, payable in the first month of the following quarter.
Other relevant data is given below:
Cash balance as of September 30 $74,000
Inventory balance as of September 30 $112,000
Merchandise purchases for September $200,000
The company maintains a minimum cash balance of at least $50,000 at the end of each month. All borrowing is done at the beginning of a month; any repayments are made at the end of a month.
The company has an agreement with a bank that allows the company to borrow the exact amount needed at the beginning of each month. The interest rate on these loans is 1% per month and for simplicity we will assume that interest is not compounded. At the end of the quarter, the company will pay the bank all of the accrued interest on the loan and as much of the loan as possible while still retaining at least $50,000 in cash.
Required:
Prepare a cash budget for the three-month period ending December 31. Include the following detailed budgets:
1.
a. A sales budget, by month and in total.
b. A schedule of expected cash collections from sales, by month and in total.
c. A merchandise purchases budget in units and in dollars. Show the budget by month and in total.
d. A schedule of expected cash disbursements for merchandise purchases, by month and in total.
2. A cash budget. Show the budget by month and in total. Determine any borrowing that would be needed to maintain the minimum cash balance of $50,000.
PROJECT 1 - Excel Template
Student Name:
SALES BUDGET:
October November December Quarter
Budgeted unit sales
Selling price per unit
Total sales
SCHEDULE OF EXPECTED CASH COLLECTIONS:
October November December Quarter
August sales
September sales
October sales
November sales
December sales
Total cash collections
MERCHANDISE PURCHASES BUDGET:
October November December Quarter
Budgeted unit sales
Add desired ending inventory
Total needs
Less beginning inventory
Required purchases
Cost of purchases @ $4 per unit
BUDGETED CASH DISBURSEMENTS FOR MERCHANDISE PURCHASES:
October November December Quarter
September purchases
October purchases
November purchases
December purchases
Total cash payments
LBJ COMPANY
CASH BUDGET
FOR THE 3 MONTHS ENDING DECEMBER 31
October November December Quarter
Cash balance
Add collections from customers
Total cash available
Less disbursements
Merchandise purchases
Advertising
Rent
Salaries
Commissions
Utilities
Equipment purchases
Dividends paid
Total disbursements
Excess (deficiency) of receipts
over disbursements
Financing:
Borrowings
Repayments
Interest
Total financing
Cash balance, ending
July (actual)
20,000
August (actual)
26,000
September (actual)
40,000
October (budget)
70,000
November (budget)
110,000
December (budget)
60,000
January (budget)
30,000
February (budget)
28,000
March (budget)
25,000
Explanation / Answer
Oct Nov Dec Total Sept Aug Sales in units A 70000 110000 60000 240000 40000 26000 Sale Price 10 10 10 10 10 10 Sales in value 700000 1100000 600000 2400000 400000 260000 SCHEDULE OF EXPECTED CASH COLLECTIONS: Oct Nov Dec Total August sales 26000 26000 September sales 280000 40000 320000 280000 40000 October sales 140000 490000 70000 700000 November sales 220000 770000 990000 December sales 120000 120000 Total cash collections 446000 750000 960000 2156000 Cash Disbursement Oct Nov Dec Total Jan Finished Goods Sales 70000 110000 60000 240000 30000 Closing Inventory 40% of next month sales S*.4 44000 24000 12000 12000 Total Finised Googd 114000 134000 72000 252000 Less: Beginning Inventory 28000 44000 24000 28000 Units to be produced 86000 90000 48000 224000 Purchase Price 4 4 4 4 Total Purchase price A 344000 360000 192000 896000 Cash Disbursement Oct Nov Dec Total September 100000 0 0 100000 Oct 172000 172000 344000 Nov 180000 180000 360000 Dec 0 0 96000 96000 Total B 272000 352000 276000 900000 Cash Expenses LBJ COMPANY CASH BUDGET FOR THE 3 MONTHS ENDING DECEMBER 31 Oct Nov Dec Totalk Cash balance 74000 50000 50000 74000 Add collections from customers 446000 736000 960000 2142000 Total cash available 520000 786000 1010000 2216000 Less disbursements Merchandise purchases 272000 352000 276000 900000 Advertising 220000 220000 220000 660000 Rent 20000 20000 20000 60000 Salaries 110000 110000 110000 330000 Commissions 4% sales 28000 44000 24000 96000 Utilities 10000 10000 10000 30000 Equipment purchases 22000 50000 72000 Dividends paid 20000 20000 Total disbursements 702000 806000 660000 2168000 Excess (deficiency) of receipts -182000 -20000 350000 48000 over disbursements Less: Minimum Cash Balance 50000 50000 50000 50000 Financing: Borrowings 232000 70000 302000 Repayments -291640 -291640 Interest -8360 -8360 Total financing 232000 70000 -300000 2000 Cash balance, ending 50000 50000 50000 50000
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.