French Colas bottles two soft drinks under license from Cadbury Schweppes at its Lyon Plant. Bottling at this plant is a highly repetitive, automated process. Empty bottles are removed from their carton, placed on a conveyor, and cleaned, rinsed, dried, filled, capped, and heated (to reduce condensation). All inventory is in direct materials and finished goods at the end of each working day. There is no work-inprocess inventory. The two soft drinks bottled by French Colas are Orange and diet Orange. The syrup for both soft drinks is purchased from Cadbury Schweppes. Syrup for the regular brand contains higher sugar content than that for the diet brand. French Colas uses a lot size of 1,000 cases as the unit of analysis in its budgeting. (Each case contains 24 bottles). Direct materials are expressed in terms of lots, where one lot of direct materials is the input necessary to yield one lot (1000 cases) of beverage. In 2021, the following purchase prices are forecast for direct materials: Orange Diet Orange Syrup $1,200 per lot $1,100 per lot Containers (bottles, caps, etc) $1,000 per lot $1,000 per lot Packaging $ 800 per lot $ 800 per lot The two soft drinks are bottled using the same equipment. The equipment is sanitized daily, but it is only rinsed when a switch is made during the day between diet orange and orange. Diet orange is always bottled first each day to reduce risk of sugar contamination. The only difference in the bottling process for the two soft drinks is the syrup. Summary data used in developing budgets for 2021 are as follows:

1. SALES

a. Orange: 1,080 lots at $9,000 selling price per lot. b. Diet Orange: 540 lots at $8,500 selling price per lot.

2. BEGINNING INVENTORY OF RAW MATERIALS (January 1, 2021)

a. Syrup for Orange: 80 lots for $1,100 purchase price per lot b. Syrup for Diet Orange: 70 lots for $1,000 purchase price per lot c. Containers: 200 lots for $950 purchase price per lot d. Packaging: 400 lots for $900 purchase price per lot 2

3. BEGINNING INVENTORY OF FINISHED GOODS (January 1, 2021) a. Orange: 100 lots at $5,300 per lot b. Diet Orange: 50 lots at $5,200 per lot

4. TARGET ENDING INVENTORY OF RAW MATERIALS (December 31, 2021) a. Syrup for Orange, 30 lots b. Syrup for Diet Orange, 20 lots c. Containers, 100 lots d. Packaging, 200 lots

5. TARGET ENDING INVENTORY OF FINISHED GOODS (December 31, 2021) a. Orange, 20 lots b. Diet Orange, 10 lots 6. Each lot requires 20 direct manufacturing labor-hours at the 2020 budgeted rate of $25 per hour. Indirect manufacturing labor costs are included in the manufacturing overhead forecast. 7. Variable manufacturing overhead is forecast to be $600 per hour of bottling time; bottling time is the time the filling equipment is in operation. It takes 2 hours to bottle one lot of orange and 2 hours to bottle one lot of diet orange. 8. Fixed manufacturing overhead is forecast at $1,200,000 for 2021. 9. Hours of budgeting bottling time is the sole allocation base for all fixed manufacturing overhead. 10. Administration costs are forecast to be 10% of the cost of goods manufactured for 2021. Marketing costs are forecasted at 12% of dollar sales for 2021. Distribution costs are forecast at 8% of dollar sales for 2021.

REQUIRED: submit an excel file comprising the following budgets & prepare for last minute updates on the data set

a. Sales Revenue budget (in dollars)

b. Production budget (in units)

c. Direct Material Usage Budget (in units and dollars)

d. Direct Materials Purchases Budget (in units and dollars)

e. Direct manufacturing labor Budget (in dollars)

f. Manufacturing Overhead Costs Budget (in dollars)

g. Ending Finished Goods Inventory Budget (in dollars) h. Cost of Goods Sold Budget (in dollars)

i. Marketing Costs Budget (in dollars)

j. Distribution Costs Budget (in dollars)

k. Administration Costs Budget (in dollars)

l. Budgeted Income Statement (in dollars)

PLEASE HELP ME FOR D-L !!