Question

Master Budget Project

Summer Breeze Inc. (SBI) is a retail outlet specializing in equipment and apparel for the summer months. SBI is trying to prepare a comprehensive budget for the first quarter of 2018. SBI has accumulated the following data:

Balance Sheet as of December 31, 2017			(Actual Values)
Assets			Liabilities and Equity
Cash	$ 25,000		Accounts Payable	$ 40,000
Accounts Receivable	75,000		Other Payables	28,200
Inventory	22,000		Common Stock	400,000
Fixed Assets, net	500,000		Retained Earnings	153,800
Total Assets	$622,000			$622,000

·       SBI sales are seasonal, growing through June and then declining through the rest of the year.

·       December 2017 sales were $200,000. November 2017 sales were $150,000.

·       Cost of goods sold is 76% of sales.

·       Fixed Selling, General & Administrative Expenses are $50,000 per month. Variable selling expenses (commissions) are 10% of sales. SG&A are paid in the month incurred. Commissions are paid the following month.

·       Depreciation is $60,000 annually.

·       Other Payables on the December 31, 2017 Balance Sheet includes Taxes Payable - $8,200 and Commissions Payable - $20,000.

·       The Income Tax rate is 40%. Taxes are paid the month after they are incurred.

·       SBI’s inventory policy is to maintain enough inventory to meet 20% of next month’s sales.

·       Sales are collected 70% in the current month, 20% in the month after sale and 10% in the second month after sale.

·       Purchases are paid 75% in the month of purchase and 25% in the month after purchase.

·       SBI has a line of credit with a local bank. The line of credit has a $200,000 limit and 6% rate. Borrowings are made on the last day of the month. Interest Expense is recognized the following month. Interest is paid the month after the expense is recognized.

·       SBI’s policy is to maintain a minimum cash balance of $10,000

·       SBI pays a $500 dividend to its shareholders each month.

·       Projected sales are as follows:

Sales Budget	January	February	March	April	May
Sales	$400,000	$700,000	$1,200,000	$1,250,000	$1,375,000

Use the information above to prepare SBI’s comprehensive budget for the first quarter of 2018. SBI’s budgets follow the format on the following pages. Round all balances to the nearest dollar.

Master Budget (Continued)

Summer Breeze Inc. Budget Template

Budgeted Income Statement	January	February	March	Totals
Sales
Variable Costs: Cost of Goods Sold
Commissions
Contribution Margin
Fixed S,G&A Expenses
Depreciation
Income Before Interest
Interest Expense
Income Before Tax
Tax Expense
Net Income

Purchases Budget	January	February	March	Totals
Cost of Goods Sold
Desired Ending Inventory
Total Needs
Beginning Inventory
Total Purchases

Cash Receipts Budget	January	February	March	Totals
Sales
Cash Collections:      Current Month
1 Month Prior
2 Months Prior
Total Collections
Cash Disbursements	January	February	March	Totals
Purchases
Payments for Purchases:    Current Month
1 Month Prior
SG&A Expenses Paid
Commissions Paid
Interest Paid
Taxes Paid
Dividends Paid
Total Disbursements

Master Budget (Continued)

Summer Breeze Inc. Budget Template

Cash Budget	January	February	March	Totals
Cash Receipts
Cash Disbursements
Excess/(Deficiency) of Cash
Beginning Balance
Projected Ending Balance
Borrowing/(Repayments) on Line of Credit
Ending Cash Balance

Balance Sheet as of March 31, 2018
Assets			Liabilities and Equity
Cash			Accounts Payable
Accounts Receivable			Commissions Payable
Inventory			Taxes Payable
			Interest Payable
			Notes Payable (Line of Credit)
Fixed Assets, net			Common Stock
			Retained Earnings
Total Assets			Total Liabilities & Equity

Required: Create SBI’s master budget using an Excel spreadsheet that follows the format above. Only the professionally completed budget is required. In addition, a cover page with the assignment name, your section time, team member names and team number should be included.

Answer 1

Summer Breze Inc
Cash Budget
for the quarter ending March 31, 2018
	January	February	March	Total
Beginning Cash Balance	25000	2000	22000	25000
Collections from sales	335000	590000	1020000	1945000
Total cash available for disbursement	360000	592000	1042000	1970000
Cash disbursements:
For cost of goods sold	331300	553100	841700	1726100
For S G & A Expenses	70000	90000	120000	280000
For dividend	500	500	500	1500
For income tax	8200	400	17096	25696
Total cash disbursements	410000	644000	979296	2033296
Cash surplus / deficit	-50000	-52000	62704	-63296
Minimum cash balance	10000	10000	10000	10000
Excess / (Shortfall)	-60000	-62000	52704	-73296
Financing
Borrowing	52000	74000		126000
Repayment			-60000	-60000
Interest			-260	-260
	52000	74000	-60260	65740
Eding cash balance	2000	22000	2444	2444
Summer Breze Inc
Income Statement
for the quarter ending March 31, 2018
	January	February	March	Total
Sales Revenue	400000	700000	1200000	2300000
Variable expenses:
Cost of goods sold	304000	532000	912000	1748000
Sales Commissions	40000	70000	120000	230000
Total Variable expenses	344000	602000	1032000	1978000
Contribution Margin	56000	98000	168000	322000
Fixed Expenses:
S G & A expenses:	50000	50000	50000	150000
Depreciation Expense	5000	5000	5000	15000
Total Fixed Expenses	55000	55000	55000	165000
Income before interest	1000	43000	113000	157000
Interest expense		260	630	890
Income before income tax	1000	42740	112370	156110
Income tax (40%)	400	17096	44948	62444
Net income	600	25644	67422	93666
Summer Breeze Inc.
Balance Sheet
as at March 31, 2018
Assets
Cash		2444
Accounts Receivable		430000
Inventory		190000
Total current asets		622444
Fixed Assets, net		485000
Total Assets		1107444
Liabilities and Stockholders' equity
Accounts Payable		229900
Short-term note payable		66000
Interest Payable		630
Sales commission payable		120000
Income tax payable		44948
Total Liabilities		461478
Stockholders' equity
Common stock		400000
Retained earnings
Balance as at December 31, 2017	153800
Net income for the quarter	93666
Dividends paid	-1500
Balance as at June 30		245966
Total liabilities and stockholders' equity		1107444	0

Working:

Sales Budget
	January	February	March	Total
Budgeted Sales	400000	700000	1200000	2300000
Schedule of cash collections:
	January	February	March	Total
Budgeted Sales	400000	700000	1200000	2300000
Collections from :
November sales	15000			15000
December sales	40000	20000		60000
January Sales	280000	80000	40000	400000
February Sales		490000	140000	630000
March Sales			840000	840000
Total Collections	335000	590000	1020000	1945000
Accounts Receivable	140000	250000	430000	430000
Purchase Budget
	January	February	March	Total
Budgeted Sales	400000	700000	1200000	2300000
Cost of goods sold (76% of sales)	304000	532000	912000	1748000
Add: Desired ending inventory	106400	182400	190000	190000
(20% of next month's cost of goods )
Total units required	410400	714400	1102000	1938000
Less: Beginning inventory	22000	106400	182400	22000
Budgeted Purchases	388400	608000	919600	1916000
Payment schedule for purchases
	January	February	March	Total
Budgeted purchases	388400	608000	919600	1916000
Payments for :
December purchases	40000			40000
January purchases	291300	97100		388400
February purchases		456000	152000	608000
March purchases			689700	689700
Total payment for purchases	331300	553100	841700	1726100
Accounts Payable	97100	152000	229900	229900
S G & A expense budget
	January	February	March	Total
Budgeted sales	400000	700000	1200000	2300000
Sales commission (10% of sales)	40000	70000	120000	230000
Fixed Expenses	50000	50000	50000	150000
Budgeted selling expenses	90000	120000	170000	380000
Cash payment for SG & A expenses
Fixed Expenses	50000	50000	50000	150000
Sales commisions	20000	40000	70000	130000
Total cash payments for S G & A expenses	70000	90000	120000	280000