Accounting heads, please help! The check numbers in yellow are not matching. Please use the information provided to help me resolve it in EXCEL. Please show me the formulas/work on the solution.

USE COMPLETED DATA BELOW FOR REFERENCE TO PROBLEM ABOVE

Check figures are: January Total Assets = $538,362; February Total Liabilities = $261,800; March Total Current Assets = $330.718. ASSETS Dec 31, 2018 Jan 31, 2019 Feb 28,2019Mar 31, 2019 Current Assets Cash Accounts Receivable Inventory Prepaid Insurance $ 103,200 92,000 95,550 1,600 $ 152,047 96,600 87,815 $ 151,253 88,780 85,540 18,000 $ 171,083 188,000 91,455 16,500 713/ Oy Assets 292,350 336,462 343,573 467,038 Property Plant and Equipment Office Equipment Accumulated Depreciation 7273/F22 FarmaVJE VIX 300,000 (60,000) 240,000 300,000 (65,000) 235,000 300,000 (70,000) 230,000 300,000 (75,000) 225,000 773/ ses $ 532,350 $ 571,462 $ 573,573 $ 692,038 LIABILITIES AND SHAREHOLDERS EQUITY LIABILITIES Current Liabilities Accounts Payable - Purchases $ 88,803 88,803 84,985 Sales Commission Payable 5,000 5,000 5,250 Selling and Admin Payable 4,000 33,200 Advertising Payable 2,000 6,300 Interest Payable 650 1,250 1,850 Dividends Payable 12,000 Income Taxes Payable 12,970 4,526 3,493 81,296 4,825 32,860 2,450 3,184 7273/77 Z/avies 113,423 111,579 135,078 124,614 Long Term Debt 160,000 160,000 160,000 160,000 72713/ Lietvlives 273,423 271,579 295,078 284,614 SHAREHOLDERS' EQUITY Common Stock Retained Earnings 124,000 134,927 124,000 139,952 124,000 153,093 124,000 165,070 7273/Survdex'Ezy 258,927 263,952 277,093 289,070 T.3/Liatvlives 3120 9 $ 532,350 $ 535,530 $ 572,171 $ 573,684 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 2019: 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, 2018 Balance Sheet. Please complete the four items requested on Worksheet Tabs A, B, C, and D of this Workbook. Check Figures are provided for you on each WINE TO END WHINING, INC. PLANNING DATA FOR 2019 Dec. January February March April All Sales are on Credit Collections of Accounts Receivable: Month of Service Month following Service 54.00% 46.00% $ 113,400 $ 104,220 $ 101,520 $ 108,540 $96,600 $88,780 $86,480 $92,460 $ 92,000 March April 188,000 $ 201,000 Actual December 2018 Sales Expected Sales in Dollars January February March April $ 200,000 December January February $ 210,000 $200,000 $ 210,000 $ 193,000 $ S 193,000 $ 188,000 $ 201,000 Cost of Goods Sold a percentage of selling price. 65.00% 136,500 125,450 122,200 130,650 Planned ending inventory as a percentage of next month's required inventory. 70.00% 87,815 85,540 91,455 Purchases are are all on credit. Purchases paid in current month Purchases paid in next month 34.00% 66.00% 68,000 132,000 2.50% $ 5,000 $ 5,250 $ 4,825 $ 4,700 $ 5,025 Sales commission as a percent of sales Sales commission are paid one month after the month incurred. $4,200 $4,200 $4,200 $4,200 $4,200 $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% $ $ 2,000 $ 6,200 $ 2,100 $ 6,300 $ 1,930 $ 6,130 $ 1,880 $ 6,080 $ 2,010 6,210 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, 2019 is The premium will be charged to prepaid insurance $18,000 $1,500 /Month 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% $29,000 $ $ 4,000.00 $ $33,000 $ 29,000 $ 29,000 $ 4,200 $ 3,860 $ 33,200 $ 32,860 $ 29,000 $ 29,000 3,760 $ 4,020 32,760 $ 33,020 Depreciation expense per month $5,000 Annual 7,200 Monthly Cash in March 600 1800 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 2019 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. Use the information provided on the December 31, 2019 Balance Sheet shown below to help you create the budgeting documents 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 Based on the information available to you, complete the Purchases Budget worksheet below: Check figures are: January Budgeted Purchase for Next Month Sales = $128,765; March Inventory Needed to be Available during Current Month = $213,655. WINE TO END WHINING, INC. PURCHASES BUDGET PLANNING FOR 2019 Solution: Desired Ending Inventory Balance for Current Month Current Month's Cost of Sales Inventory Needed to be Available during Current Month Less: Beginning Inventory of Current Month Budgeted Purchase for Next Month Sales Actual Dec 2019 $ 95,550 130,000 225,550 91,000 $134,550 Budgeted January $ 87,815 136,500 224,315 95,550 $128,765 Budgeted February $ 85,540 125,450 210,990 87,815 $ 123,175 Budgeted March $ 91,455 122,200 213,655 85,540 $ 128,115 Based on the information available to you, complete the Cash Budget worksheet below: Check figures are: January Cash Receipts = $ 205,400; February Cash Disbursements = $201,614; March Ending Cash Balance = $137,883. Solution: WINE TO END WHINING, INC. PLANNED CASH TRANSACTIONS and CASH BUDGET For the Month Ending, Jan 31, 2020 308,600 Feb 28, 2020 352,867 Mar 31, 2020 341,553 Cash Available Cash Disbursements 156,553 201,614 170,470 Ending Cash Balance $ 152,047 $ 151,253 $ 171,083 BUDGETED INCOME STATEMENTS For the Month Ending, Sales Cost of Sales Gross Margin Jan 31, 2020 $ 210,000 136,500 73,500 Feb 28,2020 $ 193,000 125,450 67,550 Mar 31, 2020 $ 188,000 122,200 65,800 Other Operating Expenses Commission Expense Advertising Expense Insurance Expense Selling & Admin. Expense Depreciation Expense Total Other Operating Expenses 5,250 6,300 1,600 33,200 5,000 51,350 4,825 6,130 1,500 32,860 5,000 50,315 4,700 6,080 1,500 32,760 5,000 50,040 Operating Income 22,150 17,235 15,760 Other Income (Expense) Interest Expense 600 600 600 Income Before Income Tax 21,550 16,635 15,160 Income Tax Expense 4,526 3,493 3,184 Net Income $ 17,025 $ 13,142 $ 11,976 Check figures are: January Total Assets = $538,362; February Total Liabilities = $261,800; March Total Current Assets = $330.718. ASSETS Dec 31, 2018 Jan 31, 2019 Feb 28,2019Mar 31, 2019 Current Assets Cash Accounts Receivable Inventory Prepaid Insurance $ 103,200 92,000 95,550 1,600 $ 152,047 96,600 87,815 $ 151,253 88,780 85,540 18,000 $ 171,083 188,000 91,455 16,500 713/ Oy Assets 292,350 336,462 343,573 467,038 Property Plant and Equipment Office Equipment Accumulated Depreciation 7273/F22 FarmaVJE VIX 300,000 (60,000) 240,000 300,000 (65,000) 235,000 300,000 (70,000) 230,000 300,000 (75,000) 225,000 773/ ses $ 532,350 $ 571,462 $ 573,573 $ 692,038 LIABILITIES AND SHAREHOLDERS EQUITY LIABILITIES Current Liabilities Accounts Payable - Purchases $ 88,803 88,803 84,985 Sales Commission Payable 5,000 5,000 5,250 Selling and Admin Payable 4,000 33,200 Advertising Payable 2,000 6,300 Interest Payable 650 1,250 1,850 Dividends Payable 12,000 Income Taxes Payable 12,970 4,526 3,493 81,296 4,825 32,860 2,450 3,184 7273/77 Z/avies 113,423 111,579 135,078 124,614 Long Term Debt 160,000 160,000 160,000 160,000 72713/ Lietvlives 273,423 271,579 295,078 284,614 SHAREHOLDERS' EQUITY Common Stock Retained Earnings 124,000 134,927 124,000 139,952 124,000 153,093 124,000 165,070 7273/Survdex'Ezy 258,927 263,952 277,093 289,070 T.3/Liatvlives 3120 9 $ 532,350 $ 535,530 $ 572,171 $ 573,684 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 2019: 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, 2018 Balance Sheet. Please complete the four items requested on Worksheet Tabs A, B, C, and D of this Workbook. Check Figures are provided for you on each WINE TO END WHINING, INC. PLANNING DATA FOR 2019 Dec. January February March April All Sales are on Credit Collections of Accounts Receivable: Month of Service Month following Service 54.00% 46.00% $ 113,400 $ 104,220 $ 101,520 $ 108,540 $96,600 $88,780 $86,480 $92,460 $ 92,000 March April 188,000 $ 201,000 Actual December 2018 Sales Expected Sales in Dollars January February March April $ 200,000 December January February $ 210,000 $200,000 $ 210,000 $ 193,000 $ S 193,000 $ 188,000 $ 201,000 Cost of Goods Sold a percentage of selling price. 65.00% 136,500 125,450 122,200 130,650 Planned ending inventory as a percentage of next month's required inventory. 70.00% 87,815 85,540 91,455 Purchases are are all on credit. Purchases paid in current month Purchases paid in next month 34.00% 66.00% 68,000 132,000 2.50% $ 5,000 $ 5,250 $ 4,825 $ 4,700 $ 5,025 Sales commission as a percent of sales Sales commission are paid one month after the month incurred. $4,200 $4,200 $4,200 $4,200 $4,200 $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% $ $ 2,000 $ 6,200 $ 2,100 $ 6,300 $ 1,930 $ 6,130 $ 1,880 $ 6,080 $ 2,010 6,210 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, 2019 is The premium will be charged to prepaid insurance $18,000 $1,500 /Month 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% $29,000 $ $ 4,000.00 $ $33,000 $ 29,000 $ 29,000 $ 4,200 $ 3,860 $ 33,200 $ 32,860 $ 29,000 $ 29,000 3,760 $ 4,020 32,760 $ 33,020 Depreciation expense per month $5,000 Annual 7,200 Monthly Cash in March 600 1800 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 2019 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. Use the information provided on the December 31, 2019 Balance Sheet shown below to help you create the budgeting documents 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 Based on the information available to you, complete the Purchases Budget worksheet below: Check figures are: January Budgeted Purchase for Next Month Sales = $128,765; March Inventory Needed to be Available during Current Month = $213,655. WINE TO END WHINING, INC. PURCHASES BUDGET PLANNING FOR 2019 Solution: Desired Ending Inventory Balance for Current Month Current Month's Cost of Sales Inventory Needed to be Available during Current Month Less: Beginning Inventory of Current Month Budgeted Purchase for Next Month Sales Actual Dec 2019 $ 95,550 130,000 225,550 91,000 $134,550 Budgeted January $ 87,815 136,500 224,315 95,550 $128,765 Budgeted February $ 85,540 125,450 210,990 87,815 $ 123,175 Budgeted March $ 91,455 122,200 213,655 85,540 $ 128,115 Based on the information available to you, complete the Cash Budget worksheet below: Check figures are: January Cash Receipts = $ 205,400; February Cash Disbursements = $201,614; March Ending Cash Balance = $137,883. Solution: WINE TO END WHINING, INC. PLANNED CASH TRANSACTIONS and CASH BUDGET For the Month Ending, Jan 31, 2020 308,600 Feb 28, 2020 352,867 Mar 31, 2020 341,553 Cash Available Cash Disbursements 156,553 201,614 170,470 Ending Cash Balance $ 152,047 $ 151,253 $ 171,083 BUDGETED INCOME STATEMENTS For the Month Ending, Sales Cost of Sales Gross Margin Jan 31, 2020 $ 210,000 136,500 73,500 Feb 28,2020 $ 193,000 125,450 67,550 Mar 31, 2020 $ 188,000 122,200 65,800 Other Operating Expenses Commission Expense Advertising Expense Insurance Expense Selling & Admin. Expense Depreciation Expense Total Other Operating Expenses 5,250 6,300 1,600 33,200 5,000 51,350 4,825 6,130 1,500 32,860 5,000 50,315 4,700 6,080 1,500 32,760 5,000 50,040 Operating Income 22,150 17,235 15,760 Other Income (Expense) Interest Expense 600 600 600 Income Before Income Tax 21,550 16,635 15,160 Income Tax Expense 4,526 3,493 3,184 Net Income $ 17,025 $ 13,142 $ 11,976