Necklaces, bracelets, and earrings. Although styles within each line vary, the average unit selling price and unit cost is the same within each line. Estimated unit sales, by product line, for the year , are as follow:
|
Quarter
|
Bracelets
|
Earrings
|
Necklaces
|
Rings
|
|
3rd, 2014
|
800
|
4500
|
2000
|
600
|
|
4th, 2014
|
1700
|
9000
|
3200
|
750
|
|
1st, 2015
|
1500
|
6000
|
3000
|
1000
|
Estimated selling prices for the first quarter are bracelets, $6 per unit; earrings, $8 per unit; necklaces, $10 per unit; and rings, $12 per unit. 70% of Jamboree's sales are for cash. Cash sales of over $50 qualify for a 5% discount. Of the cash sales, approximately 25% qualify for the discount.
30% of Jamboree's sales are on credit. Credit sales have historically been collected in the following pattern:
Quarter of sale, 40%; quarter after sale, 50%; two quarters after sale, 8%; Uncollectibles, 2%. For budgeting, assume uncollectibles are written off in the quarter of sale.
Credit sales in the first quarter totaled $21,600. Credit sales in the second quarter totaled $29,700.
Jamboree's merchandise inventory policy is to keep 25% of the following quarter's expected sales on hand at the end of each quarter. The estimated merchandise cost per unit is bracelets, $3; earrings, $5, necklaces, $6; and rings $8.
Jamboree pays for 40% of merchandise purchases with cash, and takes a 10% discount. Credit purchases are paid as follows: quarter after purchase: 60%; two quarters after purchase: 40%. For simplicity, do not worry
Total merchandise purchases for the first and second quarter were, respectively, $48,125 and $55,800.
Operating expenses are paid for as incurred.
Jamboree currently has 5 employees. During the first and second quarters, salaries and fringe benefits per employee were $4,500 per quarter. Salaries and fringe are expected to increase to $4,725 in the third and fourth quarters. In addition to salaries, total commissions of 6.5% of the gross amount sales are paid out. This percentage includes fringe, so no additional calculation needs to be done with respect to commissions.
Rent is currently $3,600 per quarter. Rent payments are expected to increase to $4,200 per quarter beginning in the third quarter. Fixed utilities average $900 per quarter. Variable utility costs average 12% of rent.
Insurance costs are currently $750 per quarter. Because Jamboree has never made a claim, insurance is expected to decrease by $150 beginning in the second quarter.
Jamboree depreciates its store fixtures using the straight line method. The original cost of the store fixtures was $120,000, with an estimated useful life of 9 years and an estimated salvage value of $20,000. At the beginning of the third quarter, total accumulated depreciation was $50,000. There are no plans for purchasing additional long-lived assets for the remainder of the year.
Miscellaneous expenses are $2,500 per quarter.
Jamboree borrowed $15,000 on April 1, 2011 by signing a 5 year, 8%, installment note requiring quarterly payments. You will need to set up an installment note amortization schedule. You may use the schedule from Blue Flame for reference, or you may refer to a basic accounting textbook. Payments began on June 30th of 2011.
Jamboree has a $500,000 line of credit with a local bank for temporary cash shortfalls. The annual interest rate on the line of credit is 8%. Interest must be paid each quarter on outstanding principle. The company's policy is to pay back the credit line as quickly as possible. For simplicity, assume borrowings, interest payments and principal payments are made on the last day of the quarter.
As part of the line of credit agreement with the bank, Jamboree invests excess cash in a money market fund with the bank. The money market fund earns a special rate of 5% annually. Assume money is placed in the fund on the last day of the quarter. In the event of cash shortfalls, money market balances are used to meet cash needs before the line of credit is used.
The company declares and pays quarterly cash dividends equal to 4% of operating income (operating income means income excluding "other" revenues and expenses). Dividends are paid on the last day of the quarter. If there is an operating loss for the quarter, no dividend is paid.
Jamboree's minimum desired cash balance each quarter is $4,000.
Jamboree's tax rate is 30%. Taxes are paid at the end of each quarter. For simplicity, assume if the company has no profit, then taxes are zero (in other words, don't worry about loss carrybacks and carryforwards).
The following budgeted balance sheet is available for June 30th (the end of the second quarter):
|
Assets
|
|
|
Liabilities
|
|
|
Cash
|
$4,000
|
|
Accounts Payable
|
$45,030
|
|
Accounts Receivable
|
$18,954
|
|
Line of Credit
|
$5,000
|
|
Merchandise Inventory
|
$10,425*
|
|
Note Payable
|
$5,937
|
|
Store Fixtures, net of depreciation
|
$70,000
|
|
Total Liabilities
|
$55,967
|
|
|
|
Owners' Equity
|
|
|
|
|
Capital Stock
|
$30,000
|
|
|
|
Retained Earnings
|
$17,412
|
|
|
|
Total Owners'Equity
|
$47,412
|
|
Total Assets
|
$103,379
|
|
Total Liabilities & Owners' Equity
|
$103,379
|
*The merchandise inventory consists of 200 bracelets @ $3 each; 1,125 pairs of earrings @ $5 per pair; 500 necklaces @ $6 each; and 150 rings @ $8 each.
Requirements:
Using an Excel compatible spreadsheet program, prepare Jamboree's master budget for the 3rd and 4th quarter of 2014. Your master budget should include the following budgets/schedules:
Sales
Merchandise Purchase
Collections/Accounts Receivable
Disbursements for Merchandise/Accounts Payable
Selling and Administrative
Cash
Cost of Goods Sold
Income Statement
Balance Sheet
You should prepare the spreadsheet as though it is for your manager, who knows nothing about spreadsheets. Therefore, the user must be able to change amounts in the budget (units, dollar values, etc.) in order to play what-if games without understanding how to actually use Excel. This means you must make the spreadsheet very easy to use, yet very flexible.