Please use the information provided above to complete this problem in EXCEL. Please use the check figures to confirm work and illustrate your formula/work being done.

WINE TO END WHINING, INC. BALANCE SHEET December 31, 2019 ASSETS Current Assets Cash Accounts Receivable Inventory Prepaid Insurance Total Current Assets 103,200 92,000 95,550 1,600 292,350 Property Plant and Equipment Office Equipment Accumulated Depreciation Total Property Plant and Equipment 300,000 (60,000) 240,000 Total Assets 532,350 LIABILITIES AND SHAREHOLDERS EQUITY LIABILITIES Current Liabilities Accounts Payable - Purchases Sales Commission Payable Selling and Admin Payable Advertising Payable Interest Payable Dividends Payable Income Taxes Payable Total Current Liabilities 88,803 5,000 4,000 2,000 650 12,970 113,423 Long Term Debt 160,000 Total Liabilities 273,423 SHAREHOLDERS' EQUITY Common Stock Retained Earnings 124,000 134,927 Total Shareholders' Equity 258,927 Total Liabilities and Shareholders Equity 532,350 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: 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 prior year Balance Sheet. Please complete the four items requested on Worksheet Tabs Cand D of this Workbook. Check Figures are provided for you on each Worksheet. WINE TO END WHINING, INC. PLANNING DATA FOR 2020 All Sales are on Credit Collections of Accounts Receivable: Month of Service Month following Service 54.00% 46.00% $ 200,000 Actual December 2018 Sales Expected Sales in Dollars January February March April $ 210,000 $ 193,000 $ 188,000 $ 201,000 Cost of Goods Sold as a percentage of selling price. 65.00% Planned ending inventory as a percentage of next month's required inventory 70.00% Purchases are are all on credit. Purchases paid in current month Purchases paid in next month 34.00% 66.00% 2.50% Sales commission as a percent of sales Sales commission are paid one month after the month incurred. $4,200 Advertising expenses have a fixed and variable portion: Fixed portion The fixed portion of the advertising is paid in the current month. Variable portion as a percentage of sales The variable portion is paid one month after the month incurred. 1.00% 12 Insurance policy: Months of insurance coverage paid for The policy period is from February 1 to January 31 each year The premium to be paid on February 1, 2020 is The premium will be charged to prepaid insurance $18,000 Selling and Administrative expenses have a fixed and variable portion: Fixed portion is paid in the month incurred Variable portion as a percentage of sales Variable S&A expenses are paid one month after the month incurred $29,000 2.00% Depreciation expense per month $5,000 The annual interest rate on the long term debt is 4.50% 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 2020 21.00% Income Tax Rate Accrued taxes are paid on the 15th of January, April, July and October $12,000 The Company's Board of Directors plans to declare a dividend on January 20 It will be paid on February 15. Based on the information available to you, complete the Budgeted Income Statement worksheet below: Check figures are: January Budgeted Gross Margin = $73,500; February Budgeted Operating Income = $17,235; March Budgeted Net Income = $11,976. Solution: WINE TO END WHINING, INC. BUDGETED INCOME STATEMENTS For the Month Ending, Jan 31, 2020 Feb 28,2020 Mar 31, 2020 Sales Cost of Sales Gross Margin Other Operating Expenses Commission Expense Advertising Expense Insurance Expense Selling & Admin. Expense Depreciation Expense Total Other Operating Expenses Operating Income Other Income (Expense) Interest Expense Uncome Before Income Tax Income Tax Expense Net Income