Case 2. Annual Budget and Budgeted Financial Statements

This case study provides practice in preparing an annual budget for a company, including budgeted financial statements. This case is not a group project. Instead, you are required to complete this case on your own. You must upload your submission to the Canvas website by the due date indicated in the course schedule. If you perform the calculations by hand, upload pages 3-6 of this Word file. If you perform your calculations using the Excel worksheet, upload your Excel file.

Facts

Nomad Vineyards Inc. (Nomad) is a wholesale distributor of imported wine that commenced operations in 20Y1. Ethan is the president of Nomad and has just hired you as a consultant to help him prepare monthly budgets for the upcoming year, 20Y2. For simplicity, prepare monthly budgets for only the first half of the year, January 1 through June 30, 20Y2.

Ethan has made some sales forecasts for 20Y2. He expects monthly sales to start at $400,000 in January and then grow $20,000 per month during the first half of the year, as follows:

Sales revenue, 20Y1		Budgeted sales revenue, 20Y2
October	$330,000		January	$400,000		April	$460,000
November	$355,000		February	$420,000		May	$480,000
December	$380,000		March	$440,000		June	$500,000
						July	$520,000

All inventory sales are made on account.Nomad collects 20% of its receivables in the month of the sale and 80% in the following month.Uncollectible accounts were negligible in 20Y1 and no bad debts are expected in 20Y2. Nomad's only cash receipts are from inventory sales.

All merchandise inventory purchases are made on account. Nomad pays 30% of its payables in the month of the purchase and 70% in the following month.Nomad's gross profit percentage is 40% and its cost of goods sold percentage is 60%. Nomad maintains an ending inventory balance (safety stock) equal to 50% of the cost of goods sold in the following month. Inventory purchases in December 20Y1 were $234,000.

Nomad's monthly selling and administrative (S&A) expenses include variable costs equal to 5% of sales revenue and fixed costs of $70,000. The variable S&A expenses are sales commissions, whereas the fixed S&A expenses include rent, advertising, salaries, insurance, and depreciation. All S&A expenses are paid in cash in the month the expense is incurred, except for monthly depreciation expense of $10,000 and monthly insurance expense of $3,000. Nomad paid its annual insurance premium for 20Y2 of $36,000 on December 31, 20Y1.

Ethan has established a capital expenditure budget for 20Y1. The plan is to acquire $75,000 of new property, plant and equipment (PP&E) at the start of each quarter, including January 20Y2 and April 20Y2.Nomad pays dividends semi-annually in January and July. In December 20Y1, Nomad declared a $50,000 dividend which is payable in January 20Y2. Nomad's accountant estimates that its income tax liability for 20Y2 will be $260,000 (tax rate of roughly 25%). Consequently, Nomad will make four quarterly estimated income tax payments of $65,000 each on April 15, June 15, September 15 and December 15 of 20Y2.

Nomad's balance sheet on December 31, 20Y1 is as follows:

Assets
Cash	$ 124,000
Accounts receivable [a]	304,000
Inventory	120,000
Prepaid insurance	36,000
PP&E, net of accumulated depreciation	796,000
Total assets	$1,380,000

Liabilities and Stockholders' Equity
Accounts payable [b]	$ 163,800
Dividends payable	50,000
Common stock	1,000,000
Retained earnings	166,200
Total liabilities & stockholders' equity	$1,380,000

[a]Nomad will collect the entire balance in January 20Y2

[b]Nomad will pay off the entire balance in January 20Y2

Required

Prepare monthly budgets for the first six months of 20Y2, including the following detailed schedules:

1. Sales budget and expected cash collections from inventory sales
2. Merchandise purchases budget and expected cash payments for inventory purchases
3. S&A expenses budget and expected cash payments for S&A expenses
4. Cash budget
5. Budgeted income statement for six months ending June 30, 20Y2
6. Budgeted balance sheet on June 30, 20Y2
7. Budgeted statement of cash flows for six months ending June 30, 20Y2

Templates, check figures, and hints to get started can be found on the following pages. You can perform the calculations by hand, or you can create the budget using the Excel worksheets for Case 2. Regardless of whether you perform the calculations by hand or in Excel, your solution must be in the format indicated by the templates.

Case 2. Annual Budget and Budgeted Financial Statements- Solution

1. Sales budget and expected cash collections from inventory sales, 20Y2
	January	February	March	April	May	June	6 months
Accrual-basis sales revenue	400,000	420000	440000	460,000	480,000	500,000	2,7000,000
Cash receipts from sales:
Prior month's sales	304,000	320,000	336,000	352,000	368,000	384,000	2,064,000
Current month's sales	80,000	84,000	88,000	92,000	96,000	100,000	540,000
Total cash receipts	384,000	404,000	424,000	444,000	464,000	484,000	2,604,000

2. Merchandise purchases budget and expected cash payments for inventory purchases, 20Y2
	January	February	March	April	May	June	6 months
Budgeted COGS	240,000	252,000	264,000	276,000	288,000	300,000	1,620,000
+ Desired ending inventory	126,000	132,000	138,000	144,000	150,000	156,000	156,000
Total inventory needed	366,000	384,000	402,000	420,000	438,000	456,000	1,776,000
- Beginning inventory	120,000	126,000	132,000	138,000	144,000	150,000	120,000
Required inventory purchases	246,000	258,000	270,000	282,000	294,000	306,000	1,656,000
Cash payments for:
Prior month's purchases	163,800	172,200	180,600	189,000	197,400	205,800	1,108,800
Current month's purchases	73,800	77,400	81,000	84,600	88,200	91,800	496,800
Total cash payments	237,600	249,600	261,600	273,600	285,600	297,600	1,605,600

3. S&A expenses budget and expected cash payments for S&A expenses, 20Y2
	January	February	March	April	May	June	6 months
Accrual-basis S&A expenses	400,000	420,000	440,000	460,000	480,000	500,000	2,700,000
Variable S&A expenses	20,000	21000	22000	23000	24000	25000	135,000
Fixed S&A expenses	70,000	70000	70000	70000	70000	70000	420,000
Total S&A expenses	90,000	91000	92000	93000	94000	95000	555,000
Cash payments for S&A expenses
- Depreciation expense	(10,000)	-10000	-10000	-10000	-10000	-10000	-60000
- Prepaid insurance expense	(3,000)	-3000	-3000	-3000	-3000	-3000	-18,000
Total cash payments	77,000	78000	79000	80000	81000	82000	477,000

4. Cash budget, 20Y2
	January	February	March	April	May	June	6 months
Beginning cash balance	124,000	68400	144800	228200	178600	276000	1,020,000
+ Cash receipts from sales	384,000	404000	424000	444000	464000	484000	2,604,000
- Cash payments for:
Inventory purchases step 1	(237,600)	(249600)	(261600)	(273600)	(285600)	(297600)	(1605600)
S&A expenses step 3	(77,000)	(78000)	(79000)	(80000)	(81000)	(82000)	(477000)
Capital expenditures	(75,000)	0	0	75000	0	0	150000
Dividends	(50,000)	0	0	0	0	0	50000
Income taxes	0	0	0	(65,000)	0	(65000)	(130,000)
Ending cash balance	68,400	144800	228200	178600	276000	315400	1,211,400

5. Budgeted income statement, 20Y2 (6 months)
Sales	2,700,000	Sales budget
Cost of goods sold	1,620,000	Purchases budget
Gross margin	1,080,000
Selling and administrative expenses	555,000	S&A expense budget
Operating income	525,000
Interest expense	n.a.
Income tax expense	130,000	Cash budget
Net income	395,000

6. Budgeted balance sheet, June 30, 20Y2
Assets
Cash	315,400	Cash budget
Accounts receivable	400,000	Sales budget
Inventory	156,000	Purchases budget
Prepaid insurance	18000	Jan 1 balance less insurance expense
PP&E, net of depreciation	90000	Jan 1 balance plus acquisitions less depreciation
Total assets	979,400

I need help with this Section below:

Liabilities and Stockholders' Equity
Accounts payable		Purchases budget
Common stock		Jan 1 balance
Retained earnings		Jan 1 balance plus net income
Total liabilities and stockholders' equity

7. Budgeted statement of cash flows, 20Y2 (6 months)
Cash provided by operating activities
Net income		Income statement
+ Depreciation		S&A expenses budget
- Increase in accounts receivable		Comparative balance sheets
- Increase in inventory		Comparative balance sheets
+ Decrease in prepaid insurance		Comparative balance sheets
+ Increase in accounts payable		Comparative balance sheets
Total
Cash used in investing activities
- Equipment purchases		Cash budget
Cash used in financing activities
- Dividends paid		Cash budget
Increase in cash balance

Check figures:

• Total budgeted sales revenue is $2,700,000
• Total budgeted cash collections are $2,604,000
• Total budgeted inventory purchases are $1,656,000
• Budgeted cash payments for inventory purchases are $1,605,600
• Budgeted cash balance on June 30 is $315,400
• Budgeted net income is $395,000
• Budgeted total assets on June 30 is $1,775,400
• Cash provided by operating activities is $391,400