Please fill out the excel sheet for me!!

Production Budget in Lots Orange D. Orange Beg. FG Inv Sales Cl. FG Inv Production Orange D. Orange Raw Materials Purchases Budget (Lots) Orange Srp Beg RM Inv RM Used Clo. RM Inv Syrup Purch Raw Materials Purchases Budget (Lots) Containers Beg RM Inv RM Used Cio. RM Inv Catne Purch ANOJ Packin Beg RM IN RM Used CRM In Packin Purch Raw Materials Purchases Budget (SS) ANOU Syrup Containers Packaging RM PurchCst Bottlers PLOT Totiu Hrs VMOH 55 Variable Manufacturing Overhead (SS) Product Orange D. Orange VMOH Rate Admin. Market. Distribte Budgets (SS) Admin Marketing Distribution TOTAL 10% COGM 12% SALES 8% SALES COST OF GOOD SOLD STATEMENT (proforma, full) for the year ended December 2019, budgeted rigures plot Price (except for COGM) COGM OpFGENOJ OpFGIDOJ CigFGIAVO) ClofGvDOJ COGSold CASE FACTS: 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). Ali inventory is in direct materials and finished goods at the end of each working day. There is no work-in- process 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 cach 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 88,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 S1,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 CASE FACTS: 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). Ali inventory is in direct materials and finished goods at the end of each working day. There is no work-in- process 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 cach 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 88,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 S1,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 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 S25 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) 1. Budgeted Income Statement (in dollars) Data_Sheet Master_Budget Sales Budget (Lots & $$) of Lots Product Price plot Revenue Orange Diet Orange Total Srey Raw Materials Usage Budget (Lots) Orange D. Orange Production RM per lot RM Usage Raw Material Prices plot ($$) Purchse Prplot OPGInvest Orange Syrup D. Orange Syrup Container Packaging Raw Materials Usage Budget ($$) Orange D. Orange Total RM Used Syrup Containers Packaging RM Used Cost Lots Prodced Direct Labor Budget (Hrs & SS) Product Orange D. Orange Labor Hrs Labor Cost Labor Rate p.tr Labor Hrs. p.Lot cost of Good Manufactured Orange Diet Orange FMOHSS FixedMOH (in) Product Orange D. Orange RM Used DL Cost VMOH COGM Allocable FMOH INCOME STATEMENT (Proforma, full) for the year ended December 2019, budgeted figures Sales Revenue COGS Gross Profit Admin. Exp Marketing Exp Distribute Exp Net Profit