You have been hired as a new management trainees by Earrings Unlimited, a distributor of earrings to various retail outlets located in shopping malls across the country. In the past, the company has done very little in the way of budgeting and at certain times of the year has experience a shortage of cash.

Since you are well trained in budgeting, you have decided to prepare comprehensive budgets for the upcoming second quarter in order to show management the benefits that can be gained from an integrated budgeting program. To this end, you have worked with accounting and other areas to gather the information assembled below.

The company sells many styles of earrings, but all are sold for the same price-$10.00 per pair. Actual sales of earrings for the last three months and budgeted sales for the next six month follow (in pairs of earrings):

January (actual) 20,000 February (actual) 26,000
March (actual) 40,000 April (budget) 65,000
May (budget) 100,000 June (budget) 50,000
July (budget) 30,000 August (budget) 28,000
September (budget) 25,000

The concentration of sales before and during May is due to Mother’s Day. Sufficient inventory should be on hand at the end of each month to supply 40% of the earrings sold in the following month. Suppliers are paid $4.00 for a pair of earrings. One-half of a month’s purchases in paid for in the month of the purchase; the other half is paid for in the following month. All sales are on credit, with no discount, and payable within 15 days. The company has found, however, that only 20 percent of a month’s sales are collected in the month of sale. An additional 70 percent is collected in the following month, and the remaining 10 percent in the second month following sale. Bad debts have been negligible.














Monthly operation expenses for the company are given below:

Variable:
Sales Commission 4% of sales
Fixed:
Advertising 200,000
Rent 18,000
Salaries 106,000
Utilities 7,000
Insurance 3,000
Depreciation 14,000

Insurance is paid on an annual basis, in November of each year. The company plans to purchase 16,000 in new equipment during May and $40,000 in new equipment during June; both purchases will be for cash. The company declares dividends of $15,000 each quarter payable in the first month of the following quarter.

A listing of the company’s ledger accounts as of March 3 is given below:

Assets

Cash 74,000
Accounts receivalbe ($26,000 February sales;$320,00 March sale 346,000
Inventory 104,000
Prepaid Insurance 21,000
Property and equipment (net) 950,000

Total Assets 1,495,000

Liabilities and Stockholders Equity

Accounts payable 100,000
Dividends payable 15,000
capital stock 800,000
retained earnings 580,000

Total liabilities and Stockholders Equity 1,495,000

The company maintains a minimum cash balance of 50,000. All borrowing is done at the beginning of a month, and repayments are made at the end of a month. The annual interest rate is 12 percent. Interest is computed and paid at the end of each quarter on all loans outstanding during the quarter.


Required;

Prepare a master budget for the three-month period ending June 30. 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 purchaser 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 the month and in total. Determine any borrowing that would be needed to maintain the minimum cash balance of $50,000

3. A budgeted income statement for the three months period ending June 30. Use the contribution approach.

4. A budgeted balance sheet as of June 30.

I am stuck here and can't figure out how to come to the answers for this. Please provide some help asap. Found out about this assignment late and am behind the gun. Have about 24 hours to complete. Any help would be appreciated.

Please read this announcement.

Announcement:

If you'll just start by doing the #1 requirements, you can build from there. I think maybe you just need a little confidence. This is a comprehensive assignment.

Start with 1a. You'll have a fairly simple schedule by month of units and dollars. Remember they are only looking for the 3 months of the second quarter.

Part b will use this info: The company has found, however, that only 20 percent of a month's sales are collected in the month of sale. An additional 70 percent is collected in the following month, and the remaining 10 percent in the second month following sale.

Part c and d - read the exercise as to inventory needs and how suppliers are paid.

Do what you can and post it. We'll check it to see that you are on the right track.

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


SCHEDULE OF EXPECTED CASH COLLECTIONS:
April May June Quarter
February sales 26000 2,600 2,600
March sales 40000 28,000 400,000 428,000
April sales 65000 13,000 45,500 6,500 65,000
May sales 100000 20,000 70,000 90,000
June sales 50000 10,000 10,000
Total Cash Collections 43,600 465,500 86,500 595,600


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 16,000 40,000 20,000 16,000
Required purchases 89,000 80,000 42,000 211,000
Cost of purchases @ $4 per unit 356,000 320,000 168,000 844,000


BUDGETED CASH DISBURSEMENTS FOR MERCHANDISE PURCHASES:
April May June Quarter
Accounts payable
April purchases
May purchases
June purchases
Total cash payments


EARRINGS UNLIMITED
CASH BUDGET
FOR THE THREE MONTHS ENDING JUNE 30
April May June Quarter
Cash balance 50,000 50,000 50,000 50,000
Add collections from customers 43,600 465,500 86,500 595,600
Total cash available 93,600 515,500 136,500 745,600

Less Disbursements
Merchandise purchases
Advertising 200,000 200,000 200,000 200,000
Rent 18,000 18,000 18,000 18,000
Salaries 106,000 106,000 106,000 106,000
Commissions 2,600 4,000 2,000 8,600
Utilities 7,000 7,000 7,000 7,000
Equipment purchases 16,000 40,000
Dividends paid 15,000
Total Disbursements 348,600 351,000 373,000 339,600

Excess (deficiency) of receipts
over disbursements
Financing:
Borrowings
Repayments
Interest
Total financing

Cash balance, ending


EARRINGS UNLIMITED
BUDGETED INCOME STATEMENT
FOR THE THREE MONTHS ENDED JUNE 30

Sales -
Variable expenses:
Cost of goods sold -
Commissions - -
Contribution Margin -
Fixed expenses:
Advertising -
Rent -
Salaries -
Utilities -
Insurance -
Depreciation - -
Net operating income -
Interest expense -
Net income -


EARRINGS UNLIMITED
BUDGETED BALANCE SHEET
JUNE 30

Assets:

Cash
Accounts receivable (see below)
Inventory
Prepaid insurance
Property and equipment, net
Total assets


Liabilities and Stockholders' Equity

Accounts payable, purchases
Dividends payable
Capital stock
Retained earnings (see below)
Total liabilities and stockholders' equity


Accounts receivable at June 30:
May sales x ?%
June sales x ?%
Total


Retained earnings at June 30:
Balance, March 31
Add net income
Total
Less dividends declared
Balance, June 30

Sales Budget is right.

Collections
The way I'd show the cash collections budget is:
April collections-
.2*650,000 = 130,000
.7*400,000 = 280,000 (from March sales)
.1*260,000 = 26,000 (from Feb sales)
Total budgeted cash collections for April = 436,000

I'll let you recompute May and June.

Purchases
You are on the right track. The ending inventory at March 31 is $104,000 (65,000 units budgeted for April x $4 per unit x .4) For April, you need to buy the remaining 60% and 40% of the needs for May. It looks like you just have the "less beginning inventory" number wrong. I didn't check May and June. Once you understand April, you can check and see if they are right.


Budgeted cash disbursements
Are you having problems with this one? There are no answers. It is similar to the cash collections computations except "One-half of a month's purchases in paid for in the month of the purchase; the other half is paid for in the following month."

I stopped here. If you can make your corrections and then see if you need any changes beyond that, I'll check back later to look at it. Thanks.

Ok Just Looking I have that under control for purchases. Yes I am having trouble with the budgeted Cash dibursements and the cash budget for 3 months. I have filled in what I could please help me in figuring out what is wrong and what I need to do. Thanks. Reposting my solutions thus far in a new thread question

You have been hired as new management trainees by Earrings Unlimited, a distributor of earrings to various retail outlets located in shopping malls across the country. In the past, the company has done very little in the way of budgeting and at certain times of the year has experience a shortage of cash.

Since you are well trained in budgeting, you have decided to prepare comprehensive budgets for the upcoming second quarter in order to show management the benefits that can be gained from an integrated budgeting program. To this end, you have worked with accounting and other areas to gather the information assembled below.

The company sells many styles of earrings, but all are sold for the same price-$10.00 per pair. Actual sales of earrings for the last three months and budgeted sales for the next six month follow (in pairs of earrings):

January (actual) 20,000 February (actual) 26,000
March (actual) 40,000 April (budget) 65,000
May (budget) 100,000 June (budget) 50,000
July (budget) 30,000 August (budget) 28,000
September (budget) 25,000

The concentration of sales before and during May is due to Mothers Day. Sufficient inventory should be on hand at the end of each month to supply 40% of the earrings sold in the following month. Suppliers are paid $4.00 for a pair of earrings. One-half of a month's purchases in paid for in the month of the purchase; the other half is paid for in the following month. All sales are on credit, with no discount, and payable within 15 days. The company has found, however, that only 20 percent of a month's sales are collected in the month of sale. An additional 70 percent is collected in the following month, and the remaining 10 percent in the second month following sale. Bad debts have been negligible.

Monthly operation expenses for the company are given below:

Variable:
Sales Commission 4% of sales
Fixed:
Advertising 200,000
Rent 18,000
Salaries 106,000
Utilities 7,000
Insurance 3,000
Depreciation 14,000

Insurance is paid on an annual basis, in November of each year. The company plans to purchase 16,000 in new equipment during May and $40,000 in new equipment during June; both purchases will be for cash. The company declares dividends of $15,000 each quarter payable in the first month of the following quarter.

A listing of the company's ledger accounts as of March 3 is given below:

Assets
Cash 74,000
Accounts receivable ($26,000 February sales;$320,00 March sale 346,000
Inventory 104,000
Prepaid Insurance 21,000
Property and equipment (net) 950,000

Total Assets 1,495,000

Liabilities and Stockholders Equity
Accounts payable 100,000
Dividends payable 15,000
capital stock 800,000
retained earnings 580,000

Total liabilities and Stockholder's Equity 1,495,000

The company maintains a minimum cash balance of 50,000. All borrowing is done at the beginning of a month, and repayments are made at the end of a month. The annual interest rate is 12 percent. Interest is computed and paid at the end of each quarter on all loans outstanding during the quarter.


Required;

Prepare a master budget for the three-month period ending June 30. 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 purchaser 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 the month and in total. Determine any borrowing that would be needed to maintain the minimum cash balance of $50,000

3. A budgeted income statement for the three months period ending June 30. Use the contribution approach.

4. A budgeted balance sheet as of June 30.
Student Name:


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


SCHEDULE OF EXPECTED CASH COLLECTIONS:
April May June Quarter
February sales 26,000 26,000
March sales 40000 280,000 40,000 320,000
April sales 65000 130,000 455,000 65,000 650,000
May sales 100000 200,000 700,000 900,000
June sales 50000 100,000 100,000
Total Cash Collections 436,000 695,000 865,000 1,996,000


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 16,000 40,000 20,000 16,000
Required purchases 89,000 80,000 42,000 211,000
Cost of purchases @ $4 per unit $ 356,000.00 $ 320,000.00 $ 168,000.00 $ 844,000.00


BUDGETED CASH DISBURSEMENTS FOR MERCHANDISE PURCHASES:
April May June Quarter
Accounts payable 80,000 80,000
April purchases 178,000 178,000 356,000
May purchases 160,000 160,000 320,000
June purchases 84,000 84,000
Total cash payments 258,000 338,000 244,000 436,000


EARRINGS UNLIMITED
CASH BUDGET
FOR THE THREE MONTHS ENDING JUNE 30
April May June Quarter
Cash balance 50,000 50,000 50,000 50,000
Add collections from customers 43,600 465,500 86,500 595,600
Total cash available 93,600 515,500 136,500 745,600

Less Disbursements
Merchandise purchases 258,000
Advertising 200,000 200,000 200,000 200,000
Rent 18,000 18,000 18,000 18,000
Salaries 106,000 106,000 106,000 106,000
Commissions 2,600 4,000 2,000 8,600
Utilities 7,000 7,000 7,000 7,000
Equipment purchases 16,000 40,000
Dividends paid 15,000
Total Disbursements 348,600 351,000 373,000 339,600

Excess (deficiency) of receipts
over disbursements
Financing:
Borrowings
Repayments
Interest
Total financing

Cash balance, ending


EARRINGS UNLIMITED
BUDGETED INCOME STATEMENT
FOR THE THREE MONTHS ENDED JUNE 30

Sales -
Variable expenses:
Cost of goods sold -
Commissions - -
Contribution Margin -
Fixed expenses:
Advertising -
Rent -
Salaries -
Utilities -
Insurance -
Depreciation - -
Net operating income -
Interest expense -
Net income -


EARRINGS UNLIMITED
BUDGETED BALANCE SHEET
JUNE 30

Assets:

Cash
Accounts receivable (see below)
Inventory
Prepaid insurance
Property and equipment, net
Total assets


Liabilities and Stockholders' Equity

Accounts payable, purchases
Dividends payable
Capital stock
Retained earnings (see below)
Total liabilities and stockholders' equity


Accounts receivable at June 30:
May sales x ?%
June sales x ?%
Total


Retained earnings at June 30:
Balance, March 31
Add net income
Total
Less dividends declared
Balance, June 30

Okay, I'm back now. Give me some time to look at what you did and respond. Just so you know, the moderators prefer that you keep all responses in one thread. Thanks.

Okay, parts 1a and 1b are correct.

I don't see where you corrected 1c. For April you should have:
Budgeted unit sales... 65,000
Desired ending inventory... 40,000
Needs... 105,000
Less beginning inventory... 26,000
Units to purchased... 79,000
Price per unit... $4
Costs of 79,000 units... $316,000

I did it the easy way. :)

We know that 40% of April's needs are in inventory. Needed:
60% * 65,000*$4... 156,000
40% * 100,000 * $4... 160,000
Purchases required for April $316,000

Your numbers for May and June are good.

Let's start there. I'll look at the cash disbursements next, but I need a little bit of time to do something else. Be back soon.

I think you are making the cash disbursements for purchases more complicated than it is. The problem states that 50% are paid the month following purchase and 50% the month of purchase.

If we look at April, we have the following:

50% of March purchases... $100,000
50% of April purchases... $158,000

Total... $258,000

You'll need to also look at May and June.

Moving on to Part 2, you'll start with the beginning cash number per your question, add budgeted receipts, subtract budgeted purchases, adjust by the operation expenses and the plans the company has for other purchases and dividends.

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


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


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 16,000 40,000 20,000 16,000
Required purchases 89,000 80,000 42,000 211,000
Cost of purchases @ $4 per unit $356,000.00 $320,000.00 $168,000.00 $844,000.00


BUDGETED CASH DISBURSEMENTS FOR MERCHANDISE PURCHASES:
April May June Quarter
Accounts payable $91,200.00 $91,200.00
April purchases $178,000.00 $178,000.00 $356,000.00
May purchases $160,000.00 $160,000.00 $320,000.00
June purchases $84,000.00 $84,000.00
Total cash payments $269,200.00 $338,000.00 $244,000.00 $447,200.00


EARRINGS UNLIMITED
CASH BUDGET
FOR THE THREE MONTHS ENDING JUNE 30
April May June Quarter
Cash balance 74,000 50,000 50,000 50,000
Add collections from customers 436,000 465,500 86,500 595,600
Total cash available 510,000 515,500 136,500 1,162,000

Less Disbursements
Merchandise purchases 269,200
Advertising 200,000 200,000 200,000 200,000
Rent 18,000 18,000 18,000 18,000
Salaries 106,000 106,000 106,000 106,000
Commissions 2,600 4,000 2,000 8,600
Utilities 7,000 7,000 7,000 7,000
Equipment purchases 16,000 40,000
Dividends paid 15,000
Total Disbursements 348,600 351,000 373,000 339,600

Excess (deficiency) of receipts
Over disbursements
Financing:
Borrowings
Repayments
Interest
Total financing

Cash balance, ending

Quote:

---

Originally Posted by Will2412

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 16,000 40,000 20,000 16,000
Required purchases 89,000 80,000 42,000 211,000
Cost of purchases @ $4 per unit $356,000.00 $320,000.00 $168,000.00 $844,000.00

---

You still aren't correcting the $356,000. Are you confused about that? Look at your beginning inventory. The inventory amount is $104,000. If you divide that by $4, you get 26,000. You are still using 16,000. I don't know why.

Quote:

---

Originally Posted by Will2412

BUDGETED CASH DISBURSEMENTS FOR MERCHANDISE PURCHASES:
April May June Quarter
Accounts payable $91,200.00 $91,200.00
April purchases $178,000.00 $178,000.00 $356,000.00
May purchases $160,000.00 $160,000.00 $320,000.00
June purchases $84,000.00 $84,000.00
Total cash payments $269,200.00 $338,000.00 $244,000.00 $447,200.00

---

Where is the $91,200 coming from? The problem shows $100,000. You are using $356,000 - that needs to be corrected. That's causing a problem for April and May. June is correct.

Ok. I have that down. My next problem is obtaining the correct info to create the cash budget. I know that my beginning cash is 74,000 and my collections are 436,000 for a sum of 510,000. If I am correct I must first also complete all of Aprils calculations in order to get beginning cash for May, is this correct? I am unsure of what data to use if you can please guide me through I would appreciate it as well as how much consideration do I give to the desired cash at beginning of month. Here is what I have so far.
Thank you

Cash balance 74,000 50,000 50,000 50,000
Add collections from customers 436,000 465,500 86,500 595,600
Total cash available 510,000 515,500 136,500 1,162,000

Less Disbursements
Merchandise purchases 210,000
Advertising 200,000 200,000 200,000 200,000
Rent 18,000 18,000 18,000 18,000
Salaries 106,000 106,000 106,000 106,000
Commissions 2,600 4,000 2,000 8,600
Utilities 7,000 7,000 7,000 7,000
Equipment purchases 16,000 40,000
Dividends paid 15,000
Total Disbursements 348,600 351,000 373,000 339,600

Excess (deficiency) of receipts
Over disbursements
Financing:
Borrowings
Repayments
Interest
Total financing

Cash balance, ending

Quote:

---

Originally Posted by Will2412

EARRINGS UNLIMITED
CASH BUDGET
FOR THE THREE MONTHS ENDING JUNE 30
April May June Quarter
Cash balance 74,000 50,000 50,000 50,000
Add collections from customers 436,000 465,500 86,500 595,600
Total cash available 510,000 515,500 136,500 1,162,000

Less Disbursements
Merchandise purchases 269,200
Advertising 200,000 200,000 200,000 200,000
Rent 18,000 18,000 18,000 18,000
Salaries 106,000 106,000 106,000 106,000
Commissions 2,600 4,000 2,000 8,600
Utilities 7,000 7,000 7,000 7,000
Equipment purchases 16,000 40,000
Dividends paid 15,000
Total Disbursements 348,600 351,000 373,000 339,600

Excess (deficiency) of receipts
over disbursements
Financing:
Borrowings
Repayments
Interest
Total financing

Cash balance, ending

---

I'm only going to comment on April since that could change May and June. When you make the changes and think it's final I'll look at May and June. Cash available is correct. I still don't agree with the Purchases number of $269,200. Double check your commissions number. It looks like you took 4% of units, not sales dollars. Good job on not falling for the Insurance and Depn amounts. Once you adjust these numbers, you can look at April cash balance vs the required $50,000 and see if you need to take a loan draw.

We really need to stick to one thread. I responded on the other thread.

Hi sorry to keep beating this dead horse, lol. But yes I am confused about the use of the 356,000 figure. I have found the mistake I made on the beginning inventory and found it @ 104,000. No are you telling me to get the cost of purchases I need to divide that number by 4 to get a value of 26000 for cost of purchases. By just entering that alone without the division I end up with only 1000.
Next as for the 91200 figure I don't know where that figure is. It may have been a type error from when I sent my first worksheet. Here is how it looks now, minus the adjustment for the 356000 figure until you clarify that for me. Thx

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 16,000 40,000 20,000 16,000
Required purchases 89,000 80,000 42,000 211,000
Cost of purchases @ $4 per unit $356,000.00 $320,000.00 $168,000.00 $844,000.00


BUDGETED CASH DISBURSEMENTS FOR MERCHANDISE PURCHASES:
April May June Quarter
Accounts payable $80,000.00 $80,000.00
April purchases $130,000.00 $130,000.00 $260,000.00
May purchases $200,000.00 $200,000.00 $400,000.00
June purchases $100,000.00 $100,000.00
Total cash payments $210,000.00 $330,000.00 $300,000.00 $340,000.00

I apologize for jumping back in the different thread I accidentally had posted twice and keep clicking into that one, I will stay on here from now on

{threads merged} when posting a follow-up question or info, please use the Answer options at the bottom of the page rather than the Comments.

Thank you, I was sure confused.

It's okay. The moderators combined your threads so it wouldn't be so confusing. Now, just don't start another thread. ;)



About the $356,000, these are the correct numbers. I just took your calcs and corrected for the number of units of inventory. I feel like you understand it as you got the other 2 months correct, but just somehow picked up a wrong number in April.

Budgeted unit sales... 65,000
Desired ending inventory... 40,000
Needs... 105,000
Less beginning inventory... 26,000
Units to purchased... 79,000
Price per unit... $4
Costs of 79,000 units... $316,000

Somewhere along the way you were using 16,000 units as the beginning inventory versus 26,000. If you take the $104,000 inventory and divide by the cost of $4 per unit, you will see there are 26,000 units in the beginning inventory. Another way to get to this number is that you know you want 40% of the April units in inventory, so 65,000 * .4 = 26,000.

If you understand that now, why don't you recalculate the cash disbursements and I'll check it. As I said above, you have June shown correctly at $244K. I'm not sure what you did on your last attempt but the one you posted before was very close except you need to fix the $91,200 (look at the a/p figure) and the $356,000 and I think you will have it. I think you got the $80k from me but I fixed it later. I had picked something up incorrectly. Just to be clear, this is what you had:

BUDGETED CASH DISBURSEMENTS FOR MERCHANDISE PURCHASES:
April May June Quarter
Accounts payable $91,200.00 $91,200.00
April purchases $178,000.00 $178,000.00 $356,000.00
May purchases $160,000.00 $160,000.00 $320,000.00
June purchases $84,000.00 $84,000.00
Total cash payments $269,200.00 $338,000.00 $244,000.00 $447,200.00

Ok here is my problem then. I am getting the 80,000 figure for AP in April like this: March budgeted sales- (40000/2)*4 which gives me the April AP of 80000.
April May June Quarter
Accounts payable $80,000.00 $80,000.00
April purchases $130,000.00 $130,000.00 $260,000.00
May purchases $200,000.00 $200,000.00 $400,000.00
June purchases $100,000.00 $100,000.00
Total cash payments $210,000.00 $330,000.00 $300,000.00 $340,000.00

In March, the purchase requirements would have been:

60% of 40,000... 24,000
40% of 65,000... 26,000
Total units... 50,000
Cost per unit... $4
Total cost... $200,000

The disbursements are paid 50% in the current month and 50% in the next month. In March, $100,000 is paid and therefore $100,000 is in A/P - agrees to your problem.

ETA - So we know $100,000 will be paid in April. We will also pay 50% of the April purchases. Are you agreeing with me that figure is $316,000 in total for April?

I see what you are doing. You are taking the units sold times $4. What you want is that calculation where you figured 60% of the current month and 40% of the following month. Remember that sales units and purchases units are not the same.

Ok now Im good and lost. If we pay 50% in each month then why the multiplyer of 60% in March. Also why is it 40% of 65000 if that is Aprils data. I think this will be the last question of the night. I have been at this all day and still have some other stuff to get done for class. I will finish this up tomorrow after you explain this to me. Then tomorrow I will make my adjustments and try to upload the file for viewing. Will you be available tomorrow to aid in the completion of this project. I have until about 10pm CST to have it done. Thx, Will

First you look at what you purchased. Remember that you buy 40% of the next month's budgeted sales. You are looking at April's sales because you want to have 40% purchased in March and in inventory at the end of March. You buy 60% of the current month's budgeted sales. That is what you did here, with the exception that you have that $356,000 number instead of $316,000:

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 16,000 40,000 20,000 16,000
Required purchases 89,000 80,000 42,000 211,000
Cost of purchases @ $4 per unit $ 356,000.00 $ 320,000.00 $ 168,000.00 $ 844,000.00

You did this for April - June. To compute March, you would have:

60% of 40,000... 24,000
40% of 65,000... 26,000
Total units... 50,000
Cost per unit... $4
Total cost... $200,000

This $200,000 is the amount that is paid 50% in March and 50% in April.

I'll be around.

Outstanding, I thank you so much. I am really trying to get a grasp of this accounting knowledge, but it is overwhelming at times. I have been thrust in to this class with just basic knowledge of accounting principles and to top it all off it is an accelerated course which makes it even that much more challenging. With your help I know I will succeed. Thank you once again and have a great night.

Thanks. I appreciate it.

Ok see how these adjustments look. I will talk with you tomorrow. Goodnight

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 @ $4 per unit $316,000.00 $320,000.00 $168,000.00 $804,000.00


BUDGETED CASH DISBURSEMENTS FOR MERCHANDISE PURCHASES:
April May June Quarter
Accounts payable $100,000.00 $100,000.00
April purchases $130,000.00 $130,000.00 $260,000.00
May purchases $200,000.00 $200,000.00 $400,000.00
June purchases $100,000.00 $100,000.00
Total cash payments $230,000.00 $330,000.00 $300,000.00 $360,000.00

The Merchandis Purchases Budget is right.

You need to work on the disbursements. Per your Purchases Budget for April, you need to buy $316,000. Half of that will be paid in April and half in May. Therefore your total disbursement in April will be the $100,000 from March and the $158,000 from April = $258,000

Morning. Ok well I went through some of the figures and think I did the budgeted cash disbursements for merchandise purchases correctly. I also started n on the 3 month cash budget. I am hungup now here. I understand that all money financed is to be paid in full in the following month. However as the mth of may unfolds I come to see that we have a surplus of cash this month ($76400) however when we pay back previous mth finance we are at a point of needing to finance money to cover next mth, so I am a bit lost here. Thx

BUDGETED CASH DISBURSEMENTS FOR MERCHANDISE PURCHASES:
April May June Quarter
Accounts payable $100,000.00 $100,000.00
April purchases $158,000.00 $158,000.00 $316,000.00
May purchases $160,000.00 $160,000.00 $320,000.00
June purchases $84,000.00 $84,000.00
Total cash payments $258,000.00 $318,000.00 $244,000.00 $416,000.00


EARRINGS UNLIMITED
CASH BUDGET
FOR THE THREE MONTHS ENDING JUNE 30
April May June Quarter
Cash balance $74,000.00 $50,400.00
Add collections from customers $436,000.00 $695,000.00 $86,500.00 $595,600.00
Total cash available $510,000.00 $745,400.00 $86,500.00 $1,341,900.00

Less Disbursements
Merchandise purchases $258,000.00 $318,000.00 $244,000.00 $416,000.00
Advertising $200,000.00 $200,000.00 $200,000.00 $200,000.00
Rent $18,000.00 $18,000.00 $18,000.00 $18,000.00
Salaries $106,000.00 $106,000.00 $106,000.00 $106,000.00
Commissions $2,600.00 $4,000.00 $2,000.00 $8,600.00
Utilities $7,000.00 $7,000.00 $7,000.00 $7,000.00
Equipment purchases $16,000.00 $40,000.00
Dividends paid $15,000.00
Total Disbursements $606,600.00 $669,000.00 $373,000.00 $339,600.00

Excess (deficiency) of receipts
Over disbursements (96,600) 76,400
Financing:
Borrowings 146,600
Repayments 147,000
Interest
Total financing 147,000 (70,600)

Cash balance, ending 50,400

Budgeted cash disbursements are good.

Check your sales commissions. It looks like you took 4% of units rather than sales. (.04*65,000=2,600 vs .04*650,000=26,000). Other than that, you have the right idea. Why don't you fix the commissions and then I'll check May and June.

Quote:

---

Originally Posted by Will2412

Morning. Ok well I went thru some of the figures and think I did the budgeted cash disbursements for merchandise purchases correctly. I also started n on the 3 month cash budget. I am hungup now here. I understand that all money financed is to be paid in full in the following month. However as the mth of may unfolds I come to see that we have a surplus of cash this month ($76400) however when we pay back previous mth finance we are at a point of needing to finance money to cover next mth, so I am a bit lost here. Thx

---

Not so sure I agree with the way this is written. You have to have a balance of $50,000. We know you will have to borrow money to get to $50,000 in April. After you do the May work, if you have a surplus you can pay down on the borrowed amount. If you fall below $50,000, you will have to borrow more. Why don't you fix the commissions and see where you are at? The problem states: All borrowing is done at the beginning of a month, and repayments are made at the end of a month. You will only repay at the end of the month is there are surplus funds, but remember the cash balance has to stay at $50,000.

OK corrected the commissions and understand the error I made. Here are the figures as well as the beginning of the cash budget.
CASH BUDGET
FOR THE THREE MONTHS ENDING JUNE 30
April May June Quarter
Cash balance $74,000.00 $50,000.00
Add collections from customers $436,000.00 $695,000.00 $86,500.00 $595,600.00
Total cash available $510,000.00 $745,000.00 $86,500.00 $1,341,500.00

Less Disbursements
Merchandise purchases $258,000.00 $318,000.00 $244,000.00 $416,000.00
Advertising $200,000.00 $200,000.00 $200,000.00 $200,000.00
Rent $18,000.00 $18,000.00 $18,000.00 $18,000.00
Salaries $106,000.00 $106,000.00 $106,000.00 $106,000.00
Commissions $26,000.00 $40,000.00 $20,000.00 $86,000.00
Utilities $7,000.00 $7,000.00 $7,000.00 $7,000.00
Equipment purchases $16,000.00 $40,000.00
Dividends paid $15,000.00
Total Disbursements $630,000.00 $705,000.00 $391,000.00 $417,000.00

Excess (deficiency) of receipts
Over disbursements (120,000) 40,000
Financing:
Borrowings 170,000
Repayments 170,000
Interest
Total financing 170,000

Cash balance, ending 50,000

Okay, looks good through the $40,000 at the end of May. If you'll make your adjustment for the loan at the end of May and complete June, you will be done with part 2.

Ok here is what I have so far let me know how this looks
EARRINGS UNLIMITED
CASH BUDGET
FOR THE THREE MONTHS ENDING JUNE 30
April May June Quarter
Cash balance $74,000.00 $50,000.00 $50,000.00
Add collections from customers $436,000.00 $695,000.00 $86,500.00 $595,600.00
Total cash available $510,000.00 $745,000.00 $136,500.00 $1,391,500.00

Less Disbursements
Merchandise purchases $258,000.00 $318,000.00 $244,000.00 $416,000.00
Advertising $200,000.00 $200,000.00 $200,000.00 $200,000.00
Rent $18,000.00 $18,000.00 $18,000.00 $18,000.00
Salaries $106,000.00 $106,000.00 $106,000.00 $106,000.00
Commissions $26,000.00 $40,000.00 $20,000.00 $86,000.00
Utilities $7,000.00 $7,000.00 $7,000.00 $21,000.00
Equipment purchases $16,000.00 $40,000.00 $56,000.00
Dividends paid $15,000.00 $15,000.00
Total Disbursements $630,000.00 $705,000.00 $391,000.00 $918,000.00

Excess (deficiency) of receipts
Over disbursements (120,000) 40,000 (254,500) (334,500)
Financing:
Borrowings 170,000 10,000 305,000 485,000
Repayments (485,000) (485,000)
Interest (37,450) (37,450)
Total financing 170,000 10,000 305,000 (485,000)

Cash balance, ending 50,000 50,000 50,000 50,000

Look at your June collections. It's off by a decimal. Sorry I didn't notice that before. The numbers were good, but 1/10th of what they should be - I'm wondering if you used units versus sales dollars. I wish it was easier to do columns in here.

Ok I see that as well, I was short a zero on the 86500 it should have been 865000. I mad another attempt at it and think I have this part down. Pleas let me know your input so I may attempt moving on to the income statement. I totally understand about the whole column thing, I wish there were away to just attach a file to work out of. Here is my data, Thk you

April May June Quarter
Cash balance $74,000.00 $50,000.00 $50,000.00 $174,000.00
Add collections from customers $436,000.00 $695,000.00 $865,000.00 $595,600.00
Total cash available $510,000.00 $745,000.00 $915,000.00 $2,170,000.00

Less Disbursements
Merchandise purchases $258,000.00 $318,000.00 $244,000.00 $416,000.00
Advertising $200,000.00 $200,000.00 $200,000.00 $200,000.00
Rent $18,000.00 $18,000.00 $18,000.00 $18,000.00
Salaries $106,000.00 $106,000.00 $106,000.00 $106,000.00
Commissions $26,000.00 $40,000.00 $20,000.00 $86,000.00
Utilities $7,000.00 $7,000.00 $7,000.00 $21,000.00
Equipment purchases $16,000.00 $40,000.00 $56,000.00
Dividends paid $15,000.00 $15,000.00
Total Disbursements $630,000.00 $705,000.00 $635,000.00 $918,000.00

Excess (deficiency) of receipts
Over disbursements (120,000) 40,000 280,000 200,000
Financing:
Borrowings 170,000 10,000 180,000
Repayments (180,000) (180,000)
Interest 16,100 16,100
Total financing 170,000 10,000 (163,900) (16,100)

Cash balance, ending 50,000 50,000 116,100 116,100

We're getting there. It looks like you added the interest payment instead of subtracting it.. Also not understanding how you computed your interest. It is 12% a year, so 1% a month. Remember the loans were taken at the end of the month/first day of following month. So, for example, on the $170k you only have 2 months of interest - May and June.

Ok I think I figured that out. I still know something is wrong though. I was given comparisson data to check my answers with. My first 2 checks work but the last 2 do not. Here are the answers I was provided
Total cash collections: $1,996,000

Cost of purchases: $804,000

Cash balance ending quarter: $94,700

Total liabilities and stockholders equity: $1,618,700

I have taken a chance and gone through the rest of the assignment. Please check this and let me know what else is to be done. I mad corrections to the cash budget interest and also made an adjustment to commssions as well can you please check that. Here is my data for the cash budget, income statement and the balance sheet. I know there still exists problems just can't find them. I will be back shortly, quick errand to run. Thanks and ttys

CASH BUDGET
FOR THE THREE MONTHS ENDING JUNE 30
April May June Quarter
Cash balance $74,000.00 $50,000.00 $50,000.00 $174,000.00
Add collections from customers $436,000.00 $695,000.00 $865,000.00 $595,600.00
Total cash available $510,000.00 $745,000.00 $915,000.00 $2,170,000.00

Less Disbursements
Merchandise purchases $258,000.00 $318,000.00 $244,000.00 $416,000.00
Advertising $200,000.00 $200,000.00 $200,000.00 $200,000.00
Rent $18,000.00 $18,000.00 $18,000.00 $18,000.00
Salaries $106,000.00 $106,000.00 $106,000.00 $106,000.00
Commissions $26,000.00 $40,000.00 $20,000.00 $86,000.00
Utilities $7,000.00 $7,000.00 $7,000.00 $21,000.00
Equipment purchases $16,000.00 $40,000.00 $56,000.00
Dividends paid $15,000.00 $15,000.00
Total Disbursements $630,000.00 $705,000.00 $635,000.00 $918,000.00

Excess (deficiency) of receipts
over disbursements (120,000) 40,000 280,000 1,252,000
Financing:
Borrowings 170,000 10,000 180,000
Repayments 180,000 (180,000)
Interest 3,500 (3,500)
Total financing 170,000 10,000 183,500 (363,500)

Cash balance, ending 50,000 50,000 463,500 1,615,500


EARRINGS UNLIMITED
BUDGETED INCOME STATEMENT
FOR THE THREE MONTHS ENDED JUNE 30

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 200,000
Rent 18,000
Salaries 106,000
Utilities 21,000
Insurance 3,000
Depreciation 14,000 362,000
Net operating income 842,000
Interest expense (3,500)
Net income 845,500


EARRINGS UNLIMITED
BUDGETED BALANCE SHEET
JUNE 30

Assets:

Cash 94,700
Accounts receivable (see below) 1,000,000
Inventory 104,000
Prepaid insurance 21,000
Property and equipment, net 95,000
Total assets 1,314,700


Liabilities and Stockholders' Equity

Accounts payable, purchases 100,000
Dividends payable 15,000
Capital stock 800,000
Retained earnings (see below)
Total liabilities and stockholders' equity 915,000


Accounts receivable at June 30:
May sales x ?% 900,000
June sales x ?% 100,000
Total 1,000,000


Retained earnings at June 30:
Balance, March 31 580,000
Add net income 845,500
Total 1,425,500
Less dividends declared 15,000
Balance, June 30 1,410,500

The problem with your May cash is you are adding:

Excess of receipts... $280,000
Pay off loan... 180,000
Pay interest... 3,500

Your balance... $463,500

You are paying the loan and interest, and that will make your cash decrease. The only way I can make the $94,700 figure work is to charge 3 months of interest on the $170,000 and 2 months on the $10,000. Since we didn't borrow the money until the end of the month, that doesn't make sense. Did you get those figures from another student, the teacher, the textbook?

You are good through the contribution margin, but you are only showing one month's worth of the fixed expenses. The IS is for the 3 months ending June 30.

I'll let you make those corrections before I check the Balance Sheet.

Ok I made the adjustment by excluding the excess of receipts to include only the loan payment and interest. I also made the adjustment on the interest to include 3 months for Apr and 2 months interest for may. My total for May is now 465300 and the interest is at 5300. I am at a loss on how to get the figure of 94700. These figures were uploaded by the instructor to our documents. Here is what my correction looks like.

April May June Quarter
Cash balance $74,000.00 $50,000.00 $50,000.00 $174,000.00
Add collections from customers $436,000.00 $695,000.00 $865,000.00 $595,600.00
Total cash available $510,000.00 $745,000.00 $915,000.00 $2,170,000.00

Less Disbursements
Merchandise purchases $258,000.00 $318,000.00 $244,000.00 $416,000.00
Advertising $200,000.00 $200,000.00 $200,000.00 $200,000.00
Rent $18,000.00 $18,000.00 $18,000.00 $18,000.00
Salaries $106,000.00 $106,000.00 $106,000.00 $106,000.00
Commissions $26,000.00 $40,000.00 $20,000.00 $86,000.00
Utilities $7,000.00 $7,000.00 $7,000.00 $21,000.00
Equipment purchases $16,000.00 $40,000.00 $56,000.00
Dividends paid $15,000.00 $15,000.00
Total Disbursements $630,000.00 $705,000.00 $635,000.00 $918,000.00

Excess (deficiency) of receipts
Over disbursements (120,000) 40,000 280,000 1,252,000
Financing:
Borrowings 170,000 10,000 180,000
Repayments 180,000 (180,000)
Interest 5,300 (5,300)
Total financing 170,000 10,000 185,300 (365,300)

Cash balance, ending 50,000 50,000 465,300 1,617,300

I showed you above what you are doing wrong. For June:

Cash bal... 50,000
Collections... 865,000
Available... 915,000
Disbursements... (635,000)
Sub-total... 280,000 (you were right to this point)
Paid loan... (180,000)
Paid interest... (5,300)
Cash bal... 94,700

You are showing a total for the 3 months for $1,617,300. You don't need that as it has no meaning. Your balance at 6/30 is $94,700.

You'll need to correct your Income Statement now re the fixed expenses. I still don't agree with your teacher's interest amount, but it isn't that big a deal.

Okay on to the BS - I worked it out and have the balance your teacher has (using her interest). You need to go step by step. Almost all of the numbers will change from the original, except Capital Stock. As an example, P&E has new purchases and depreciation. It took me a while to do this, so don't be surprised if this takes a while.

ETA-Hah - it just occurred to me. I kept thinking the loan was taken out at the end of the month based on the cash flow, but it was taken out at the beginning of the month based on the budget. Okay - I agree with the interest. Hope I didn't confuse you there. So, it's 1 month of $170,000 and 2 months of $180,000 at 1% per month. :)

Ok I resolved the issue and figured out the interest coming up with 94700. Got simple mistakes can cause major problems for sure. Ok I think I have the income statement correct producing a new net income of 836700. As for the BS well this is my problem. I don't understand what to do at all here. If I perform calculations one way I end up less than the check amount. If I do it another I end up way over the check data. Can you give me some guidance as to exactly what I need to do to fiish this project. I am so close but would not be anywhere near here without you.
I will again send my data

EARRINGS UNLIMITED
BUDGETED INCOME STATEMENT
FOR THE THREE MONTHS ENDED JUNE 30

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 200,000
Rent 18,000
Salaries 106,000
Utilities 21,000
Insurance 3,000
Depreciation 14,000 362,000
Net operating income 842,000
Interest expense (5,300)
Net income 836,700


EARRINGS UNLIMITED
BUDGETED BALANCE SHEET
JUNE 30

Assets:

Cash 94,700
Accounts receivable (see below) 1,000,000
Inventory 104,000
Prepaid insurance 21,000
Property and equipment, net 95,000
Total assets 1,314,700


Liabilities and Stockholders' Equity

Accounts payable, purchases 100,000
Dividends payable 15,000
Capital stock 800,000
Retained earnings (see below) 1,401,700
Total liabilities and stockholders' equity 2,316,700


Accounts receivable at June 30:
May sales x ?% 900,000
June sales x ?% 100,000
Total 1,000,000


Retained earnings at June 30:
Balance, March 31 580,000
Add net income 836,700
Total 1,416,700
Less dividends declared 15,000
Balance, June 30 1,401,700