During the last year the Gulf Oil Equipment Company expanded its product line fo
ID: 2443755 • Letter: D
Question
During the last year the Gulf Oil Equipment Company expanded its product line for the industry. The company began offering a specialized line of pipe used in drilling operations. The pipe is a dramatic improvement for the industry since it yields a 50% savings in weight without having a loss of strength. The company sells the pipe in 16’ lengths for $250 per length. Actual unit sales for the last three months and budgeted unit sales for the next six months follow:Regular Regular
October (actual) 1000 March (budgeted) 1000
November (actual) 1200 April (budgeted) 1450
December (actual) 1100 May (budgeted) 1650
January (budgeted) 1200 June (budgeted) 1400
February (budgeted) 1500
Sufficient inventory should be on hand to supply 40% of the next month’s sales at the end of each month.
The supplier is paid $160 per length of pipe. One half of a month’s purchases is paid in the month of purchase; the other half is paid for inn the following month. All sales are on credit, with no discount, and payable within 15 days. 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 are negligible.
Monthly operating expenses for the company are given below:
Variable:
Sales Commissions 20% of sales
Fixed:
Advertising $8,000
Rent 7,000
Salaries 20,000
Utilities 9,000
Depreciation 5,000
Insurance expired 1,000
Insurance is paid on an annual basis, in November of each year. The company declares dividends of $15,000 each quarter, payable in the first month of the following quarter. Part of the budgeting program will be to establish an ongoing line of credit at a local bank. Therefore, determine the borrowing that will be needed to maintain a minimum cash balance of $50,000. All borrowing will be done at the beginning of the month; any repayments will be made at the end of the month. The annual interest rate is 12%.
Interest is computed and paid at the end of the quarter on all loans outstanding for the quarter.
Company ledger accounts for the pipe division as of December 31 are given below:
ASSETS
Cash 72,000
Accounts Receivable 250,000
Inventory 76,800
Prepaid Insurance 10,000
Property and equipment 1,000,000
Total assets 1,408,800
LIABILITIES AND S. EQUITY
Accounts Payable 91,200
Dividends Payable 15,000
Capital Stock 1,000,000
Retained Earnings 302,600
Total Liabilities and Stockholders Equity 1,408,800
Using EXCEL templates provided prepare a master budget including:
1. Sales budget, by month and in total
2. Schedule for cash collections from sales, by month and total
3. A merchandise purchases budget in units and in dollars, by month and total
4. Schedule of cash disbursements
5. A cash budget
6. Budgeted income statement for three months
7. Budgeted balance sheet
I need help with the schedule of cash disbursements all the way through the budgeted balance sheet. Any help in that regard would be greatly appreciated. Thank you. The assignment is due no later than 9 am Thursday April 14 2011
Explanation / Answer
(a) Sales budget:
April
May
June
Quarter
Budgeted unit sales
65,000
100,000
50,000
215,000
Selling price per unit
× $10
× $10
× $10
× $10
Total sales
$650,000
$1,000,000
$500,000
$2,150,000
(b) Schedule of expected cash collections:
April
May
June
Quarter
February sales
$26,000
$ 26,000
March sales
$280,000
$40,000
$320,000
April sales
$130,000
$455,000
$65,000
$650,000
May sales
$200,000
$700,000
$900,000
June sales
100,000
100,000
Total cash collections
$436,000
$695,000
$865,000
$1,996,000
(c) Merchandise purchases budget:
April
May
June
Quarter
Budgeted unit sales
$65,000
$100,000
$50,000
$215,000
Add: desired ending inventory
40,000
20,000
12,000
12,000
Total needs
$105,000
$120,000
$62,000
$227,000
Less: beginning inventory
26,000
40,000
20,000
26,000
Required purchases
79,000
80,000
42,000
201,000
Cost of purchases at $4 per unit
$316,000
$320,000
$168,000
$ 804,000
(d) Budgeted cash disbursements for merchandise purchases:
April
May
June
Quarter
Accounts payable
$100,000
$ 100,000
April purchases
158,000
$158,000
316,000
May purchases
160,000
$160,000
320,000
June purchases
84,000
84,000
Total cash payments
$258,000
$318,000
$244,000
$ 820,000
(2)
Earrings Unlimited
Cash Budget
For the Three Months Ending June 30th
April
May
June
Quarter
Cash balance
$74,000
$50,000
$50,000
$ 74,000
Add collections from customers
436,000
695,000
865,000
1,996,000
Total cash available
510,000
745,000
915,000
2,070,000
Less disbursements:
Merchandise purchases
$258,000
$318,000
$244,000
$820,000
Advertising
$200,000
$200,000
$200,000
$600,000
Rent
$18,000
$18,000
$18,000
$54,000
Salaries
$106,000
$106,000
$106,000
$318,000
Commissions
$26,000
$40,000
$20,000
$86,000
Utilities
$7,000
$7,000
$7,000
$21,000
Equipment purchases
$0
$16,000
$40,000
$56,000
Dividends paid
$15,000
$0
$0
15,000
Total disbursements
$630,000
$705,000
$635,000
$1,970,000
Excess (deficiency) of receipts over disbursements
-$120,000
$40,000
$280,000
$100,000
Financing:
Borrowings
$170,000
$10,000
$0
$180,000
Repayments
$0
$0
-$180,000
-$180,000
Interest
$0
$0
-$5,300
-$5,300
Total financing
$170,000
$10,000
-$185,300
-$5,300
Cash balance, ending
$50,000
$50,000
$94,700
$ 94,700
3.
Earrings Unlimited
Budgeted Income Statement
For the Three Months Ending June 30th
Sales
$2,150,000
Variable expenses:
Cost of goods sold
$860,000
Commissions
$86,000
$946,000
Contribution margin
$1,204,000
Fixed expenses:
Advertising
$600,000
Rent
$54,000
Salaries
$318,000
Utilities
$21,000
Insurance
$9,000
Depreciation
$42,000
$1,044,000
Net operating income
$160,000
Interest expense
$5,300
Net income
$154,700
4.
Earrings Unlimited
Budgeted Balance Sheet
For the Three Months Ending June 30th
Assets
Cash
$ 94,700
Accounts receivable
$500,000
Inventory
$48,000
Prepaid insurance
$12,000
Property and equipment, net
$964,000
Total assets
$1,618,700
Liabilities and Stockholders’ Equity
Accounts payable, purchases
$84,000
Dividends payable
$15,000
Capital stock
$800,000
Retained earnings
$719,700
Total liabilities and stockholders’ equity
$1,618,700
April
May
June
Quarter
Budgeted unit sales
65,000
100,000
50,000
215,000
Selling price per unit
× $10
× $10
× $10
× $10
Total sales
$650,000
$1,000,000
$500,000
$2,150,000
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.