Module 7 Excel Spreadsheet Instructions Below you will see three sets of inputs. After inputting all of your formulas, you should be able to use any of these sets of data and have the answers automatically update. For this assignment, please choose one of the data sets above and input all of the necessary formulas to find the answers. Once you are done, choose a different data set, enter it into your spreadsheet, and check the updated answers to ensure that everything is flowing through the formulas appropriately. A check answer for each one has been provided. Data set #1 Inputs: Data Section: Actual and Budgeted Unit Sales: April May June July August September 1,500 1,000 1,600 1,400 1,500 1,200 $8.000 107,800 52,800 130,000 Balance Sheet, May 31, 1935 Cash Accounts receivable Merchandise inventory Fixed assets (net) Total assets Accounts payable (merchandise) Owner's equity Total liabilities & equity $298,600 $74,800 223,800 $298,600 $98 $55 50% Average selling price Average purchase cost per unit Desired ending inventory (% of next month's unit sales) Collections from customers: Collected in month of sale Collected in month after sale Collected two months after sale Projected cash payments: Variable expenses Fixed expenses (per month) Depreciation per month 20% 60% 20% 30% of sales $10,000 $1,000 Check figure (Total liabilities & equity): $324,357 F G 000 YOUN c 11 000 NNNNNNNN A B D E 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 8 9 6 Data Section: 10 7 11 8 Actual and Budgeted Unit Sales: 12 9 April 1,500 13 10 May 1,000 14 June 1,600 15 12 July 1,400 16 13 August 1,500 17 14 September 1,200 18 15 19 16 Balance Sheet, May 31, 1985 20 17 Cash $8,000 21 18 Accounts receivable 107,800 22 19 Merchandise inventory 52,800 23 20 Fixed assets (net) 130,000 24 21 25 22 Total assets $298,600 26 23 27 24 Accounts payable (merchandise) $74,800 28 25 Owner's equity 223,800 29 26 30 27 Total liabilities & equity $298,600 31 28 32 29 33 30 Average selling price 34 31 Average purchase cost per unit $55 35 32 Desired ending inventory 36 33 (% of next month's unit sales) 60% 37 34 Collections from customers: 35 Collected in month of sale 20% 39 36 Collected in month after sale 60% 40 37 Collected two months after sale 20% 41 38 Projected cash payments: 42 Variable expenses 30% of sales 43 40 Fixed expenses (per month) $10,000 44 41 Depreciation per month $1,000 45 42 W NA 00 $98 38 39 47 48 49 50 000 June July August 51 FORMULA1 FORMULA2 0 0 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 June July August 0 FORMULA3 FORMULA 0 0 O O OO 58 FORMULA5 FORMULA6 0 0 FORMULAT 0 0 FORMULAS $0 $0 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 June July August 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 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 $8,000 $42,760 $142,720 29,400 58,800 31,360 76 19,600 94,080 27,440 31,360 82,320 29,400 $127,560 $183,880 $285,800 85 86 87 $74,800 FORMULAS 0 41,160 10,000 0 $0 44,100 10,000 00 00 00 00 $74,800 FORMULAS 0 41,160 10,000 0 0 0 SO 44,100 10,000 0 $84,800 $41,160 $54,100 $42,760 8,000 $142,720 $231,700 8,000 8,000 $34,760 $134,720 $223,700 $0 0 $0 0 $0 0 $0 $0 $0 $42,760 $142,720 $231,700 85 82 Merchandise 86 83 Variable expenses 87 84 Fixed expenses 88 85 Interest paid 89 86 90 87 Total disbursements 91 88 92 89 Cash balance before financing 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, 1985 127 124 128 125 Assets: 129 126 Cash FORMULA16 FORMULA17 FORMULA18 FORMULA19 FORMULA20 FORMULA21 FORMULA22 $0 0 $0 FORMULA23 FORMULA23 FORMULA24 FORMULA25 FORMULA26 $0 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 141 138 Total liabilities & equity 142 139 143 140 144 145 146 147 148 FORMULA27 0 FORMULA28 $0