Complete the excel using the video provided.

Complete the excel using the video provided.

Schedule 1: Sales Forecast Credit sales Cash sales Note: credit sales collected in 1 mo Schedule 2: Cash Collections Cash sales this month Credit sales last month Total collections Sales schedule March 92.000 40% 36.800 60% 55.200 55.200 Purchases schedule Schedule 3: Purchases March Ending inventory See note #3. 110.400 COGS See note #2. 64.400 Total needed 174.800 Beginning inventory See note #1 97.520 Purchases (total needed - beg inv) 77.280 COGS % of sales 70% #1: Begin the month with cushion 46 #2: COGS is 70% of sales. Beg. Inv. % of COGS 80% #3: End the month with en Note #4: March ending inventory matches the inventory on the initial balance sheet. Note #5: need August COGS forecast in order to calculate July ending inventory. Note #6: inventory policy is beginning inventory = 80% of COGS + cushion. Schedule 4: Purchase disbursements 50% of last month's purchases 50% of this month's purchases Purchase disbursements 50% 50% Schedule 5: Wages and commissions Wages, fixed commissions, 15% of sales Total wages and commissions Wages and commissions schedule March 5.75 5.750 15% 13.800 19.550 Schedule 6: disbursements -- wages and commissions 50% of last month's wages and commissions 50% 50% of this month's wages and commissions 50% Total disbursements -- wages and commissions Cash Budget Minimum cash balance Beginning cash balance Cash receipts: collections from customers Total cash available for needs before financing Cash disbursements: merchandise wages and commissions miscellaneous expenses, % of sales rent Truck purchase Total disbursements Minimum cash balance desired Total cash needed Excess of total cash Financing new borrowing interest repayments (PRINCIPAL REPAYMENTS) loan balance interest rate, % Total effects of financing 23.000 5% 4.600 6.900 1.50% Cash balance Note: The financing entries for April are different from the rest of the months in the fo Note A: Current month excess cash - interest payment > 0 pay all interest and at lea Current month excess cash - interest payment > previous month's loan balance pay a (Principal) repayment, if any, is the MIN of the excess of (excess cash - interest paymen (AND >= 0) Note B: The current month's loan balance = previous month's loan balance + new borr Note that here, any interest not paid is included in the new borrowing. PDC COMPANY PROJECTED INCOME STATEMENT March Sales COGS Gross margin Operating expenses Wages and commissions Rent Miscellaneous expenses Insurance Depreciation Total operating expenses Income from operations Interest expense Net income 0.460 1.150 PDC COMPANY PROJECTED BALANCE SHEETS (March is given on page 195, table 6.1) March Current assets Cash Accounts receivable Merchandise inventory Prepaid insurance Total current assets Plant Equipment, fixtures, and other Accumulated depreciation Net plant, property, and equipment Total assets Current liabilities Accounts payable Accrued wages and commissions payable Loan Total current liabilities Owners' equity Total liabilities and equity chedule April May June July August 115.000 184.000 138.000 115.000 92.000 46.000 73.600 55.200 46.000 69.000 110.400 82.800 69.000 69.000 36.800 105.800 110.400 46.000 156.400 82.800 73.600 156.400 69.000 55.200 124.200 s schedule April May June July August 149.040 123.280 110.400 97.520 80.500 128.800 96.600 80.500 64.400 229.540 252.080 207.000 178.020 110.400 149.040 123.280 110.400 119.140 103.040 83.720 67.620 Begin the month with enough inventory to cover 80% of COGS + 46.0 cushion. COGS is 70% of sales. End the month with enough to cover the beginning of next month. he initial balance sheet. ending inventory. GS + cushion. 38.640 59.570 98.210 59.570 51.520 111.090 51.520 41.860 93.380 41.860 33.810 75.670 missions schedule April May June July 5.750 5.750 5.750 5.750 17.250 27.600 20.700 17.250 23.000 33.350 26.450 23.000 9.775 11.500 21.275 11.500 16.675 28.175 16.675 13.225 29.900 13.225 11.500 24.725 Budget April May June July 23.000 23.000 23.000 23.000 105.800 128.800 156.400 179.400 156.400 179.400 124.200 147.200 98.210 21.275 5.750 4.600 6.900 136.735 23.000 159.735 -30.935 111.090 28.175 9.200 4.600 0.000 153.065 23.000 176.065 3.335 93.380 29.900 6.900 4.600 0.000 134.780 23.000 157.780 21.620 75.670 24.725 5.750 4.600 0.000 110.745 23.000 133.745 13.455 30.935 0.000 0.000 30.935 0.000 0.464 2.871 28.064 0.000 0.421 21.199 6.865 0.000 if Excess of total cash