PART TWO Cash flow and Financing Luis and Carlos believe that there is a need for better forecasting regarding the company's cash inflows and outflows. After a series of questions and answers with the Ruizes, you determined that (1) the company has not prepared a cash budget for several quarters and (2) is not up to date on its payables and receivables. You are going to prepare a forecasted income statement and a forecasted cash budget for the fourth quarter. Pertinent information needed has been collected and is outlined below. Pertinent Information: The business manager/accountant and Luis provided projections pertaining to the 1st quarter (January through March 2020) and other information outlined below: 1. Total sales 1st quarter 2020: 8,100 Generators; Sales price is $300/generator 2. Total Sales for the 2nd quarter 2020 are projected to increase 5% above the 1st quarter total sales due to an aggressive marketing program that began March 1, 2020. Total quarter sales per month are expected to be realized as follows: 25% in April, 35% in May, and 40% in June. The sales budgets expressed in sales dollars and expressed in the number of generators sold are as follows: PROJECTED April May June TOTAL QTR SALES REVENUE SALES $637,875 $893,025 $1,020,600 $2,551,500 SALES: PRICE X QUANTITY $637,875 $893,025 $1,020,600 $2,551,500 Number of Generators Sold April May June TOTAL QTR Number of Generators Sold 2,126 2,977 3,402 8,505 quarter sales per month are expected to be realized as follows: 25% in April, 35% in May, and 40% in June. The sales budgets expressed in sales dollars and expressed in the number of generators sold are as follows: PROJECTED April May June TOTAL QTR SALES REVENUE SALES $637,875 $893,025 $1,020,600 $2,551,500 SALES: PRICE X QUANTITY $637,875 $893,025 $1,020,600 $2,551,500 Number of Generators Sold April May June TOTAL QTR Number of Generators Sold 2,126 2,977 3,402 8,505 3. Monthly Sales are classified as follows and realized in corresponding percentage: a. Cash sales.. 25% of total sales b. Credit sales.... 75% of total sales 4. Monthly credit sales are collected as follows and in the corresponding proportion: a. Collected in the month of the sale, 37% b. Collected one month after the sale month, 38% c. Collected the second month after the sale month, 25% 5. Cost Classifications based on 1" quarter information: Product Costs Variable Fixed Costs Costs $ ? 570,000 Raw Materials Cost (See corrected COGS Schedule in Part 1, Required #1) Direct Labor Overhead: Rent-Factory Indirect Labor Insurance - Factory (70%) Utility-Factory (60%) Depreciation-factory building $ 180,000 98,000 39,900 34,200 9,000 91,000 Period Costs Advertising expense Selling & Administrative Wages and Salaries Utility-Selling & Administrative (40%) Insurance-Administrative (30%) Depreciation Administrative 90,000 25,200 270,000 135,000 3,600 17,100 135,000 6. All factory overhead and administrative expenses (except depreciation) are paid in cash in the month the cost is incurred. 7. Direct labor is paid at the end of the month 8. Raw material purchases are paid as follows: 50% in the month of the purchase, 30% the month after the purchase, and 20% in the second month following the purchase. The raw materials' budget is as follows: April May June TOTAL QTR PROJECTED RAW M TERIAL PURCHASES $795,900 NEEDED FOR SALES DESIRED ENDING LECCININILNIC $198,975 49,744 GLAGOA $278,565 69,641 23 $318,360 79,590 CORN 6. All factory overhead and administrative expenses (except depreciation) are paid in cash in the month the cost is incurred. 7. Direct labor is paid at the end of the month 8. Raw material purchases are paid as follows: 50% in the month of the purchase, 30% the month after the purchase, and 20% in the second month following the purchase. The raw materials budget is as follows: April May June TOTAL QTR PROJECTED RAW MATERIAL PURCHASES $795,900 NEEDED FOR SALES DESIRED ENDING LESS: BEGINNING PURCHASES $198,975 49,744 (106,000) $142,719 $278,565 69,641 (49,744) $298,463 $318,360 79,590 (69,641) $328,309 $769,490 9. Additional monthly obligation paid in cash include: a. Property taxes, due April 27th, $3,500 b. Employee payroll taxes due June 15th, $8,000, 5 You have determined that you are going to provide the following information to Luis and Carlos: a. Determine the variable cost PER Generator for EACH variable cost identified in the table above in #5. b. Prepare a contribution margin income statement for the total 1st quarter 2020 and for the projected total 2nd quarter 2020. HINT: You will need to prepare a detailed schedule of cost of goods sold for the quarter. Additional information needed is below: C. Prepare a forecasted traditional multi-step income statement for the total 2nd quarter. (See Part 1, Required #2 and Exhibit #1). Additional information is below: Inv story Data: 1-Apr 30-Jun Raw Materials $106,000 $69,641 Work-in-Process 84,000 71,400 Finished Goods 288,000 244,800 d. A schedule of collections from credit sales for each month of the 2nd quarter 2020. Previous months' credit sales are: February 2020 credit sales, $2,225,000 March 2020 credit sales, $2,025,000 e. A schedule of payment for raw materials for each month of the 2nd quarter 2020. Previous months' credit (on account) purchases: February 2020 purchases on account, $935,700 March 2020 purchases on account, $842,130 f. A forecasted cash budget for (a) the total 2nd quarter and (b) for each month in the 2nd quarter based on the information provided above. PART TWO Cash flow and Financing Luis and Carlos believe that there is a need for better forecasting regarding the company's cash inflows and outflows. After a series of questions and answers with the Ruizes, you determined that (1) the company has not prepared a cash budget for several quarters and (2) is not up to date on its payables and receivables. You are going to prepare a forecasted income statement and a forecasted cash budget for the fourth quarter. Pertinent information needed has been collected and is outlined below. Pertinent Information: The business manager/accountant and Luis provided projections pertaining to the 1st quarter (January through March 2020) and other information outlined below: 1. Total sales 1st quarter 2020: 8,100 Generators; Sales price is $300/generator 2. Total Sales for the 2nd quarter 2020 are projected to increase 5% above the 1st quarter total sales due to an aggressive marketing program that began March 1, 2020. Total quarter sales per month are expected to be realized as follows: 25% in April, 35% in May, and 40% in June. The sales budgets expressed in sales dollars and expressed in the number of generators sold are as follows: PROJECTED April May June TOTAL QTR SALES REVENUE SALES $637,875 $893,025 $1,020,600 $2,551,500 SALES: PRICE X QUANTITY $637,875 $893,025 $1,020,600 $2,551,500 Number of Generators Sold April May June TOTAL QTR Number of Generators Sold 2,126 2,977 3,402 8,505 quarter sales per month are expected to be realized as follows: 25% in April, 35% in May, and 40% in June. The sales budgets expressed in sales dollars and expressed in the number of generators sold are as follows: PROJECTED April May June TOTAL QTR SALES REVENUE SALES $637,875 $893,025 $1,020,600 $2,551,500 SALES: PRICE X QUANTITY $637,875 $893,025 $1,020,600 $2,551,500 Number of Generators Sold April May June TOTAL QTR Number of Generators Sold 2,126 2,977 3,402 8,505 3. Monthly Sales are classified as follows and realized in corresponding percentage: a. Cash sales.. 25% of total sales b. Credit sales.... 75% of total sales 4. Monthly credit sales are collected as follows and in the corresponding proportion: a. Collected in the month of the sale, 37% b. Collected one month after the sale month, 38% c. Collected the second month after the sale month, 25% 5. Cost Classifications based on 1" quarter information: Product Costs Variable Fixed Costs Costs $ ? 570,000 Raw Materials Cost (See corrected COGS Schedule in Part 1, Required #1) Direct Labor Overhead: Rent-Factory Indirect Labor Insurance - Factory (70%) Utility-Factory (60%) Depreciation-factory building $ 180,000 98,000 39,900 34,200 9,000 91,000 Period Costs Advertising expense Selling & Administrative Wages and Salaries Utility-Selling & Administrative (40%) Insurance-Administrative (30%) Depreciation Administrative 90,000 25,200 270,000 135,000 3,600 17,100 135,000 6. All factory overhead and administrative expenses (except depreciation) are paid in cash in the month the cost is incurred. 7. Direct labor is paid at the end of the month 8. Raw material purchases are paid as follows: 50% in the month of the purchase, 30% the month after the purchase, and 20% in the second month following the purchase. The raw materials' budget is as follows: April May June TOTAL QTR PROJECTED RAW M TERIAL PURCHASES $795,900 NEEDED FOR SALES DESIRED ENDING LECCININILNIC $198,975 49,744 GLAGOA $278,565 69,641 23 $318,360 79,590 CORN 6. All factory overhead and administrative expenses (except depreciation) are paid in cash in the month the cost is incurred. 7. Direct labor is paid at the end of the month 8. Raw material purchases are paid as follows: 50% in the month of the purchase, 30% the month after the purchase, and 20% in the second month following the purchase. The raw materials budget is as follows: April May June TOTAL QTR PROJECTED RAW MATERIAL PURCHASES $795,900 NEEDED FOR SALES DESIRED ENDING LESS: BEGINNING PURCHASES $198,975 49,744 (106,000) $142,719 $278,565 69,641 (49,744) $298,463 $318,360 79,590 (69,641) $328,309 $769,490 9. Additional monthly obligation paid in cash include: a. Property taxes, due April 27th, $3,500 b. Employee payroll taxes due June 15th, $8,000, 5 You have determined that you are going to provide the following information to Luis and Carlos: a. Determine the variable cost PER Generator for EACH variable cost identified in the table above in #5. b. Prepare a contribution margin income statement for the total 1st quarter 2020 and for the projected total 2nd quarter 2020. HINT: You will need to prepare a detailed schedule of cost of goods sold for the quarter. Additional information needed is below: C. Prepare a forecasted traditional multi-step income statement for the total 2nd quarter. (See Part 1, Required #2 and Exhibit #1). Additional information is below: Inv story Data: 1-Apr 30-Jun Raw Materials $106,000 $69,641 Work-in-Process 84,000 71,400 Finished Goods 288,000 244,800 d. A schedule of collections from credit sales for each month of the 2nd quarter 2020. Previous months' credit sales are: February 2020 credit sales, $2,225,000 March 2020 credit sales, $2,025,000 e. A schedule of payment for raw materials for each month of the 2nd quarter 2020. Previous months' credit (on account) purchases: February 2020 purchases on account, $935,700 March 2020 purchases on account, $842,130 f. A forecasted cash budget for (a) the total 2nd quarter and (b) for each month in the 2nd quarter based on the information provided above