EXCEL SPREADSHEET PROJECT

ABC Company manufactures a long-lasting cleaning product, Super Clean Sponge, using a pre-cut sponge and Chemical X. The company buys the pre-cut sponges and Chemical X from outside entities and infuses Chemical X into the sponge to make its finished product. Projections for the upcoming year are as follows:

1.In 2020, ABC Company expects to sell 40,000 Super Clean Sponges in the first quarter; 50,000 sponges in the second quarter, 70,000 sponges in the 3d quarter, and 60,000 sponges in the 4th quarter.

2.At the beginning of the first quarter of 2020, there are 8,000 finished Super Clean Sponges on hand.

3.The desired ending inventory of finished goods is 20% of the next quarter's sales.

4.At the end of the 4th quarter 2020, the company must have 14,000 finished Super Clean Sponges on hand, and 3,400 pounds of Chemical on hand.

5.The selling price of each Super Clean Sponge is $10.00.

6.Each finished Super Clean sponge uses 1 pre-cut sponge and 8 oz. of Chemical X.

7.The cost of a pre-cut sponge is $2.00 and the cost of Chemical X is $3.00 per pound.

8.At the beginning of the first quarter of 2020 there are 10,000 pre-cut sponges (untreated) and 8,000 pounds of Chemical X on hand.

9.The desired ending inventory of raw materials is 10% of the next quarter's production.

10.Direct Labor cost is $0.75 per sponge; variable overhead cost is $0.20 per sponge; fixed overhead is $20,000 per quarter.

11.Variable selling and administrative expenses are 12% of sales; Fixed selling and administrative expenses are 40,000 per quarter.

13.Assume 20% of sales are for cash, 70% are collected in the quarter following the sale, and 10% are collected in the 2nd quarter following the sale

14.Assume all expenses and production costs are paid in the quarter incurred (Selling, administrative, materials, labor, and overhead).

15.Assume sales in quarter 3 of 2019 were $350,000, and sales in quarter 4 of 2019 were $300,000.

16.Assume the cash balance at the beginning of Quarter 1, 2020 was $55,000.

REQUIRED:USING EXCEL, PREPARE THE FOLLOWING:

1.Sales Budget for each quarter 2020

2.Production Budget for each quarter 2020

3.Budgeted Income Statement for each quarter 2020

4.Cash Budget for each quarter 2020

The purpose of the project is for you to utilize excel formulas and link cells as you develop the required budgets. DO NOT MAKE CALCULATIONS BY HAND AND THEN ENTER THEM INTO the EXCEL SPREADSHEET CELLS.