Homework attacthed
French Colas_Macc_PRN 1 EXCEL BUDGET CASE ASSIGNMENT ACCTG 404 Instructions: 1. This is an individual product; each participant must upload into the Dropbox on CANVAS by the due date ONE Excel file with all the required budgets listed at the end of this assignment. 2. Grades are based on the accuracy of the calculations, the readability of the budgets; and the degree to which they are interlinked. Except last minute changes to the data 3. NOTE: DO NOT hardcode ANY numbers in your master budget! All calculations should be hardcoded to the SEPARATE Excel Data Sheet. Several variables will be changed the night before submission to check that calculations are accurately cell referenced and interlinked. Points will be deducted in the event these directions are not followed. All numbers should be derived from cell references to either the data sheet, or the master budgets within your worksheet. 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). All 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 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) Data_Sheet Data Sheet for Excel Budget Sheet OrangeDiet OrangeDirect Manufacturing Labor Hours Direct Materials CostsHours per lot Syrup (per lot)Charge per hour Containers (per lot) Packaging (per lot)Variable Manufacturing Overhead Sales Budgeted AmountsHours per lot Sales QuantityCharge per hour Selling price (per lot) Beginning Inventory Raw MaterialsFixed Manufacturing Overhead Syrup QuantityTotal Fixed Manufacturing Overhead Syrup Purchase Price (per lot) Containers tc={DE1DC3C9-CD8A-40AE-AE4A-DA7CD6368C06}: [Threaded comment] Your version of Excel allows you to read this threaded comment; however, any edits to it will get removed if the file is opened in a newer version of Excel. Learn more: https://go.microsoft.com/fwlink/?linkid=870924 Comment: Enter container information into E ColumnAdmin. Costs Estimation of COGM Containers Purchase Price (per lot)Marketing Costs Estimationof dollar sales Packaging tc={2CDE8D3E-8131-4C9C-86B8-8FC24B48BEE9}: [Threaded comment] Your version of Excel allows you to read this threaded comment; however, any edits to it will get removed if the file is opened in a newer version of Excel. Learn more: https://go.microsoft.com/fwlink/?linkid=870924 Comment: Add packaging information into E columnDistribution Costs Estimation of dollar sales Packaging Purchase Price (per lot) Beginning Inventory Finished Goods Lot quantity Price per lot Ending Inventory Raw Materials Syrup Quantity Containers tc={4E24D0CB-8D9B-4046-B40B-08346C518F60}: [Threaded comment] Your version of Excel allows you to read this threaded comment; however, any edits to it will get removed if the file is opened in a newer version of Excel. Learn more: https://go.microsoft.com/fwlink/?linkid=870924 Comment: Enter Container Information into E column Packaging tc={D24F529A-D133-44A2-8B09-257E1AB07A6F}: [Threaded comment] Your version of Excel allows you to read this threaded comment; however, any edits to it will get removed if the file is opened in a newer version of Excel. Learn more: https://go.microsoft.com/fwlink/?linkid=870924 Comment: Enter Packaging Information into column E tc={DE1DC3C9-CD8A-40AE-AE4A-DA7CD6368C06}: [Threaded comment] Your version of Excel allows you to read this threaded comment; however, any edits to it will get removed if the file is opened in a newer version of Excel. Learn more: https://go.microsoft.com/fwlink/?linkid=870924 Comment: Enter container information into E Column tc={2CDE8D3E-8131-4C9C-86B8-8FC24B48BEE9}: [Threaded comment] Your version of Excel allows you to read this threaded comment; however, any edits to it will get removed if the file is opened in a newer version of Excel. Learn more: https://go.microsoft.com/fwlink/?linkid=870924 Comment: Add packaging information into E columnEnding Inventory Finished Goods Lot quantity Master_Budget Sales Budget (Lots & $$)Production Budget in Lots Product# of LotsPrice pLotRevenueOrangeD.Orange OrangeBeg. FG Inv Diet OrangeSales Total SrevClg. FG Inv Production Raw Materials Usage Budget (Lots)Raw Materials Purchases Budget (Lots) OrangeD. OrangeOrange SrpOrangeD.Orange Production QBeg. RM Inv RM per lotRM Used RM Usage QtyClg. RM Inv Syrup Purch Raw Material Prices pLot ($$)Raw Materials Purchases Budget (Lots) PurchsePrpLotOPGInvCstContainersAll OJPackinAll OJ Orange SyrupBeg. RM InvBeg. RM Inv D.Orange SyrupRM UsedRM Used ContainerClg. RM InvClg. RM Inv PackagingCntnr Purch0Packin Purch Raw Materials Usage Budget ($$)Raw Materials Purchases Budget ($$) OrangeD.OrangeTotal RM UsedAll OJ SyrupSyrup ContainersContainers PackagingPackaging RM Used CostRM PurchCst Direct Labor Budget (Hrs & $$)Variable Manufacturing Overhead ($$) Product#Lots ProdcedLabor HrsLabor CostProductBottleHrs pLOTTotlBtl HrsVMOH $$ OrangeOrange D.OrangeD.Orange Labor Rate p.HrCost of Good ManufacturedVMOH Rate Labor Hrs. p.LotOrangeDiet Orange RM Used FixedMOH (in $$)DL CostAdmin. Market. Distribte Budgets ($$) ProductFMOH $$VMOHAdmin 10% COGM OrangeFMOHMarketing12% SALES D. OrangeCOGMDistribution8% SALES TOTAL Allocable FMOH INCOME STATEMENT (Proforma, full)COST OF GOOD SOLD STATEMENT (proforma, full) for the year ended December 2019, budgeted figuresfor the year ended December 2019, budgeted figures pLot Price (except for COGM) Sales RevenueCOGM COGSOpFGInvOJ Gross ProfitOpFGInvDOJ Admin. ExpClgFGInvOJ Marketing ExpClgFGInvDOJ Distribute ExpCOGSold Net Profit