November December Spreadsheet Exercises/Problems Note: The following activities are for students with spreadsheet software skills 11. Short-term financial planning for the PDC Company was described earlier in this chapter. Refer to the PDC Company's projected monthly operating schedules in Table 6.2. PDC's monthly sales for the re mainder of 2017 are expected to be: September $ 80,000 October $100,000 $130,000 $160,000 A. Prepare PDC's sales schedule, purchases schedule, and wages schedule for each of the last four months of 2017. B. Prepare cash budgets for each of the last four months of 2017 for the PDC Company and describe how the forecast affects the end-of-month cash balances. C. Prepare PDC's projected monthly income statements for the August-December period. D. Prepare PDC's projected monthly balance sheets for the August-December period. E. Prepare PDC's projected monthly statements of cash flows for the August-December period. F. Compare your balance sheet at the end of December with the balance sheet in Table 6.1 and apply the balance sheet method to determine cash flows over the March-December period. onducte. The firm'e management team Table 6.2 PDC Company Operating Schedules and Cash Budget (A) SALES SCHEDULE APRIL TO JUNE JULY AUGUST MAY JULY MARCH APRIL 92,000 562033 Schedule 1: Sales Forecast Credit sales, 40% Cash sales 60% 92.000 36800 55.200 115.000 45.000 59.000 184000 73.600 110400 139,000 55 200 82.800 115,000 46,000 59,000 Schedule 2: Cash Collections Cash sales this month 100% of last month's credit sales Total collections 59.000 36.800 105 800 110400 16.000 156 400 82 800 73.600 156.400 59.000 55 200 124 200 PURCHASES SCHEDULE APRIL TO JULY APRIL MARCH MAY JUNE JULY 386600 Schedule 3: Purchases Ending inventory Cost of goods sold Total needed Beginning inventory Purchases 110.400 64400 174 800 -97570 77.280 149.00 30.500 229540 -10.400 119,140 123.280 128 800 252.080 -9.000 703.040 110.000 96.600 207.000 -23.280 83.720 97520 80.500 178,020 -10.400 67.620 Schedule 4 Purchase Disbursements 50% of last month's purchases 50% of this month's purchases Disbursements for purchases 38.640 59570 98.210 59,570 51.520 111.090 51,520 41,860 93380 41,860 33,810 75,570 (C) WAGES AND COMMISSIONS SCHEDULE MARCH APRIL MAY JUNE JULY APRIL TO JULY 5,750 5750 5.750 5750 5.750 Schedule 5: Wages and Commissions Wages, all fixed Commissions 15% of current sales Total Schedule 6: Disbursements-Wages and Commissions 50% of last month's expenses 50% of this month's supenses Total 13.900 19.550 17.250 22.000 27500 33 350 20,700 26,450 17.250 23.000 105800 9.775 11.500 21275 11.500 15.675 2879 15575 13725 29.900 13,225 11.500 24725 Table 6.2 PDC Company Operating Schedules and Cash Badget (A) SALES SCHEDULE MARCH APRIL APRIL TO MAY JUNE JULY AUGUST JULY Schedule 1: Sales Forecast Credit sales, 40% Cash sales, 60% 92.000 92.000 36.800 55,200 552.000 115.000 46.000 59.000 184.000 73.600 110.400 138.000 55.200 3209 115.000 16.000 68.000 Schedule 2 Cash Collections Cash sales this month 100% of last month's credit sales Total collections 69.000 36.800 105.800 110.000 16.000 82.800 73.900 156.400 59.000 55200 124 200 (B) PURCHASES SCHEDULE MARCH APRIL MAY JUNE JULY APRIL TO JULY Schedule 3: Purchases Ending inventory Cost of goods sold Total needed Beginning inventory Purchases 386.400 110.400 54.400 174,800 -97,520 77.280 149,040 80.500 229.540 -10.400 119,140 123.280 128 800 252 080 -9,040 105.040 11 400 96 800 207.000 -23290 80320 97,520 80.500 178,020 -10.000 57.620 Schedule 4: Purchase Disbursements 50% of last month's purchases 50% of this month's purchases Disbursements for purchases 38.540 59.570 58.210 58.570 51520 111030 54520 1990 90 380 41.860 33 610 75.620 (C) WAGES AND COMMISSIONS SCHEDULE APRIL TO JULY MARCH APRIL MAY JUNE JULY 5.750 5,750 5750 5,750 5.750 13,800 19.550 17.250 23.000 27.600 39 350 20700 26.650 17.250 23.000 105.800 Schedule 5: Wages and Commissions Wages, all fixed Commissions (15% of Current Sales] Total Schedule 6: Disbursements-Wages and Commissions 50% of last month's expenses of this month's expenses Total 9775 11500 21775 11.500 16.625 16575 13.723 29.900 13.725 11.500 November [Note: The following activities are for students with spreadsheet software amo 11. Short-term financial planning for the PDC Company was described earlier in this chapter. Refer to the PDC Company's projected monthly operating schedules in Table 6.2. PDC's monthly sales for the re mainder of 2017 are expected to be: September $ 80,000 October $100,000 $130,000 December $160,000 A. Prepare PDC's sales schedule, purchases schedule, and wages schedule for each of the last four months of 2017 B. Prepare cash budgets for each of the last four months of 2017 for the PDC Company and describe how the forecast affects the end-of-month cash balances. C. Prepare PDC's projected monthly income statements for the August-December period. D. Prepare PDC's projected monthly balance sheets for the August-December period. E. Prepare PDC's projected monthly statements of cash flows for the August-December period. F. Compare your balance sheet at the end of December with the balance sheet in Table 6.1 and apply the balance sheet method to determine cash flows over the March-December period. November December Spreadsheet Exercises/Problems Note: The following activities are for students with spreadsheet software skills 11. Short-term financial planning for the PDC Company was described earlier in this chapter. Refer to the PDC Company's projected monthly operating schedules in Table 6.2. PDC's monthly sales for the re mainder of 2017 are expected to be: September $ 80,000 October $100,000 $130,000 $160,000 A. Prepare PDC's sales schedule, purchases schedule, and wages schedule for each of the last four months of 2017. B. Prepare cash budgets for each of the last four months of 2017 for the PDC Company and describe how the forecast affects the end-of-month cash balances. C. Prepare PDC's projected monthly income statements for the August-December period. D. Prepare PDC's projected monthly balance sheets for the August-December period. E. Prepare PDC's projected monthly statements of cash flows for the August-December period. F. Compare your balance sheet at the end of December with the balance sheet in Table 6.1 and apply the balance sheet method to determine cash flows over the March-December period. onducte. The firm'e management team Table 6.2 PDC Company Operating Schedules and Cash Budget (A) SALES SCHEDULE APRIL TO JUNE JULY AUGUST MAY JULY MARCH APRIL 92,000 562033 Schedule 1: Sales Forecast Credit sales, 40% Cash sales 60% 92.000 36800 55.200 115.000 45.000 59.000 184000 73.600 110400 139,000 55 200 82.800 115,000 46,000 59,000 Schedule 2: Cash Collections Cash sales this month 100% of last month's credit sales Total collections 59.000 36.800 105 800 110400 16.000 156 400 82 800 73.600 156.400 59.000 55 200 124 200 PURCHASES SCHEDULE APRIL TO JULY APRIL MARCH MAY JUNE JULY 386600 Schedule 3: Purchases Ending inventory Cost of goods sold Total needed Beginning inventory Purchases 110.400 64400 174 800 -97570 77.280 149.00 30.500 229540 -10.400 119,140 123.280 128 800 252.080 -9.000 703.040 110.000 96.600 207.000 -23.280 83.720 97520 80.500 178,020 -10.400 67.620 Schedule 4 Purchase Disbursements 50% of last month's purchases 50% of this month's purchases Disbursements for purchases 38.640 59570 98.210 59,570 51.520 111.090 51,520 41,860 93380 41,860 33,810 75,570 (C) WAGES AND COMMISSIONS SCHEDULE MARCH APRIL MAY JUNE JULY APRIL TO JULY 5,750 5750 5.750 5750 5.750 Schedule 5: Wages and Commissions Wages, all fixed Commissions 15% of current sales Total Schedule 6: Disbursements-Wages and Commissions 50% of last month's expenses 50% of this month's supenses Total 13.900 19.550 17.250 22.000 27500 33 350 20,700 26,450 17.250 23.000 105800 9.775 11.500 21275 11.500 15.675 2879 15575 13725 29.900 13,225 11.500 24725 Table 6.2 PDC Company Operating Schedules and Cash Badget (A) SALES SCHEDULE MARCH APRIL APRIL TO MAY JUNE JULY AUGUST JULY Schedule 1: Sales Forecast Credit sales, 40% Cash sales, 60% 92.000 92.000 36.800 55,200 552.000 115.000 46.000 59.000 184.000 73.600 110.400 138.000 55.200 3209 115.000 16.000 68.000 Schedule 2 Cash Collections Cash sales this month 100% of last month's credit sales Total collections 69.000 36.800 105.800 110.000 16.000 82.800 73.900 156.400 59.000 55200 124 200 (B) PURCHASES SCHEDULE MARCH APRIL MAY JUNE JULY APRIL TO JULY Schedule 3: Purchases Ending inventory Cost of goods sold Total needed Beginning inventory Purchases 386.400 110.400 54.400 174,800 -97,520 77.280 149,040 80.500 229.540 -10.400 119,140 123.280 128 800 252 080 -9,040 105.040 11 400 96 800 207.000 -23290 80320 97,520 80.500 178,020 -10.000 57.620 Schedule 4: Purchase Disbursements 50% of last month's purchases 50% of this month's purchases Disbursements for purchases 38.540 59.570 58.210 58.570 51520 111030 54520 1990 90 380 41.860 33 610 75.620 (C) WAGES AND COMMISSIONS SCHEDULE APRIL TO JULY MARCH APRIL MAY JUNE JULY 5.750 5,750 5750 5,750 5.750 13,800 19.550 17.250 23.000 27.600 39 350 20700 26.650 17.250 23.000 105.800 Schedule 5: Wages and Commissions Wages, all fixed Commissions (15% of Current Sales] Total Schedule 6: Disbursements-Wages and Commissions 50% of last month's expenses of this month's expenses Total 9775 11500 21775 11.500 16.625 16575 13.723 29.900 13.725 11.500 November [Note: The following activities are for students with spreadsheet software amo 11. Short-term financial planning for the PDC Company was described earlier in this chapter. Refer to the PDC Company's projected monthly operating schedules in Table 6.2. PDC's monthly sales for the re mainder of 2017 are expected to be: September $ 80,000 October $100,000 $130,000 December $160,000 A. Prepare PDC's sales schedule, purchases schedule, and wages schedule for each of the last four months of 2017 B. Prepare cash budgets for each of the last four months of 2017 for the PDC Company and describe how the forecast affects the end-of-month cash balances. C. Prepare PDC's projected monthly income statements for the August-December period. D. Prepare PDC's projected monthly balance sheets for the August-December period. E. Prepare PDC's projected monthly statements of cash flows for the August-December period. F. Compare your balance sheet at the end of December with the balance sheet in Table 6.1 and apply the balance sheet method to determine cash flows over the March-December period