use the charts on the top to solve the balance sheets, show excel formulas pls!!!

1 ACC 255 BUDGET PROJECT - SPRING 20 2 Budget information: 3 Projected sales for January, 2021 128,000 4 Average Monthly percentage of sales increase: 5% 5 Average monthly salary expense: 33,400 6 Average monthly rent expense: 6.900 7 Average monthly utility expense: 5.870 8 Average monthly misc.expense: 2,650 9 Supplies expense as a percentage of sales: 29 10 Commission expense as a percentage of sales: 6% 11 Cash sales in month of sale 35% 12 Cost of goods sold-percentage of sales 40% 13 Desired ending inventory percent of next months COGS 159 14 Payments of inventory in month of purchase 809 15 Monthly depreciation for 2020 6,300 16 New equipment cost 160,000 17 Salvage value 4,000 18 Useful Life (months) 60 19 Minimum required cash balance 35,000 20 Monthly interest rate 0.03% 21 22 CHIPPEWA CHOCOLATES 23 Sales Budget 24 For the Three Months Ending March 31, 2021 25 26 January February March 27 Cash Sales $44,800 47,040 5 49,392 28 Credit Sales 83.200 87,360 91,728 29 Total Sales $ 128,000 $134,400 141,120 30 31 32 CHIPPEWA CHOCOLATES 33 Schedule of Cash Receipts 34 For the Two Months Ending February 28, 2021 35 36 January February 37 Cash Sales 44,800 47,040 38 Collection of Credit Sales (A/R) 78,000 83,200 39 Total Budgeted Cash Receipts 122.800 130,240 40 41 42 CHIPPEWA CHOCOLATES Inventory Purchases Budget 44 For the Two Months Ending February 28, 2021 45 46 January February 47 Cost of Goods Sold $ 51,200 53,760 48 Add: Desired Ending inventory 8,064 7,680 49 Inventory Needed 59,264 61,440 50 Less: Beginning Inventory 7.680 8,064 51 Budgeted Purchases $ 51,584 $ 53,376 52 53 54 CHIPPEWA CHOCOLATES 55 Schedule of Cash Payments 56 For the Two Months Ending February 28, 2021 57 58 January February 59 December Purchases: S 9,696 60 January Purchases 41,267 10,317 61 February Purchases 42,701 62 Cash paid for inventory 50.963 5 53,019 63 CHIPPEWA CHOCOLATES Selling and Administrative Expenses Budget For the Two months Ending February 28, 2021 January February 2,560 7,680 10,240 2,688 8,064 10,752 Jariable costs: Supplies Expense: Commission Expense: Total variable costs Fixed costs: Average monthly salary expens Average monthly rent expense Average monthly utility expens Average monthly misc.expens 33,400 6,900 5,870 2,650 33,400 6,900 5,870 2,650 Total fixed costs: Total S & A Expense 48,820 48.820 59,060 59,572 CHIPPEWA CHOCOLATES Schedule of Cash Payments for S&A Expenses For the Two Months Ending February 28, 2021 January February Jariable costs: Supplies Expense: 2,560 2.560 Commission Expense: 7.200 7,680 Total variable costs 9,760 10,240 Fixed costs: Average monthly salary expens 31,500 33,400 Average monthly rent expense 6,900 6,900 Average monthly utility expens 6,480 5,870 Average monthly misc.expens 2,650 2,650 Total fixed costs 47,530 48,820 Cash paid for S&A Expenses $ 57,290 59,060 CHIPPEWA CHOCOLATES Budgeted Income Statement For the Two Months Ending February 28, 2021 Sales Revenue ess: Cost of Goods Sold Gross Profit ess: S&A Expenses ncome before interest interest expense Net Income January February 128,000 134,400 51,200 53,760 76.800 80,640 59,060 59,572 17,740 21,068 $ 17,740 21.068 CHIPPEWA CHOCOLATES Cash Budget For the Two Months Ending February 28, 2021 January February $ 35,850 35,397 122.800 130.240 158,650 165,637 Beginning Cash Balance Add: Cash Reciepts Cash Available Less: Cash Paid for: Inventory S&A Expenses Equipment purchase 50,963 57,290 160.000 53,0181 59,060 G H Variance $ B E F 1 ACC 255 BUDGET PROJECT (521-9:30 Class) 2 Chippewa Chocolates Company 3 Balance Sheet 4 December 31, 2020 5 Assets Liabilities 7 Cash $ 35,850 Accounts Payable-Inventory $ 9,696 8 Accounts Receivable 78,000 Salaries Payable 31,500 9 Inventory 7,680 Utilities Payable 6,480 10 Equipment $ 400,000 Commissions Payable 7,200 11 Less: Accum. Depi 75,600 324,400 Notes Payable 12 Total Liabilities 54,876 13 Total Assets $ 445,930 14 Stockholder's Equity 15 Common Stock 300,000 16 Retained Earnings 91,054 17 Total Liabilities & Stock. Equ $ 445,930 18 19 20 21 22 Chippewa Chocolates Company 23 Balance Sheet 24 February 28, 2021 25 26 Assets Liabilities 27 Cash $ Accounts Payable-Inventory 28 Accounts Receivable Salaries payable 29 Inventory Utilities Payable 30 Equipment Commissions Payable 31 Less: Accum. Depr. Notes Payable 32 Total Liabilities 33 Total Assets 34 Stockholder's Equity 35 Common Stock 300,000 36 Retained Earnings 37 Total Liabilities & Stock. Equity 38 39 40 Variance check $