Question

A relatively new company, Wine To End Whining, Inc., has determined they need to engage in a budgeting process as part of their new strategic plan. The Company has asked you to provide the following items for the months of January, February, and March of 2017: A) a Purchases Budget; B) a Cash Budget; C) a Budgeted Income Statement; and D) a Budgeted Balance Sheet. They have provided you with the planning document below and also, with their December 31, 2016 Balance Sheet. Please complete the four items requested on Worksheet Tabs A, B, C, and D of this Workbook.

Use the information provided on the December 31, 2016 Balance Sheet shown below to help you create the budgeting documents requested. WINE TO END WHINING, INC. PLANNING DATA FOR 2017 All Sales are on Credit       Collections of Accounts Receivable: Month of Service 56% Month following Service 44% Actual December 2016 Sales $   200,000 Expected Sales in Dollars January $   220,000 February $   193,000 March $   178,000       April $   201,000 Cost of Goods Sold as a percentage of selling price. 60% Planned ending inventory as a percentage of next month's required inventory. 65% Purchases are are all on credit. Purchases paid in current month 30% Purchases paid in next month 70% Sales commission as a percent of sales 3% Sales commission are paid one month after the month incurred. Advertising expenses have a fixed and variable portion: Fixed portion $4,200 The fixed portion of the advertising is paid in the current month. Variable portion as a percentage of sales 1% The variable portion is paid one month after the month incurred. Insurance policy: Months of insurance coverage paid for 12 The policy period is from February 1 to January 31 each year The premium to be paid on February 1, 2017 is $18,000 The premium will be charged to prepaid insurance Selling and Administrative expenses have a fixed and variable portion: Fixed portion is paid in the month incurred $29,000 Variable portion as a percentage of sales 2% Variable S&A expenses are paid one month after the month incurred Depreciation expense per month $5,000 The annual interest rate on the long term debt is 3.5% Interest is incurred on the principal balance outstanding each month. Interest is paid on a quarterly basis. Interest for December to February will be paid March 1. No principal payments are scheduled to be made on the long-term debt until September of 2017 Income Tax rate 30% Accrued taxes are paid on the 15th of January, April, July and October The Company's Board of Directors plans to declare a dividend on January 20 $12,000 It will be paid on February 15.
WINE TO END WHINING, INC.
BALANCE SHEET
December 31, 2016
ASSETS
Current Assets
Cash	$       103,200
Accounts Receivable	88,000
Inventory	85,800
Prepaid Insurance	1,500
Total Current Assets	278,500
Property Plant and Equipment
Office Equipment	300,000
Accumulated Depreciation	(60,000)
Total Property Plant and Equipment	240,000
Total Assets	$       518,500
LIABILITIES AND SHAREHOLDERS EQUITY
LIABILITIES
Current Liabilities
Accounts Payable - Purchases	$         89,460
Sales Commission Payable	6,000
Selling and Admin Payable	4,000
Advertising Payable	2,000
Interest Payable	467
Dividends Payable	-
Income Taxes Payable	14,970
Total Current Liabilities	116,897
Long Term Debt	160,000
Total Liabilities	276,897
SHAREHOLDERS' EQUITY
Common Stock	124,000
Retained Earnings	117,603
Total Shareholders' Equity	241,603
Total Liabilities and Shareholders Equity	$       518,500

Solution:	Actual		Budgeted		Budgeted		Budgeted
	Dec 2016		January		February		March
Desired Ending Inventory Balance for Current Month	$   85,800
Current Month's Cost of Sales	120,000
Inventory Needed to be Available during Current Month	205,800
Less: Beginning Inventory of Current Month	78,000
Budgeted Purchase for Next Month Sales	$ 127,800

WINE TO END WHINING, INC.
PLANNED CASH TRANSACTIONS and CASH BUDGET
For the Month Ending,
	Jan 31, 2017		Feb 28, 2017		Mar 31, 2017
CASH IN
56% This Month's Sales
44% of Last Month's Sales
Total Cash Receipts
CASH OUT
70% Last Month's Purchases
30% This Month's Purchases
Commissions
Advertising
Selling & Admin.
Insurance
Interest
Dividends
Income Tax
Total Cash Disbursements
CASH BUDGET
Beginning Balance of Cash
Cash Receipts
Cash Available
Cash Disbursements
Ending Cash Balance

Answer 1

Solution:	Actual	Budgeted	Budgeted	Budgeted
	Dec 2016	January	February	March
Desired Ending Inventory Balance for Current Month	85800	75270	69420	78390
Current Month's Cost of Sales	120000	132000	115800	106800
Inventory Needed to be Available during Current Month	205800	207270	185220	185190
Less: Beginning Inventory of Current Month	78000	85800	75270	69420
Budgeted Purchase for Next Month Sales	127800	121470	109950	115770
WINE TO END WHINING, INC.
PLANNED CASH TRANSACTIONS and CASH BUDGET
For the Month Ending,
	Jan 31, 2017	Feb 28, 2017	Mar 31, 2017
CASH IN
56% This Month's Sales	123200	108080	99680
44% of Last Month's Sales	88000	96800	84920
Total Cash Receipts	211200	204880	184600
CASH OUT
70% Last Month's Purchases	89460	85029	76965
30% This Month's Purchases	36441	32985	34731
Commissions	6000	6600	5790
Advertising	6200	6400	6130
Selling & Admin.	33000	33400	32860
Insurance		18000
Interest			1400
Dividends
Income Tax	14970
Total Cash Disbursements	186071	182414	157876
CASH BUDGET
Beginning Balance of Cash	103200	128329	150795
Cash Receipts	211200	204880	184600
Cash Available	314400	333209	335395
Cash Disbursements	186071	182414	157876
Ending Cash Balance	128329	150795	177519