Need help with answers in EXCEL formula!

B D 1 Excel formulas must be used. Please be sure to submit your file as an Excel file. Any submissions in 2 numbers format will receive a 0. 3 4 MASTER Student Name -> 5 Section Number => 6 7 A B D E 9 10 11 12 13 14 15 16 17 18 19 20 21 1,500 1,000 1,600 1,400 1,500 1,200 22 $8,000 107,800 52,800 130,000 $298,600 ER $74,800 223,800 6 Data Section: 7 8 Actual and Budgeted Unit Sales: 9 April 10 May 11 June 12 July 13 August 14 September 15 16 Balance Sheet, May 31, 1935 17 Cash 18 Accounts receivable 19 Merchandise inventory 20 Fixed assets (net) 21 22 Total assets 23 24 Accounts payable (merchandise) 25 Owner's equity 26 27 Total liabilities & equity 28 29 30 Average selling price 31 Average purchase cost per unit 32 Desired ending inventory 33 (% of next month's unit sales) 34 Collections from customers: 35 Collected in month of sale 36 Collected in month after sale 37 Collected two months after sale 38 Projected cash payments: 39 Variable expenses 40 Fixed expenses (per month) 41 Depreciation per month 42 43 44 Answer Section: 45 Sales Budget MASTER $298,600 BESSERE 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 $98 $55 80% 20% 60% 20% 30% of sales $10,000 $1,000 C94 4 fx B D E E June July August FORMULA1 FORMULA2 OO 0 0 8mm June July August nin FORMULA3 FORMULA Oo 0 FORMULAS FORMULA 0 0 o FORMULA7 0 0 0 FORMULA @ $0 $0 HERE 47 4B 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 62 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 92 94 44 Answer Section: 45 Sales Budget 46 47 48 49 Units 50 Dollars 51 52 Unit Purchases Budget 53 54 55 56 Desired ending inventory 57 Current month's unit sales 58 59 Total units needed 60 Beginning inventory 61 62 Purchases (units) 63 64 Purchases (dollars) 65 66 67 Cash Budget 68 69 70 71 Cash balance, beginning 72 Cash receipts: 73 Collections from customers: 74 From April sales 75 From May sales 76 From June sales 77 From July sales 78 From August sales 79 80 Total cash available 81 Cash disbursements: 82 Merchandise 83 Variable expenses 84 Fixed expenses 85 Interest paid 86 87 Total disbursements 88 89 Cash balance before financing 90 Less: Desired ending balance 91 June July August NOWN $8,000 $42,760 $142,720 29,400 58,800 31,360 19,600 94,080 27,440 31,360 82,320 29,400 $127,560 $183,880 $285,800 $74,800 FORMULAB 0 41,160 10,000 0 0 0 $0 44,100 10,000 0 $84,800 $41,160 $54,100 $42,760 $142,720 $231,700 8,000 8,000 8,000 MASTER C D E 8,000 8,000 8,000 $34,760 $134,720 $223,700 $0 0 $0 0 $0 0 - $0 $ $0 $0 0 $42,760 $142,720 $231,700 SESE FORMULA 16 FORMULA17 FORMULA18 B 93 90 Less: Desired ending balance 94 91 95 92 Excess (deficit) of cash over needs 96 93 97 94 Financing 98 95 Borrowing 99 96 Repayment 100 97 101 98 Total effects of financing 102 99 103 100 Cash balance, ending 104 101 105 102 106 103 Forecasted Income Statement 107 104 For Quarter Ended August 31, 1935 108 105 109 106 Sales 110 107 Cost of goods sold 111 108 112 109 Gross profit 113 110 114 111 Expenses: 115 112 Variable expenses 116 113 Fixed expenses 117 114 Depreciation expense 118 115 Interest expense 119 116 120 117 Total expenses 121 118 122 119 Net income 123 120 124 121 125 122 Forecasted Balance Sheet 126 123 August 31, 1935 127 124 128 125 Assets: 129 126 Cash 130 127 Accounts receivable 131 128 Merchandise inventory 132 129 Fixed assets (net) 133 130 134 131 Total assets 135 132 136 133 Liabilities & equity 137 134 Accounts payable 138 135 Loans payable 139 136 Owner's equity 140 137 FORMULA19 FORMULA20 FORMULA21 FORMULA22 $0 $0 FORMULA23 FORMULA24 FORMULA25 FORMULA26 SO FORMULA27 0 FORMULA28 MASTER + Ready 5 Section Number => 6 7 A B D E 9 10 11 12 13 14 15 16 17 18 19 20 21 1,500 1,000 1,600 1,400 1,500 1,200 22 $8,000 107,800 52,800 130,000 $298,600 ER $74,800 223,800 6 Data Section: 7 8 Actual and Budgeted Unit Sales: 9 April 10 May 11 June 12 July 13 August 14 September 15 16 Balance Sheet, May 31, 1935 17 Cash 18 Accounts receivable 19 Merchandise inventory 20 Fixed assets (net) 21 22 Total assets 23 24 Accounts payable (merchandise) 25 Owner's equity 26 27 Total liabilities & equity 28 29 30 Average selling price 31 Average purchase cost per unit 32 Desired ending inventory 33 (% of next month's unit sales) 34 Collections from customers: 35 Collected in month of sale 36 Collected in month after sale 37 Collected two months after sale 38 Projected cash payments: 39 Variable expenses 40 Fixed expenses (per month) 41 Depreciation per month 42 43 44 Answer Section: 45 Sales Budget MASTER $298,600 BESSERE 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 $98 $55 80% 20% 60% 20% 30% of sales $10,000 $1,000 C94 4 fx B D E E June July August FORMULA1 FORMULA2 OO 0 0 8mm June July August nin FORMULA3 FORMULA Oo 0 FORMULAS FORMULA 0 0 o FORMULA7 0 0 0 FORMULA @ $0 $0 HERE 47 4B 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 62 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 92 94 44 Answer Section: 45 Sales Budget 46 47 48 49 Units 50 Dollars 51 52 Unit Purchases Budget 53 54 55 56 Desired ending inventory 57 Current month's unit sales 58 59 Total units needed 60 Beginning inventory 61 62 Purchases (units) 63 64 Purchases (dollars) 65 66 67 Cash Budget 68 69 70 71 Cash balance, beginning 72 Cash receipts: 73 Collections from customers: 74 From April sales 75 From May sales 76 From June sales 77 From July sales 78 From August sales 79 80 Total cash available 81 Cash disbursements: 82 Merchandise 83 Variable expenses 84 Fixed expenses 85 Interest paid 86 87 Total disbursements 88 89 Cash balance before financing 90 Less: Desired ending balance 91 June July August NOWN $8,000 $42,760 $142,720 29,400 58,800 31,360 19,600 94,080 27,440 31,360 82,320 29,400 $127,560 $183,880 $285,800 $74,800 FORMULAB 0 41,160 10,000 0 0 0 $0 44,100 10,000 0 $84,800 $41,160 $54,100 $42,760 $142,720 $231,700 8,000 8,000 8,000 MASTER C D E 8,000 8,000 8,000 $34,760 $134,720 $223,700 $0 0 $0 0 $0 0 - $0 $ $0 $0 0 $42,760 $142,720 $231,700 SESE FORMULA 16 FORMULA17 FORMULA18 B 93 90 Less: Desired ending balance 94 91 95 92 Excess (deficit) of cash over needs 96 93 97 94 Financing 98 95 Borrowing 99 96 Repayment 100 97 101 98 Total effects of financing 102 99 103 100 Cash balance, ending 104 101 105 102 106 103 Forecasted Income Statement 107 104 For Quarter Ended August 31, 1935 108 105 109 106 Sales 110 107 Cost of goods sold 111 108 112 109 Gross profit 113 110 114 111 Expenses: 115 112 Variable expenses 116 113 Fixed expenses 117 114 Depreciation expense 118 115 Interest expense 119 116 120 117 Total expenses 121 118 122 119 Net income 123 120 124 121 125 122 Forecasted Balance Sheet 126 123 August 31, 1935 127 124 128 125 Assets: 129 126 Cash 130 127 Accounts receivable 131 128 Merchandise inventory 132 129 Fixed assets (net) 133 130 134 131 Total assets 135 132 136 133 Liabilities & equity 137 134 Accounts payable 138 135 Loans payable 139 136 Owner's equity 140 137 FORMULA19 FORMULA20 FORMULA21 FORMULA22 $0 $0 FORMULA23 FORMULA24 FORMULA25 FORMULA26 SO FORMULA27 0 FORMULA28 MASTER + Ready