PLEASE SHOW ALL CALCULATIONS Jones Inc. operates a lawn care service where peak
ID: 2487646 • Letter: P
Question
PLEASE SHOW ALL CALCULATIONS
Jones Inc. operates a lawn care service where peak sales occur in the month of June. Data regarding the store's operations follow:
Sales are budgeted at $300,000 for April, 400,000 for May, and $650,000 for June and $525,000 in July
Collections are expected to be 30% in the month of sale, 65% in the month following the sale, 3% in the second month following sale and 2% uncollectible.
The cost of goods sold is 70% of sales.
The company desires ending merchandise inventory to equal 20% of the following month's cost of goods sold. Payment for merchandise is made 50% in the month of purchase and 50% in the month following the purchase.
Miscellaneous monthly expenses to be paid in cash are $12,000.
Monthly depreciation is $15,000.
Equipment purchases of $40,000 in April and $55,000 in May were paid in cash.
Dividends of $40,000 were declared and paid in June.
Any borrowings must be in $1,000 increments at 12% annual interest. Assume interest accrues at the beginning of the month and is paid at the end of the month. The company must maintain a minimum cash balance of $20,000.
Ignore income taxes.
All accounts receivable from March will be collected in April and all accounts payable in March will be paid in April.
The balance sheet as of March 31st:
Jones, Inc.
Balance Sheet
3/31/14
Assets:
Cash
$35,000
Net Accounts Receivable
$72,000
Merchandise Inventory
$41,000
Property Plant and Equipment
$1,400,000
Less: accumulated depreciation
$438,000
$962,000
Total Assets
$1,110,000
Liabilities & Stockholder's Equity
Accounts Payable
$200,000
Common Stock
$350,000
Retained Earnings
$560,000
Total liabilities and stockholder's equity
$1,110,000
Prepare the following budgets for each month April, May, June and Total for the quarter in good form in excel with proper use of formulas and formatting:
a. Prepare a Schedule of Expected Cash Collections
What is the budgeted accounts receivable at June 30th?
b. Prepare a Merchandise Purchases Budget and a Schedule of Expected Cash Disbursements
What is the budgeted accounts payable at June 30th?
c. Prepare a Cash Budget
How much does the company need to borrow for the quarter?
How much can the company repay for the quarter?
d. Prepare a Budgeted Income Statement
Jones, Inc.
Balance Sheet
3/31/14
Assets:
Cash
$35,000
Net Accounts Receivable
$72,000
Merchandise Inventory
$41,000
Property Plant and Equipment
$1,400,000
Less: accumulated depreciation
$438,000
$962,000
Total Assets
$1,110,000
Liabilities & Stockholder's Equity
Accounts Payable
$200,000
Common Stock
$350,000
Retained Earnings
$560,000
Total liabilities and stockholder's equity
$1,110,000
Explanation / Answer
a.
Schedule of expected cash collection:
April
May
June
July
Total (April + May + June)
Sales
300,000
400,000
650,000
525,000
1,350,000
At 30% in the month (A)
90,000
120,000
195,000
157,500
405,000
At 65% in the following month (B)
0
195,000
260,000
422,500
455,000
At 3% in the second month (C)
0
0
9,000
12,000
9,000
Collection of receivable (D)
72,000
0
0
0
72,000
Total (A + B + C + D)
$162,000
$315,000
$464,000
$941,000
Budgeted accounts receivable at June 30th is $464,000.
April
May
June
July
Total (April + May + June)
Sales
300,000
400,000
650,000
525,000
1,350,000
At 30% in the month (A)
90,000
120,000
195,000
157,500
405,000
At 65% in the following month (B)
0
195,000
260,000
422,500
455,000
At 3% in the second month (C)
0
0
9,000
12,000
9,000
Collection of receivable (D)
72,000
0
0
0
72,000
Total (A + B + C + D)
$162,000
$315,000
$464,000
$941,000
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.