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)

Console tables

July 10,000 August 12,000 September 9,000 October 8,000

Actual sales in May were 10,000 units; actual sales in June were 14,000 units.

2. ABC console tables selling price is $500/unit. Estimated cash collections from sales of each month (including June) of console tables to customers are as follows: 65% collected in the month of sale, 30% collected in the month following sale, and 5% cannot be collected.

3. Desired ending inventory of ABC console tables is 20% of the next months projected sales. There are 4,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: 45 pounds $6.00/pound Glass: 9 square feet $1.00/square foot Desired ending inventory of Red Oak is 30% of the following months production need because Red Oak is sometimes in short supply; desired ending inventory of Glass is 5% of the following months production need because Glass is easy to get. Inventories of materials as of June 30 are 26,000 pounds of Red Oak and 4,900 square feet of Glass.

5. The company pays for materials purchases as follows: 60% in the month of purchase, and 40% in the month following purchase. The accounts payable balance (due to materials purchases) on June 30 was $1,100,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: 1.70 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 PLEASE PUT IN PROPER EXCEL FORMAT WITH FORMULAS