The Como Cool Company Case StudyPart A$($i$)$You have just been hired as a new management accountant at Como Cool Company. You have been delegated responsibility for budgets preparation. Your first task is to prepare a master budget for two of Como Cools most popular soft drinks $-$ Coke $($i$.$e$.,$ Classic Coca$-$Cola$)$ and Diet Coke. You have gathered the following information.Como Cool Company bottles the two soft drinks at its Perth Plant. Bottling at this plant is a highly repetitive and automatic process. Empty bottles are removed from their carton, placed on a conveyor, and cleaned, rinsed, dried, filled, capped, and heated $($to reduce condensation$).$ Inventories include direct materials and finished goods inventories at the end of each working day. There is no work$-$in$-$process inventory.The two soft drinks bottled by Como Cool are Coke and Diet Coke. The syrup for both the soft drinks is purchased from Freshee Syrup Company. The syrup for the regular brand contains a higher sugar content than the syrup for the diet brand.Como Cool 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 $(1,000$ cases$)$ of beverage. The following purchase prices are forecast for direct materials for $2024($year ending $31$ December $2024)$:CokeDiet CokeSyrup$$1,200$ per lot$$1,100$ per lotContainers $($bottles$,$ caps, etc.$)$$$1,000$ per lot$$1,000$ per lotPackaging$ $800$ per lot$ $800$ per lotThe 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 coke and coke. Diet coke is always bottled first each day to reduce the risk of sugar contamination. The only difference in the bottling process for the two soft drinks is the syrup.Como Cool uses the first$-$in$,$ first$-$out $($FIFO$)$ method for costing all inventories. The company estimates income tax expense for $2024$ is based on $30\%$ of operating income before tax. The summary data used in developing budgets for the year $2024$ are shown as follows:$1.$ Sales Coke, $1,500$ lots at $$7,100$ selling price per lot Diet Coke, $600$ lots at $$7,500$ selling price per lot$2.$ Beginning $(1$ January $2024)$ inventory of direct materials Syrup for Coke, $75$ lots at $$1,200$ purchase price per lot Syrup for Diet Coke, $65$ lots at $$980$ purchase price per lot Containers, $180$ lots at $$850$ purchase price per lot Packaging, $370$ lots at $$910$ purchase price per lot$3.$ Beginning $(1$ January $2024)$ inventory of finished goods Coke, $80$ lots at $$2,500$ per lot Diet Coke, $45$ lots at $$3,900$ per lot$4.$ Target ending $(31$ December $2024)$ inventory of direct materials Syrup for Coke, $36$ lots Syrup for Diet Coke, $22$ lots Containers, $110$ lots Packaging, $220$ lots$5.$ Target ending $(31$ December $2024)$ inventory of finished goods Coke, $25$ lots Diet Coke, $15$ lots$6.$ Each lot requires $20$ direct manufacturing labour hours at the $2024$ budgeted rate of $$25$ per hour. Indirect manufacturing labour costs are included in the manufacturing overhead forecast$\mathrm{.}7.$ Variable manufacturing overhead is forecast to be $$550$ per hour of bottling time; bottling time is the time the filling equipment is in operation. It takes two hours to bottle one lot of Coke and two hours to bottle one lot of Diet Coke$\mathrm{.}8.$ Fixed manufacturing overhead is forecast to be $$1,350,000$ for $2024.$ Hours of budgeted bottling time are the sole cost allocation base for all fixed manufacturing overhead$\mathrm{.}9.$ Administration costs are forecast to be $5\mathrm{.}5\%$ of the cost of goods manufactured for $2024.$ Marketing costs are forecast to be $5\%$ of revenue for $2024.$ Distribution costs are forecast to be $4\mathrm{.}5\%$ of revenue for $2024\mathrm{.}10.$ Budgeted beginning balances on $1$ January $2024$: Account receivable $($from sales$)$$$650,000$ Accounts payable $($for direct materials$)$$$360,000$ Cash$$110,000$ Budgeted ending balances on $31$ December $2024$: Account receivable $($from sales$)$ $$700,000$ Accounts payable $($for direct materials$)$ $$420,000$REQUIRED You are required to submit a master budget, consisting of the following budgets, in an Excel spreadsheet to your divisional manager $($in the order presented below$).$ Sales budget Production budget Direct materials usage budget Direct materials purchase budget Direct manufacturing labour budget Manufacturing overhead costs budget Cost of goods sold budget Marketing, distribution, and administration costs budgets Budgeted income statement Part A$($ii$)$You have prepared and submitted the above master budget to your divisional manager. However, a recent research report warned that drinking large amounts of sugary beverages such as soft drinks could increase the risk of gaining weight and developing Type $2$ diabetes, heart disease, and gout. The report further revealed that children who drink sugary beverages have almost double the risk of dental cavities. As a result, the sales demand for soft drinks is affected. The Marketing Department of Como Cool Company has just revised the sales forecast $($i$.$e$.,$ demand and selling prices$)$ for the two soft drinks, and its marketing and distribution costs as shown:Sales:Coke, $1,200$ lots at $$6,750$ selling price per lotDiet Coke, $525$ lots at $$6,900$ selling price per lotMarketing and Distribution Costs:Note that marketing and distribution costs are forecast to be $6\%$ of revenues for $2024.$ REQUIREDYou are required to re$-$submit a revised master budget, consisting of the same budgets in Requirement Part A$($i$)$ in an Excel spreadsheet to your divisional manager. You are also required to provide a half$-$page Executive Summary to your divisional manager, highlighting what are the major impacts of the revised budget.