how would you solve this problem given these tables

Figures are provided for you on each Worksheet. Dream Ice Cream, Inc. PLANNING DATA FOR 2020 $ 254,000 $ 263,000 Actual November 2019 Sales Actual December 2019 Sales Expected 2020 Monthly Sales January February March April $ 197,000 $ 148,000 $ 136,000 $ 167,000 All Sales are on Credit Collections of Accounts Receivable: Month of Service Month following Service 42.00% 58.00% Cost of Goods Sold as a percentage of selling price. 60.00% Planned ending inventory as a percentage of next month's required inventory. 65.00% Purchases are are all on credit. Actual Purchases made in December 2019 Purchases paid in current month Purchases paid in next month $ 132,060 55.00% 45.00% $3,450 Marketing expenses have a fixed and variable portion: Fixed portion The fixed portion of the marketing is paid in the current month. Variable portion as a percentage of sales The variable portion is paid one month after the month incurred. Total marketing for month. 1. 10% 2.00% Sales commission as a percent of sales Sales commission are paid two months after the month incurred. Selling and Administrative expenses have a fixed and variable portion: Fixed portion is paid in the month incurred Variable portion as a percentage of Cost of Goods Sold Variable S&A expenses are paid one month after the month incurred $23,100 1.50% 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 Insurance premium paid on February 1, 2019 $15,660 $15,000 Depreciation expense per month $5,320 4.15% The annual interest rate on the long term debt is 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 2021. $12,000 The Company's Board of Directors plans to declare a dividend on February 20. It will be paid on March 5. 21.00% Income Tax rate Accrued taxes for the three previous months are paid on the 15th of January, April, July and October 4. Use the information provided on the December 31, 2019 Balance Sheet shown 5 below to help you create the budgeting documents requested. NOO Dream Ice Cream, Inc. BALANCE SHEET December 31, 2019 ASSETS 12 Current Assets 13 Cash 14 Accounts Receivable 15 Inventory Prepaid Insurance 17 Total Current Assets 303,200 152,540 76,830 1,250 533,820 19 Property Plant and Equipment 20 Equipment 21 Accumulated Depreciation 22 Total Property Plant and Equipment 319,200 (63,840) 255,360 23 24 Total Assets $ 789,180 LIABILITIES AND SHAREHOLDERS EQUITY LIABILITIES 29 Current Liabilities 30 Accounts Payable - Purchases 31 Sales Commission Payable Selling and Administrative Payable 33 Interest Payable Marketing Payable 35 Income Taxes Payable Dividends Payable 37 Total Current Liabilities 59,427 10,340 2,367 415 2,893 15,320 90,762 39 Long Term Debt 120,000 41 Total Liabilities 210,762 SHAREHOLDERS' EQUITY 14 45 Common Stock Retained Earnings 104,000 474,418 47 Total Shareholders' Equity 578,418 49 Total Liabilities and Shareholders Equity 789,180 No 4. Based on the information available to you, complete the Purchases Budget worksheet below: U1 Check figures are: January Budgeted Purchase for Next Month Sales = $99,090; March Inventory Needed to be Available during Current Month = $146,730. mo Dream Ice Cream, Inc. PURCHASES BUDGET PLANNING FOR 2020 3 Solution: Budgeted January Budgeted February Budgeted March 5 Desired Ending Inventory Balance for Current Month 6 Current Month's Cost of Sales 7 Inventory Needed to be Available during Current Month 8 Less: Beginning Inventory of Current Month 9 Budgeted Purchase for Next Month Sales Actual Dec 2019 $ 76,830 157,800 234,630 102,570 $ 132,060 Figures are provided for you on each Worksheet. Dream Ice Cream, Inc. PLANNING DATA FOR 2020 $ 254,000 $ 263,000 Actual November 2019 Sales Actual December 2019 Sales Expected 2020 Monthly Sales January February March April $ 197,000 $ 148,000 $ 136,000 $ 167,000 All Sales are on Credit Collections of Accounts Receivable: Month of Service Month following Service 42.00% 58.00% Cost of Goods Sold as a percentage of selling price. 60.00% Planned ending inventory as a percentage of next month's required inventory. 65.00% Purchases are are all on credit. Actual Purchases made in December 2019 Purchases paid in current month Purchases paid in next month $ 132,060 55.00% 45.00% $3,450 Marketing expenses have a fixed and variable portion: Fixed portion The fixed portion of the marketing is paid in the current month. Variable portion as a percentage of sales The variable portion is paid one month after the month incurred. Total marketing for month. 1. 10% 2.00% Sales commission as a percent of sales Sales commission are paid two months after the month incurred. Selling and Administrative expenses have a fixed and variable portion: Fixed portion is paid in the month incurred Variable portion as a percentage of Cost of Goods Sold Variable S&A expenses are paid one month after the month incurred $23,100 1.50% 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 Insurance premium paid on February 1, 2019 $15,660 $15,000 Depreciation expense per month $5,320 4.15% The annual interest rate on the long term debt is 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 2021. $12,000 The Company's Board of Directors plans to declare a dividend on February 20. It will be paid on March 5. 21.00% Income Tax rate Accrued taxes for the three previous months are paid on the 15th of January, April, July and October 4. Use the information provided on the December 31, 2019 Balance Sheet shown 5 below to help you create the budgeting documents requested. NOO Dream Ice Cream, Inc. BALANCE SHEET December 31, 2019 ASSETS 12 Current Assets 13 Cash 14 Accounts Receivable 15 Inventory Prepaid Insurance 17 Total Current Assets 303,200 152,540 76,830 1,250 533,820 19 Property Plant and Equipment 20 Equipment 21 Accumulated Depreciation 22 Total Property Plant and Equipment 319,200 (63,840) 255,360 23 24 Total Assets $ 789,180 LIABILITIES AND SHAREHOLDERS EQUITY LIABILITIES 29 Current Liabilities 30 Accounts Payable - Purchases 31 Sales Commission Payable Selling and Administrative Payable 33 Interest Payable Marketing Payable 35 Income Taxes Payable Dividends Payable 37 Total Current Liabilities 59,427 10,340 2,367 415 2,893 15,320 90,762 39 Long Term Debt 120,000 41 Total Liabilities 210,762 SHAREHOLDERS' EQUITY 14 45 Common Stock Retained Earnings 104,000 474,418 47 Total Shareholders' Equity 578,418 49 Total Liabilities and Shareholders Equity 789,180 No 4. Based on the information available to you, complete the Purchases Budget worksheet below: U1 Check figures are: January Budgeted Purchase for Next Month Sales = $99,090; March Inventory Needed to be Available during Current Month = $146,730. mo Dream Ice Cream, Inc. PURCHASES BUDGET PLANNING FOR 2020 3 Solution: Budgeted January Budgeted February Budgeted March 5 Desired Ending Inventory Balance for Current Month 6 Current Month's Cost of Sales 7 Inventory Needed to be Available during Current Month 8 Less: Beginning Inventory of Current Month 9 Budgeted Purchase for Next Month Sales Actual Dec 2019 $ 76,830 157,800 234,630 102,570 $ 132,060