Question

ACCT 116 Budget Assignment

Kline Sisters Company operates a gift shop where peak sales and activity occur in the months of December and January. Data regarding the store's operations follow:

• Sales are budgeted at $360,000 for January, 320,000 for February, and $250,000 for March and $240,000 in April.
• 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 76% of sales.
• The company desires ending merchandise inventory to equal 13% 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.
• Monthly operating expenses to be paid in cash are $12,000.
• Equipment purchases of $40,000 in February and $30,000 in March were paid in cash.
• Monthly depreciation is $15,000.
• Dividends of $40,000 were declared and paid in January.
• 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 $30,000.
• Ignore income taxes.
• All accounts receivable from December 31 will be collected in January and all accounts payable at December 31 will be paid in January.
• The balance sheet as of December 31^st:

Kline Sisters Company
Balance Sheet
12/31/14
Assets:
Cash				$22,000
Net Accounts Receivable				$83,000
Merchandise Inventory				$36,000
Property Plant and Equipment			$1,600,000
Less: accumulated depreciation			$588,000	$1,012,000
Total Assets				$1,153,000
Liabilities & Stockholder's Equity
Accounts Payable				$190,000
Common Stock				$350,000
Retained Earnings				$613,000
Total liabilities and stockholder's equity				$1,153,000

Prepare the following budgets for each month January, February, March 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 March 31^st ?
  

b.   Prepare a Merchandise Purchases Budget and a Schedule of Expected Cash Disbursements

• What is the budgeted accounts payable at March 31^st ?

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

Extra credit – 5 points – Prepare a Budgeted Balance Sheet – it must balance!

                        5 points – Prepare a letter to the CEO identifying three items of interest on the budget and provide recommendations

Please highlight your answers.

Formatting is as important as the correct answer for this project. Your spreadsheet should be in good form, presented and printed clearly, and labeled appropriately. Your print out should be in report format and clearly presented. Each budget should not span more than one page, i.e. pay attention to what prints out on each page.

In addition to your spreadsheet, you MUST print out your spreadsheet formulas. Use “control ~” to toggle over to view the formulas and then print again. Projects submitted without formulas will only receive half credit.

Check figures:

cash collections at end of quarter = $814,800

Deficiency in cash at the end of the quarter = $(99,206)

You will need to borrow and repay during the quarter.

(Note that due to varying assumptions made in the budgeting process, your numbers may be slightly different than mine. If your balance sheet balances, then your budget works.)

DO NOT PLUG ANYTHING!

*****Please post  an excel version and the formula excel version (where I can see all of your work)****

Thank you!!!!

Answer 1

a	Expected Cash Collections
	Particulars	Jan	Feb	Mar	Total
	Budgeted Sales	$360,000	$320,000	$250,000	$930,000
	Expected Collections
	In same month=30%	$108,000	$96,000	$75,000	$279,000
	In the month following
	Jan-Dec accts receivable
	Feb-Jan sales*65%
	Mar-Feb sales*65%	$83,000	$234,000	$208,000	$525,000
	In the second Month Following
	Jan
	Feb
	March-Jan Sales *3%			$10,800	$10,800
	Total Expected cash collections	$191,000	$330,000	$293,800	$814,800
	What is the budgeted accounts receivable at March 31st?
	Budgeted accounts receivable	Feb Month + March Month
		(320000*3%)+250000*(65%+3%)
		$9600+(250000*68%)
		9600+170000
		179600
b	Prepare a Merchandise Purchases Budget and a Schedule of Expected Cash Disbursements
	Particulars	jan	Feb	Mar	Total
	Budgeted Sales	$360,000	$320,000	$250,000	$930,000
	Cost of Goods Sold *76%	$273,600	$243,200	$190,000	$706,800
	Desired Ending Inventory
	13% *following months COGS
	March-240000*76%*13%	$31,616	$24,700	$23,712	$23,712
	Total	$305,216	$267,900	$213,712	$730,512
	Beginning Inventory	$36,000	$31,616	$24,700	$36,000
	Budgeted Purchases	$269,216	$236,284	$189,012	$694,512
	Expected Cash disbursements
	Particulars	jan	Feb	Mar	Total
	Budgeted Purchases	$269,216	$236,284	$189,012	$694,512
	Payment for purchases
	From Ending accounts Payable	$190,000			$190,000
	50% in the same month	$134,608	$118,142	$94,506	$347,256
	50% in the following month		$134,608	$118,142	$252,750
	Payment for operating expenses	$12,000	$12,000	$12,000	$36,000
	Equipment purchases		$40,000	$30,000	$70,000
	Dividends paid	$40,000			$40,000
	Total Expected cash Disbursements	$376,608	$304,750	$254,648	$936,006
	What is the budgeted accounts payable at March 31st?
	Budgeted Accounts Payable	March Month
		$189012*50%
		$94,506
c	Cash Budget
	Preparation of cash budget for three months Jan ,Feb and March
	Particulars	Jan	Feb	Mar	Total
	Beginning Cash Balance	$22,000	$30,392	$55,642	$22,000
	Add Expected cash collections	$191,000	$330,000	$293,800	$814,800
	Total Cash Available	$213,000	$360,392	$349,442	$836,800
	Less Expected Cash Disbursements	$376,608	$304,750	$254,648	$936,006
	Cash Surplus (Deficit)	($163,608)	$55,642	$94,794	($99,206)
	Add Borrowings	$194,000			$194,000
	(1000*164 lots)+(1000*30 Lots)
	Less Interest Payments
	[194000*12%*3/12			$5,820	$5,820
	Ending Cash Balance	$30,392	$55,642	$30,000	$88,974
d	Budgeted Income Statement
	Kline Sisters Company
	Income Statement
	for the qtr ended March 31
	Particulars	Amount
	Net Sales	$930,000
	Less COGS	$706,800
	Gross Profit	$223,200
	Less Operating Expenses	$36,000
	Less Depreciation [15000*3 months]	$45,000
	Less Interest Expenses	$5,820
	Net Income	$136,380