Sales, Production, Direct Material Cost, Direct Labor Cost and S & A Budgets: Data ABC Manufacturing Company produces and sells one product, a console table. Below is information on its activities for the next few months. 1. Sales projections for the coming months are as follows: Estimated Sales (in units) July August September October Console tables 11,000 12,000 10,000 9,000 Actual sales in May were 10,000 units; actual sales in June were 14,000 units. 2. ABC console table's selling price is $450/unit. Estimated cash collections from sales of each month (including June) of console tables to customers are as follows: 55% collected in the month of sale, 40% collected in the month following sale, and 5% cannot be collected 3. Desired ending inventory of ABC console tables is 20% of the next month's projected sales. There are 5,000 units of console tables in inventory on June 30. 4. Two materials are used in the production of ABC console table: Red Oak and Glass. Materials requirements per unit of console table are as follows: Direct Material Units of direct materials Cost per unit per unit of ABC console table Red Oak: 42 pounds $5.00/pound Glass: 9 square feet $2.00/square foot Desired ending inventory of Red Oak is 25% of the following month's production need because Red Oak is sometimes in short supply; desired ending inventory of Glass is 10% of the following month's production need. Inventories of materials as of June 30 are 25,500 pounds of Red Oak and 7,000 square feet of Glass. 5. The company pays for materials purchases as follows: 55% in the month of purchase, and 45% in the month following purchase. The accounts payable balance (due to materials purchases) on June 30 was $950,000, which is all payable in July. 6. ABC console tables have two departments to pass before they are completed. Department Direct labor hours per unit of console table Cost per direct labor hour Shaping: 0.75 hour $24 Finishing: 2 hours $16 7. Direct labor costs are paid in cash as incurred. 8. Total variable manufacturing overhead is estimated at $2.5/direct labor hour incurred. Total fixed manufacturing overhead is estimated at $500,000/month, of which $5,000 is depreciation on factory buildings and equipment. Overhead costs are paid when incurred. 9. Total variable selling and administrative costs are $2/unit of ABC console table sold. Total fixed selling and administrative costs are estimated at $300,000/month, of which $65,000 is depreciation on administrative buildings and equipment. Selling and administrative costs are paid as the costs are incurred Data Required: (30 pts) Preparation of Spreadsheet File Create one Excel spreadsheet file consisting of the following five separate worksheets: Sheet 1: This worksheet contains the data necessary to do all the other worksheets. List on this worksheet all of the data shown above, clearly labeled. None of the other worksheets should contain any numbers; they should contain ONLY FORMULAS-all cells on the other worksheets should be linked to cells in the data worksheet, cells within the same worksheet, or cells in the other worksheets. For example, the cell for direct labor cost for July should contain a formula that multiplies the production in units for July (from the production schedule) times the labor hours per unit (from the data worksheet) times the hourly wage rate (from the data worksheet). So if the sales estimate for console tables changes, you should be able to make the change only on the data worksheet; all the other worksheets should automatically adjust to the changes. The production units will change the manufacturing costs will change, etc. The data worksheet can be in any format; just be sure to label each data item clearly. Sheet 2: Include the following two budgets on the second worksheet, clearly labeled: Sales Budget: Prepare a schedule of sales revenue and cash receipts from sales for each of the months of July, August and September. List cash collections separately on lines as follows: cash collections from sales one month ago and cash collections from sales in the current month. Also, list total cash collections in July, August and September. Production Budget: Prepare a production budget for ABC console tables, in units, for each of the months of July, August and September. Sheet 3: Include the following one budget on the third worksheet, clearly labeled: Direct Materials Cost Budget: Prepare a direct materials purchases budget, in units and in total dollars, for July and August. List Red Oak purchase costs, Glass purchase costs, and total material purchase costs separately. List payments for current month purchase and for prior month purchase on separate lines. Also, list total payments in July and August Sheet 4: Include the following one budget on the fourth worksheet, clearly labeled: Direct Labor Cost Budget: Prepare the budget for costs of direct labor used for July and August, in units and in total dollars. List shaping labor cost, finishing labor cost, and total labor cost separately. Also, list total payments for each month. Sheet 5: Selling & Administrative Expense Budget: Prepare a selling and administrative expense budget for July and August. Show variable selling and administrative costs, fixed selling and administrative costs and total selling and administrative costs separately for each month. List total cash payments for each month. Sales, Production, Direct Material Cost, Direct Labor Cost and S & A Budgets: Data ABC Manufacturing Company produces and sells one product, a console table. Below is information on its activities for the next few months. 1. Sales projections for the coming months are as follows: Estimated Sales (in units) July August September October Console tables 11,000 12,000 10,000 9,000 Actual sales in May were 10,000 units; actual sales in June were 14,000 units. 2. ABC console table's selling price is $450/unit. Estimated cash collections from sales of each month (including June) of console tables to customers are as follows: 55% collected in the month of sale, 40% collected in the month following sale, and 5% cannot be collected 3. Desired ending inventory of ABC console tables is 20% of the next month's projected sales. There are 5,000 units of console tables in inventory on June 30. 4. Two materials are used in the production of ABC console table: Red Oak and Glass. Materials requirements per unit of console table are as follows: Direct Material Units of direct materials Cost per unit per unit of ABC console table Red Oak: 42 pounds $5.00/pound Glass: 9 square feet $2.00/square foot Desired ending inventory of Red Oak is 25% of the following month's production need because Red Oak is sometimes in short supply; desired ending inventory of Glass is 10% of the following month's production need. Inventories of materials as of June 30 are 25,500 pounds of Red Oak and 7,000 square feet of Glass. 5. The company pays for materials purchases as follows: 55% in the month of purchase, and 45% in the month following purchase. The accounts payable balance (due to materials purchases) on June 30 was $950,000, which is all payable in July. 6. ABC console tables have two departments to pass before they are completed. Department Direct labor hours per unit of console table Cost per direct labor hour Shaping: 0.75 hour $24 Finishing: 2 hours $16 7. Direct labor costs are paid in cash as incurred. 8. Total variable manufacturing overhead is estimated at $2.5/direct labor hour incurred. Total fixed manufacturing overhead is estimated at $500,000/month, of which $5,000 is depreciation on factory buildings and equipment. Overhead costs are paid when incurred. 9. Total variable selling and administrative costs are $2/unit of ABC console table sold. Total fixed selling and administrative costs are estimated at $300,000/month, of which $65,000 is depreciation on administrative buildings and equipment. Selling and administrative costs are paid as the costs are incurred Data Required: (30 pts) Preparation of Spreadsheet File Create one Excel spreadsheet file consisting of the following five separate worksheets: Sheet 1: This worksheet contains the data necessary to do all the other worksheets. List on this worksheet all of the data shown above, clearly labeled. None of the other worksheets should contain any numbers; they should contain ONLY FORMULAS-all cells on the other worksheets should be linked to cells in the data worksheet, cells within the same worksheet, or cells in the other worksheets. For example, the cell for direct labor cost for July should contain a formula that multiplies the production in units for July (from the production schedule) times the labor hours per unit (from the data worksheet) times the hourly wage rate (from the data worksheet). So if the sales estimate for console tables changes, you should be able to make the change only on the data worksheet; all the other worksheets should automatically adjust to the changes. The production units will change the manufacturing costs will change, etc. The data worksheet can be in any format; just be sure to label each data item clearly. Sheet 2: Include the following two budgets on the second worksheet, clearly labeled: Sales Budget: Prepare a schedule of sales revenue and cash receipts from sales for each of the months of July, August and September. List cash collections separately on lines as follows: cash collections from sales one month ago and cash collections from sales in the current month. Also, list total cash collections in July, August and September. Production Budget: Prepare a production budget for ABC console tables, in units, for each of the months of July, August and September. Sheet 3: Include the following one budget on the third worksheet, clearly labeled: Direct Materials Cost Budget: Prepare a direct materials purchases budget, in units and in total dollars, for July and August. List Red Oak purchase costs, Glass purchase costs, and total material purchase costs separately. List payments for current month purchase and for prior month purchase on separate lines. Also, list total payments in July and August Sheet 4: Include the following one budget on the fourth worksheet, clearly labeled: Direct Labor Cost Budget: Prepare the budget for costs of direct labor used for July and August, in units and in total dollars. List shaping labor cost, finishing labor cost, and total labor cost separately. Also, list total payments for each month. Sheet 5: Selling & Administrative Expense Budget: Prepare a selling and administrative expense budget for July and August. Show variable selling and administrative costs, fixed selling and administrative costs and total selling and administrative costs separately for each month. List total cash payments for each month