Use the information provided to answer the prompt below in EXCEL with steps and formulas. Use check figures provided to confirm work. Thank you!

Use the information above to answer the prompt below in EXCEL with steps and formulas. Use check figures provided to confirm work. Thank you!

Arelatively 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 prior year December Balance Sheet. Please complete the tems requested on Worksheet Tabs Aand, of this Workbook. Check Figures are 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.0096 $ 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.0096 Planned ending inventory as a percentage of next month's required invent 70.0096 Purchases are are all on credit. Purchases paid in current month Purchases paid in next month 34.00% 66.0096 2.5096 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.0096 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 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.5096 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 detk until September of 2020 21.0096 Income Tax Rate 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 2 $12,000 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 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 Sales Commission Payable Selling and Admin Payable Advertising Payable Interest Payable Dividends Payable Income Taxes Payable Total Current Liabilities 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 2020 Budgeted January Budgeted February Budgeted March Solution: Actual Dec 2019 Desired Ending Inventory Balance for Current Month $102,960 Current Month's Cost of Sales 130,000 Inventory Needed to be Available during Current Mont 232,960 Less: Beginning Inventory of Current Month 93,600 Budgeted Purchase for Next Month Sales $139,360 Arelatively 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 prior year December Balance Sheet. Please complete the tems requested on Worksheet Tabs Aand, of this Workbook. Check Figures are 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.0096 $ 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.0096 Planned ending inventory as a percentage of next month's required invent 70.0096 Purchases are are all on credit. Purchases paid in current month Purchases paid in next month 34.00% 66.0096 2.5096 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.0096 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 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.5096 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 detk until September of 2020 21.0096 Income Tax Rate 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 2 $12,000 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 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 Sales Commission Payable Selling and Admin Payable Advertising Payable Interest Payable Dividends Payable Income Taxes Payable Total Current Liabilities 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 2020 Budgeted January Budgeted February Budgeted March Solution: Actual Dec 2019 Desired Ending Inventory Balance for Current Month $102,960 Current Month's Cost of Sales 130,000 Inventory Needed to be Available during Current Mont 232,960 Less: Beginning Inventory of Current Month 93,600 Budgeted Purchase for Next Month Sales $139,360