PROJECT 1 - Excel Template Student Name: SALES BUDGET: April May June Quarter Budgeted unit sales Selling price per unit Total sales SCHEDULE OF...
View the step-by-step solution to:

Question

Please helpBDB36886-DB41-4509-8AEE-E90C7331F88D.jpeg

src="/qa/attachment/10935674/" alt="0A2DBE8F-0EC2-4B77-BFBE-74084273BA07.jpeg" />F71D809A-2C24-449C-9476-49C3AACB8A09.jpegD386D52B-A437-4178-8D85-CAE03830A998.jpeg9E66EF0E-5497-4156-8545-94E7362FAD9D.jpeg

0A2DBE8F-0EC2-4B77-BFBE-74084273BA07.jpeg

PROJECT 1 - Excel Template
Student Name:
SALES BUDGET:
April
May
June
Quarter
Budgeted unit sales
Selling price per unit
Total sales
SCHEDULE OF EXPECTED CASH COLLECTIONS:
April
May
June
Quarter
February Sales
March Sales
April Sales
May Sales
June Sales
Total cash collections
MERCHANDISE PURCHASES BUDGET:
April
May
June
Quarter
Budgeted unit sales
Add desired ending Inventory
Total needs
Less beginning Inventory
Required purchases
Cost of purchases @ 54 per unit
BUDGETED CASH DISBURSEMENT'S FOR MERCHANDISE PURCHASES
April
May
June
Quarter
March Purchases
April Purchases
May Purchases
June Purchases
Total cash payments
EARRING S UNLIMITED
CASH BUDGET
FOR THE 3 MONTHS ENDING JUNE 30
April
May
June
Quarter
Cash balance
Add collections from customers
Total cash available
Less disbursements
Merchandise purchases
Advertising
Rent
Salaries
Commissions
Utilities
Equipment purchases
Dividends paid
O
II

9E66EF0E-5497-4156-8545-94E7362FAD9D.jpeg

COURSE PROJECT 1 INSTRUCTIONS
You have just been contracted as a new management trainee by Earrings Unlimited, a
distributor of earrings to various retail outlets 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 experienced a shortage of cash.
Since you are well trained in budgeting, you have decided to prepare a master budget
for the upcoming second quarter. 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 per
pair. Actual sales of earrings for the last three months and budgeted sales for the next
six months follow:
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 bracelets
sold in the following month.

BDB36886-DB41-4509-8AEE-E90C7331F88D.jpeg

Total disbursements
Excess (deficiency) of receipts
over disbursements
Financing:
Borrowings
Repayments
Interest
Total financing
Cash balance, ending
13
E

D386D52B-A437-4178-8D85-CAE03830A998.jpeg

Suppliers are paid $4 for each bracelet. One-half of a month's purchases is paid for in
the month of purchase; the other half 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
$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.

F71D809A-2C24-449C-9476-49C3AACB8A09.jpeg

Other relevant data is given below:
Cash balance as of March 31
$74,000
Inventory balance as of March 31
$104,000
Merchandise purchases for March
$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 June 30. Include the following
detailed budgets:
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

Recently Asked Questions

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question
Ask Expert Tutors You can ask You can ask ( soon) You can ask (will expire )
Answers in as fast as 15 minutes