Question

Requirement: Complete the various budget schedules using Excel.

The Distribution Center of 123 Oil and Gas Company wants a master budget for the next three months, beginning January 1st. It desires an ending minimum cash balance of $4,000 each month. Sales are forecasted at an average selling price/transfer price of $4 per widget. In January, the Distribution Centre is beginning just-in-time deliveries from suppliers, which means that purchases equal expected sales. The December 31 inventory balance will be drawn down to $5,000, which will be the desired ending inventory thereafter. Purchase price per widget is $2. Purchases during any given month are paid in full during the following month. All sales are on credit, payable within thirty days, but experience has shown that 60 percent of current sales are collected in the current month, 30 percent in the next month, and 10 percent in the month thereafter. Bad debts are negligible. The Distribution Centre sells to related sister corporations as well as outside/external sales. The following are some of the expenses for the Distribution Centre:

Wages and salaries $12000/month Insurance expired 100/month Depreciation 200/month Miscellaneous 2000/month

Rent 200/month + 10% of quarterly sales over $10,000

Cash dividends of $1,000 are to be paid quarterly, beginning January 15, and are declared on the fifteenth of the previous month. All operating expenses are paid as incurred, except insurance, depreciation, and rent. Rent of $200 is paid at the beginning of each month and the additional 10 percent of sales is paid quarterly on the tenth of the month following the quarter. The next settlement is due January 10.

The company plans to buy some new office fixtures for $2,000 cash in March. To the distribution company this will be considered a capital purchase.

Money can be borrowed and repaid in multiples of $500, at an interest rate of 12 percent per annum. Management wants to minimize borrowing and repay rapidly. Interest is computed and paid when the principal is repaid. Assume that borrowing takes place at the beginning, and repayment at the end, of the months in question. Money is never borrowed at the beginning and repaid at the end of the same month. Compute interest to the nearest dollar.

ASSETS AS OF
DECEMBER 31,
Cash $4,000 Accounts receivable 16,000 Inventory* 31,250 Prepaid insurance 1,200 Fixed assets, net 10,000

Total $62,450

LIABILITIES AS OF DECEMBER 31,
Accounts payable (merchandise) $28,750 Dividends payable $1,000 Rent Payable $6,000

Total $35,750

*November 30 inventory balance = $12,500

Recent and forecasted sales:

October $30,000 December $20,000 November $20,000 January $50,000 February $60,000 March $30,000 April $36,000

Required

Prepare a master budget for the following schedules identified below. Use Excel and incorporate a formula based spread sheet whenever possible. I will be altering the sales figures in your submitted Excel spread sheet to test your formulas.

Work Sheet/Template Cash Collections Schedule

	January	February	March
60% of current months sale
30% of previous months sale
10% of second previous months sale
Total collections

Purchase Budget

	December	January	February	March
Desired Ending Inventory
Cost of Goods Sold
Total Needed
Beginning Inventory
Purchases

Statement of Cash Receipts and Disbursements

	January	February	March
Cash Balance Beginning
Plus Cash Collections
=Cash Available Before Financing
Less Cash Disbursements:
Purchases
Rent
Wage and Salaries
Miscellaneous Expenses
Dividends
Purchase of Fixtures
Total Disbursements
Plus Minimum Cash Desired
Total Cash Needed
Excess (Deficiency)
Financing:
Borrowing, at the beginning of period
Repayment, at the end of period
Interest at 12% per annum
Cash Balance, end

Income Statement for the 3 months ending March 31

Sales
Less Cost of Goods Sold
=Gross Profit
Less Operating Expenses:
?
?
?
?
?
Net Income from Operations
Interest Expense
Net Income

Balance Sheet as of March 31:

Assets
Current Assets:
Cash
Accounts Receivable
Inventory
Prepaid
Fixed Assets
Total Assets
Liabilities:
Accounts Payable
Rent Payable
Dividend Payable
Shareholders’ Equity
Retained Earnings and Share Capital

Answer 1

Cash collection schedule
	Jan	Feb	March
60% of current month sales	30,000.00	36,000.00	18,000.00
	(60% of 50,000)	(60% of 60,000)	(60% of 30,000)
30% of previous month sales	6,000.00	15,000.00	18,000.00
	(dec collection)	(Jan collection)	(feb collection)
10% of second previous month sales	2,000.00	2,000.00	5,000.00
	(Nov collection)	(dec collection)	(Jan collection)
Total collections	38,000.00	53,000.00	41,000.00

Purchase Budget
	Dec	Jan	Feb	March
Desired ending inventory	5,000.00	5,000.00	5,000.00	5,000.00
(Given in question)
Cost of goods sold	10,000.00	25,000.00	30,000.00	15,000.00
(Sales/sale price per unit
multiply with purchase price per unit )
Total needed (A)	15,000.00	30,000.00	35,000.00	20,000.00
Opening stock (B)	12,500.00	5,000.00	5,000.00	5,000.00
Purchases (A-B)	2,500.00	25,000.00	30,000.00	15,000.00

Statement of cash receipts and disbursements
	Jan	Feb	March
Cash balance beginning	4,000.00	18,300.00	32,100.00
Plus cash collections	38,000.00	53,000.00	41,000.00
=Cash available before financing	42,000.00	71,300.00	73,100.00
Less cash disbursements:
Purchases	2,500.00	25,000.00	30,000.00
Rent	6,200.00	200.00	200.00
Wages and salaries	12,000.00	12,000.00	12,000.00
Misc. expenses	2,000.00	2,000.00	2,000.00
dividends	1,000.00	-	-
Purchase of fixtures	-	-	2,000.00
Total disbursements	23,700.00	39,200.00	46,200.00
Cash balance	18,300.00	32,100.00	26,900.00
minimum cash desired	4,000.00	4,000.00	4,000.00
Excess / (deficiency)	14,300.00	28,100.00	22,900.00
Financing:
Borrowing at beginning	-	-	-
Repayment at end	-	-	-
interest @12% P.A.	-	-	-
Cash balance end	18,300.00	32,100.00	26,900.00

	Income statement for 3 months ending March 31
	Sales	140,000.00
	Less: Cost of goods sold	70,000.00
	=Gross profit	70,000.00
	Less: Operating expenses
	Rent (Refer note below)	13,600.00
	Wages and salaries	36,000.00
	Misc. expenses	6,000.00
	depreciation	600.00
	Insurance	300.00
	Net income from operations	13,500.00
	Interest expense	-
	Net income	13,500.00
Note	Quarterly sales above 10000	130,000.00
	(140,000-10,0000)
	10% of it	13,000.00
	Normal rent	600.00
	(200*3)
	Total rent cost	13,600.00

Balance sheet as at March 31
Equity and Liabilities		Assets
Accounts payable	41,250.00	Cash	26,900.00
( 28750+ 70,000 -57500)
		Account receivables	24,000.00
Rent payable	13,000.00	(16000+ 140,000-132000)
Dividend payable (for march quarter)	1,000.00	Inventory	5,000.00
Shareholder equity		Excess inventory of Dec	26,250.00
Share capital and retained earnings	39,200.00	(31250-5000)
(26700+ 13500-1000)
		Prepaid	900.00
		(1200-300)
		Fixed assets	11,400.00
		(10,000-600+2000)
Total Equity and Liabilities	94,450.00	Total assets	94,450.00