20211_ACG2071_Module 7 M... Falenda Bureau FB X X AutoSave ( Off Search Comments Share File Home Insert Page Layout Formulas Data Review View Automate Help fx e Define Name By Trace Precedents x Show Formulas Calculate Now Use in Formula Error Checking Watch Calculation Insert t Date & Lookup & Math & More Name By Trace Dependents AutoSum Recently Financial Logical Calculate Sheet Manager Create from Selection Window Options Function Used * Time Reference Trig Functions Ex Remove Arrows * Evaluate Formula Calculation Function Library Defined Names Formula Auditing E140 vi XVfx F G H I J K I M N A B C D E 10 11 8 Actual and Budgeted Unit Sales: 12 9 April 1,500 13 10 May 1,000 14 1 1 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, 19X5 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 MASTER + + 100% Ready Accessibility: Investigate 1:09 PM 1 73OF Q Search W 4 11/9/2023 Partly sunnyX AutoSave ( Off 20211_ACG2071_Module 7 M... Search Falenda Bureau FB X File Home Insert Page Layout Formulas Data Review View Automate Help Comments Share fx e Define Name By Trace Precedents x Show Formulas Calculate Now Use in Formula By Trace Dependents Error Checking Insert AutoSum Recently Financial Logical t Date & Lookup & Math & More Name Watch Calculation Options Calculate Sheet Function Used * Time Reference Trig Functions Manager Create from Selection Ex Remove Arrows * Evaluate Formula Window Function Library Defined Names Formula Auditing Calculation E140 A B C D E F G H I J K I M N 32 29 33 30 Average selling price $98 34 31 Average purchase cost per unit $55 35 32 Desired ending inventory 36 3 (% of next month's unit sales) 60% 37 34 Collections from customers: 38 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 39 Variable expenses 30% of sales 43 40 Fixed expenses (per month) $10,000 44 41 Depreciation per month $1,000 45 42 46 43 47 44 Answer Section: 48 45 Sales Budget 49 46 50 47 June July August 51 48 52 49 Units 1,600 1,400 1,500 53 50 Dollars $156,800 137,200 147,000 54 51 MASTER + + 100% Ready Accessibility: Investigate 1 73OF Q Search W 1:09 PM 4 11/9/2023 Partly sunnySearch Falenda Bureau FB X AutoSave ( Off 20211_ACG2071_Module 7 M... X Insert Page Layout Formulas Data Review View Automate Help Comments Share File Home fx e Define Name By Trace Precedents x Show Formulas Calculate Now Name Use in Formula By Trace Dependents Error Checking t Date & Lookup & Math & More Watch Insert AutoSum Recently Financial Logical Calculation Calculate Sheet Used * Manager Create from Selection Ex Remove Arrows * Evaluate Formula Options Function Time Reference Trig Functions Window Calculation Function Library Defined Names Formula Auditing E140 vi XVfx A_ B C D E F G H I J K I M N 57 54 June July August 58 55 59 56 Desired ending inventory 840 900 720 60 57 Current month's unit sales 1,600 1,400 1,500 61 58 62 59 Total units needed 2,440 2,300 2,220 63 60 Beginning inventory 960 840 900 64 61 65 62 Purchases (units) 1,480 1,460 1,320 66 63 67 64 Purchases (dollars) $81,400 $80,300 $72,600 68 65 69 66 70 67 Cash Budget 71 68 72 69 June July August 73 70 74 71 Cash balance, beginning $8,000 $8,000 $8,000 75 72 Cash receipts: 76 73 Collections from customers: 77 74 From April sales 29,400 78 75 From May sales 58,800 19,600 79 76 From .June sales 31 360 94 080 31 360 MASTER + + 100% Ready Accessibility: Investigate W 1:10 PM 1 73OF Q Search 11/9/2023 4 Partly sunnyX AutoSave ( Off 20211_ACG2071_Module 7 M... Search Falenda Bureau FB X File Home Insert Page Layout Formulas Data Review View Automate Help Comments Share fx LA e Define Name By Trace Precedents x Show Formulas Calculate Now Insert AutoSum Recently Financial Logical t Date & Lookup & Math & More Name Use in Formula By Trace Dependents Error Checking Watch Calculation Options Calculate Sheet Function Used * Time Reference Trig Functions Manager Create from Selection Ex Remove Arrows Evaluate Formula Window Function Library Defined Names Formula Auditing Calculation E140 vi XVfx A B C D E F G H I J K I M N 79 76 From June sales 31,360 94,080 31,360 80 77 From July sales 27,440 82,320 81 78 From August sales 29,400 82 79 83 80 Total cash available $127,560 $149,120 $151,080 84 81 Cash disbursements: 85 82 Merchandise $74,800 $81,400 $80,300 86 83 Variable expenses 47,040 41,160 44,100 F 87 84 Fixed expenses 10,000 10,000 10,000 88 85 Interest paid 184 59 89 86 90 87 Total disbursements $131,840 $132,744 $134,459 91 88 92 89 Cash balance before financing ($4,280) $16,376 $16,621 93 90 Less: Desired ending balance 8,000 8,000 8,000 94 91 95 92 Excess (deficit) of cash over needs ($12,280) $8,376 $8,621 96 93 97 94 Financing 98 95 Borrowing $12,280 $0 S 99 96 Repayment (8,376) (3,904) 100 97 101 98 Total effects of financing $12,280 ($8,376) ($3,904) MASTER + Ready Accessibility: Investigate + 100% 1 73OF W 1:11 PM Q Search Partly sunny 11/9/2023 420211_ACG2071_Module 7 M... Search FB X X AutoSave ( Off Falenda Bureau Comments Share File Home Insert Page Layout Formulas Data Review View Automate Help fx e Define Name By Trace Precedents x Show Formulas Calculate Now Use in Formula * By Trace Dependents Error Checking Watch Insert AutoSum Recently Financial Logical t Date & Lookup & Math & More Name Calculation Calculate Sheet Used * Manager Create from Selection Ex Remove Arrows Evaluate Formula Window Options Function Time Reference Trig Functions Defined Names Formula Auditing Calculation Function Library E140 vi X fx B C D E F G H I J K I M N A 103 100 Cash balance, ending $8,000 $8,000 $12,717 104 101 105 102 106 103 Forecasted Income Statement 107 104 For Quarter Ended August 31, 19X5 108 105 109 106 Sales "$441,000 110 107 Cost of goods sold 247,500 111 108 112 109 Gross profit $193,500 113 110 114 111 Expenses: 115 112 Variable expenses $132,300 116 113 Fixed expenses 30,000 117 114 Depreciation expense 3,000 118 115 Interest expense 243 119 116 120 117 Total expenses $165,543 121 118 122 119 Net income $27,957 123 120 124 121 125 122 Forecasted Balance Sheet MASTER + + 100% Ready Accessibility: Investigate W 1:11 PM 1 73OF Q Search 11/9/2023 4 Partly sunnySearch FB X X AutoSave ( Off 20211_ACG2071_Module 7 M... Falenda Bureau Share File Home Insert Page Layout Formulas Data Review View Automate Help Comments fx e Define Name By Trace Precedents x Show Formulas Calculate Now Use in Formula By Trace Dependents Error Checking Insert AutoSum Recently Financial Logical t Date & Lookup & Math & More Name Watch Calculation Calculate Sheet Function Used * Time Reference Trig Functions Manager Create from Selection Ex Remove Arrows * Evaluate Formula Window Options Function Library Defined Names Formula Auditing Calculation E140 vi XVfx A_ B C D E F G H I J K I M N 124 121 125 122 Forecasted Balance Sheet 126 123 August 31, 19X5 127 124 128 125 Assets: 129 126 Cash $12, 717 130 127 Accounts receivable $145,040 131 128 Merchandise inventory 234,300 132 129 Fixed assets (net) 127,000 133 130 134 131 Total assets $519,057 135 132 136 133 Liabilities & equity: 137 134 Accounts payable $74,800 138 135 Loans payable 139 136 Owner's equity 251,757 140 137 141 138 Total liabilities & equity $326,557 142 139 143 140 144 145 146 > MASTER + + 100% Ready Accessibility: Investigate W 1:11 PM 1 73OF Q Search 11/9/2023 4 Partly sunny