Question

You just got a job with Bling, Inc. The company distributes bracelets to various retail outlets across the country. In the past, the company hasn’t done budgeting and they run short on cash periodically.

Because your wonderful Cost Accounting teacher prepared you so well, you decided to prepare comprehensive budgets for the second quarter in order to show management the benefits. You included other departments in your decision making process and have gathered the information below.

The company sells many styles of bracelets, but they are all sold for $16 each. Actual sales of bracelets in units for the last three months and budgeted sales are below:

January (actual)	22,000	June (budget)	52,000
February (actual)	28,000	July (budget)	32,000
March (actual)	42,000	August (budget)	30,000
April (budget)	67,000	September (budget)	27,000
May (budget)	102,000

The increase in sales before and during May is due to Mother’s Day. Bling, Inc. wants to make sure they have enough inventory on hand at the end of each month to supply 40% of the bracelets sold in the following month.

Manufacturers are paid $5 for each bracelet. 50% of a month’s purchases are paid for in the month of purchase; the remainder is paid for in the following month. All credit sales are due (without discount), with no discount, in15 days. Based on history, Bling, Inc. has found, that only 20% of a month’s sales are collected in the month of sale. 70% is collected in the following month, and the remaining 10% is collected in the second month following sale. Bad debts have been immaterial and can be ignored.

Monthly operating expenses for the company are given below:

Variable:
Sales commissions		4%	of sales
Fixed:
Advertising	$	300,000
Rent	$	28,000
Salaries	$	126,000
Utilities	$	12,000
Insurance	$	4,000
Depreciation	$	24,000

Insurance is paid in November of each year and covers a 12 month period.

The company expects to buy $21,000 in new, better equipment in May and $50,000 in new equipment in June. The new equipment purchases in both months will be paid in cash. The company declares dividends of $22,500 each quarter, payable in the first month of the following quarter.

A listing of Bling, Inc.’s accounts as of March 31 is given below:

Assets
Cash	$	84,000
Accounts receivable ($44,800 February sales;    $537,600 March sales)		582,400
Inventory		134,000
Prepaid insurance		26,000
Property and equipment (net)		1,050,000
Total assets	$	1,876,400
Liabilities and Stockholders’ Equity
Accounts payable	$	110,000
Dividends payable		22,500
Common stock		1,000,000
Retained earnings		743,900
Total liabilities and stockholders’ equity	$	1,876,400

Bling, Inc. will always maintain a minimum cash amount of $60,000. All borrowing is always done at the beginning of a month; all repayments (if any and able) are made at the end of the month.

The company can borrow in increments of $1,000 at the beginning of each month. The interest rate is 1% per month (assume no compounding interest). At the end of the quarter, the company would pay the bank all of the accumulated interest on the loan and as much of the loan as possible (in increments of $1,000), while maintaining their minimum cash balance.

Required: YOUR FINAL WORK MUST BE TYPED AND YOU SHOULD USE EXCEL/LINKED FORMULAS. TEAMS OF UP TO 3 STUDENTS ARE PERMITTED. You must submit your work, HARD COPY, at the beginning of CLASS and one member of your team must upload the file to course web. The file name must be the last names of each group member (5 point deduction if not). 10 points will be DEDUCTED if the documents are not easily readable and formatted professionally [40 points].

1.

Prepare a master budget for the three-month period ending June 30. Include the following detailed budgets: A sales budget, by month and in total for the quarter. A schedule of expected cash collections from sales, by month and in total for the quarter. A merchandise purchase budget in units and dollars. Show the budget by month and in total for the quarter (round unit cost of purchases to 1 decimal point). A schedule of expected cash disbursements for purchases by month and in total for the quarter. 2. A cash budget. Show the budget by month and in total for the quarter. You must show disbursements separately for partial credit if totals are incorrect. For example, merchandise, advertising, rent etc. 3. A budgeted income statement for the three-month period ending June 30 (Use the internal approach for reporting). 4. A budgeted balance sheet as of June 30th in good form.

Answer 1

	Bling Company
	Sales Budget
		April	May	June	Total
	Sales (units)	67000	102000	52000	221000.00
	Unit Price	16	16.00	16	16
	Sales (Dollars)	1072000	1632000	832000	3536000
	Bling Company
	Schedule of Cash collection
		Feb	Mar	April	May	June	Total
	Sales (Amount)	448000	672000	1072000	1632000	832000	3536000
	Sales Same month -20%			214400	326400	166400	707200
	Sales following month - 70%			470400	750400	1142400	2363200	665600
	Sales Second month - 10%			44800	67200	107200	219200	163200
	Total Collection			729600	1144000	1416000	3289600	828800
	Bling Company
	Purchase Budget
		April	May	June	Total
	Sales	67000	102000	52000	221000
	Add : Ending Inventory	40800	20800	12800	12800
	Total Production	107800	122800	64800	233800
	Less : Opening Inventory	26800	40800	20800
	Purchases (Units)	81000	82000	44000	207000
	Unit Price	$5	$5	$5
	Purchases (Dollars)	$405,000	$410,000	$220,000	$1,035,000
	Bling Company
	Schedule of cash payment for Purchases
		April	May	June	Total
	Purchases (Dollars)	405000	410000	220000	1035000
	Same Month - 50%	202500	205000	110000	517500
	Following Month - 50%	110000	202500	205000	517500
	Total	312500	407500	315000	1035000
	Bling Company
	Cash Budget
		April	May	June	Total
	Beginning Cash Balance	$84,000	$60,720	$244,940	$84,000
	Add : Collections	729600	1144000	1416000	3289600
	Total Cash available	$813,600	$1,204,720	$1,660,940	$3,373,600
	Less : Payments
	Purchases	312500	407500	315000	1035000
	Sales Comission (4% of sales)	42880	65280	33280	141440
	Advertising	300000	300000	300000	900000
	Rent	28000	28000	28000	84000
	Salaries	$126,000	$126,000	$126,000	378000
	Utilities	$12,000	$12,000	$12,000	36000
	Equipment		$21,000	$50,000	71000
	Dividends	$22,500			22500
	Total Disbursements	843880	959780	864280	2667940
	Preliminary Cash Balance	($30,280)	$244,940	$796,660	$705,660
	Loan Taken	91000			91000
	Interest Payments			2730	2730
	Loan Repayments			91000	91000
	Ending Cash Balance	$60,720	$244,940	$702,930	$702,930
	Bling Inc
	Income Statement
		April	May	June	Total
	Sales	1072000	1632000	832000	3536000
	Less : Cost of Goods Sold	$335,000	$510,000	$260,000	$1,105,000
	Gross Profit	$737,000	$1,122,000	$572,000	$2,431,000
	Less : Expenses
	Sales Comission (4% of sales)	42880	65280	33280	141440
	Advertising	300000	300000	300000	900000
	Rent	28000	28000	28000	84000
	Salaries	126000	126000	126000	378000
	Utilities	12000	12000	12000	36000
	Depreciation	$24,000	$24,000	$24,000	$72,000
	Insurance	$4,000	$4,000	$4,000	$12,000
	Interest			2730	2730
	Total Expenses	536880	559280	530010	1626170
	Net Income	$200,120	$562,720	$41,990	$804,830
	Less : Dividends			$22,500	$22,500
					$782,330
	Bling Inc
	Balance Sheet
	ASSETS
	Current Assets
	Cash		$702,930
	Accounts Receivable		828800
	Inventory		64000
	Prepaid Insurance		14000
	Total Current Assets			$1,609,730
	Property and Equipment		$1,050,000
	Add : New Purchase		$71,000
	Less : Depreciation		$72,000	$1,049,000
	Total Assets			$2,658,730
	LIABILITIES AND SHAREHOLDERS EQUITY
	Current Liabilities
	Accounts Payable		110000
	Dividend Payable		22500	132500
	Common Stock			1000000
	Retained Earnings		743900+782330	1526230
	Total Liabilities and Shareholders Equity			2658730