Budget Data

XYZ Co$.$

Assets as at March $31,2011$ Liabilities and Equity as at March $31,2011$

Cash $6000$ Accounts Payable $9000$

Accounts Receivable $21000$ Lease Payable $12000$

Inventory$($note $1)16000$

Prepaid Supplies $800$ Owners Equity $57800$

Equipment $35000$

Total Assets $78800$ Total Liabilities and Equity $78800$

Required:

Prepare a Quarterly Master budget for XYZ Co$.$ For the months of April, May and June, Up to and Including the Income Statement

Notes:

$1)$ Inventory as at March $31,2011$ consists of $6,400$ pens at a cost of $$2,50$ per pen.

$2)$ XYZ Co$.$ sells boxes at a selling price per pen is: $$7\mathrm{.}50$

$3)$ Expected Sales in units:

April $10,000$

May $12,000$

June $9,000$

July $8,000$

August $13,000$

$4)$ All sales are on credit and are collected as follows:

Month of Sale $75\%$

Month after Sale $25\%$

There are no bad debts

$5)$ The Wholesale cost per box is: $$2\mathrm{.}50$

$6)$ Payments for wholesale purchases are:

Month of Purchase $70\%$

Month after Purchase $30\%$

$7)$ Desired Ending Inventory is $15\%$ of next months sales

$8)$ Operating expenses monthly are:

Wages $$11,000\mathrm{.}00$

Freight $1\%$ of sales

Rent $$3,000\mathrm{.}00$

Depreciation $$1,200\mathrm{.}00$

Miscellaneaous $$500\mathrm{.}00$

$7)$ Office Equipment is purchased in April for: $$50,000$

$8)$ Monthly minimum cash balance required $$5,000\mathrm{.}00$

Monthly interest on borrowed funds: $1\%$

Assume funds are borrowed at the beginning

of the month that they are needed and repaid at the end

of the month where excess cash permits repayment

Assume simple interest rate, no compounding

Interest is repaid aXYZ Co$.$

April May June Total July

Sales Budget

Sales Units $10000120009000310008000$

Selling Price $$7\mathrm{.}50$ $$7\mathrm{.}50$ $$7\mathrm{.}50$ $$7\mathrm{.}50$ $$7\mathrm{.}50$

Total Sales $$75,000\mathrm{.}00$ $$90,000\mathrm{.}00$ $$67,500\mathrm{.}00$ $$232,500\mathrm{.}00$ $$60,000$

Correct Correct Correct Correct Correct

Cash Collection Budget

April May June Total

Collections from Current Month $75\%$ $$56,250$ $$67,500$ $$50,625$ $$174,375$

Collections from Previous Month $25\%$ $$21,000$ $$18,750$ $$22,500$ $$62,250$

Total Cash Collections from Sales $$77,250\mathrm{.}00$ $$86,250\mathrm{.}00$ $$73,125\mathrm{.}00$ $$236,625\mathrm{.}00$

Correct Correct Correct Correct

Purchases Budget April May June Total

Sales Units $10000120009000310008000$

Desired Ending Inventory $15\%1800135012001200$

Beginning Inventory $6400180013501200$

Required Purchases in units $540011550885025800$

Cost per box $$2\mathrm{.}50$ $$2\mathrm{.}50$ $$2\mathrm{.}50$ $$2\mathrm{.}50$

Cost of box Purchases $$13,500\mathrm{.}00$ $$28,875\mathrm{.}00$ $$22,125\mathrm{.}00$ $$64,500\mathrm{.}00$

Correct Correct Correct Correct

Cash Disbursements for Purchases

April May June Total

Payment for Current Month Purchases $70\%$ $$9,450$ $$20,213$ $$15,488$

Payment for Prior Month Purchases $30\%$ $$9,000$

Total Cash Disbursements for Purchases $$18,450\mathrm{.}00$

Correct

Operating Expenses Budget

April May June Total

Wages $$11,000$ $$11,000$ $$11,000$ $$33,000$

Freight $$750$ $$900$ $$675$ $$2,325$

Rent $$3,000$ $$3,000$ $$3,000$ $$9,000$

Depreciation $$1,200$ $$1,200$ $$1,200$ $$3,600$

Miscellaneaous $$500$ $$500$ $$500$ $$1,500$

Total Operating Expenses $$16,450\mathrm{.}00$ $$16,600\mathrm{.}00$ $$16,375\mathrm{.}00$ $$49,425\mathrm{.}00$

Correct Correct Correct Correct

Cash Budget

April May June Total

Beginning Cash Balance $$6,000$ $$83,250$ $$169,500$ $$258,750$

Cash Collections $$77,250$ $$86,250$ $$73,125$ $$236,625$

Total Cash Available $$83,250\mathrm{.}00$ $$169,500\mathrm{.}00$ $$242,625\mathrm{.}00$ $$495,375\mathrm{.}00$

Cash Disbursements:

Purchases $$18,450$

Operating Expenses $$16,450$

Other $$50,000$

Total Cash Disbursements $$84,900$

Net Cash Available $-$$$1,650$

Financing:

Bank Borrowing

Loan Repayment

Interest Cost

Total Financing

Ending Cash Balance the time of the loan repayment