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 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

A.

Wine to End Whining Inc.
Purchases Budget
For the three months ended March 2017
	January	February	March	Total
Cost of Goods Sold ( 60% of sales)	$ 132,000	$ 115,800	$ 106,800	$ 354,600
Add: Desired Ending Inventory ( 65 % of next month COGS)	75,270	69,420	78,390	78,390
Total Needs	207,270	185,220	185,190	432,990
Less: Beginning Inventory	85,800	75,270	69,420	85,800
Budgeted Purchases	121,470	109,950	115,770	347,190

B.

Wine To End Whining Inc.
Cash Transactions
	January	February	March
Cash In
56 % of This Month Sales	123,200	108,080	99,680
44% of Last Month Sales	88,000	96,800	84,920
Total Cash Receipts	211,200	204,880	184,600
Cash Out
70% Last Month Purchases	89,460	85,029	76,965
30% This Month Purchases	36,441	32,985	34,731
Commissions	6,000	6,600	5,790
Advertising	6,200	6,400	6,130
Selling and Administrative	33,000	33,400	32,860
Insurance	0	18,000	0
Interest	0	0	1,400
Dividends	12,000	0	0
Income Tax	14,970	0	0
Total Cash Disbursements	198,071	182,414	157,876
Cash Budget
Beginning Balance of Cash	103,200	116,329	138,795
Cash Receipts	211,200	204,880	184,600
Cash Available	314,400	321,209	323,395
Cash Disbursements	198,071	182,414	157,876
Ending Cash Balance	116,329	138,795	165,519