G$.$ Garfield and G$.$ Foreman are considering a partnership in an outdoor grill company named Wild Grill Innovations. G$.$ Garfield seeks funding for his capital contribution, and potential investors require budgets and forecasts for the company's revenue and expenses. G$.$ Foreman's finance department is tasked with creating a monthly forecast budget for G$.$ Garfield, along with an annual forecast and budget. As one of G$.$ Foreman's employees, you are responsible for preparing the monthly budget for May $2024.$PURPOSE: The purpose of this assignment is to develop essential budgeting and forecasting skills within the context of a business partnership venture. By creating monthly budgets for May $2024$ for "Wild Grill Innovations," practical experience in budget preparation will be gained. Through this hands$-$on exercise using Google Sheets analytical abilities, financial acumen, and spreadsheet proficiency skills will be strengthened which are essential for effective decision$-$making and strategic planning in real$-$world business scenarios. Additionally, by calculating key financial metrics such as cost of goods sold $($COGS$)$ per unit and overall, a comprehensive understanding of the financial implications of business operations and the importance of accurate budgeting in driving business success will be derived. This assignment also provides an opportunity to determine the appropriate budget layout to effectively communicate financial information and facilitate decision$-$making processes within the organization.Requirements: Create each of the budgets below in Google Sheets. $100$ points$\mathrm{.}1.$ Sales Budget:a$.$ Include a sales budget detailing expected sales by product and territory. i$.$ There should only be one sales budget that includes all the territories. b$.$ Provide a schedule of expected cash collection$\mathrm{.}2.$ Production Budget: a$.$ Create a single production budget outlining the production requirements for each product line based on the sales forecast$\mathrm{.}3.$ Direct Materials Budget: a$.$ Prepare a single direct materials budget, that itemizes all production needs and expected cash disbursements for each of the raw materials required in production$\mathrm{.}4.$ Direct Labor Budget:a$.$ Develop a single direct labor budget, specifying labor requirements and associated costs for the three product line$\mathrm{.}5.$ Manufacturing Overhead $($MOH$)$ Budget:a$.$ Construct a single MOH budget, detailing overhead costs and cash disbursements$\mathrm{.}6.$ Selling & Administrative $($S&A$)$ Budget:a$.$ Compile a single S&A budget, including cash disbursements for selling and administrative expenses$\mathrm{.}7.$ Budgeted Income Statement:a$.$ Prepare a single budgeted income statement, illustrating the calculation of cost of goods sold $($COGS$)$ per unit per product line.b$.$ A single cost of goods sold $($COGS$)$ number should appear in the Budgeted Income Statement.i$.$ Hint, to calculate a single COGS number the following information must be known and should be shown in your Google Sheet$\mathrm{.}1.$ Value Grill Cost Per Unit and number of Value Grill units sold$2.$ Backyard Cost Per Unit and number of Backyard units sold$3.$ Master Grill Cost Per Unit and number Master units sold$8.$ Cash Budget:a$.$ Develop a single cash budget, projecting cash inflows and outflows for the month of May $2024.$ Google Sheet Instructions & Set$-$Up $(25$ points$)$ Each budget component will appear in an individual worksheet with each worksheet tab labeled with the appropriate Budgets Name. You will determine the appropriate layout of the budgets. Recommend using your textbook and supplemental lecture notes as a guide. o A rule of thumb is there should be limited repeated lines and columns. o Ensure a manager can quickly scan a budget to grasp the necessary data. Column Headings:o Label the columns in each worksheet with descriptive headings.o Include columns for relevant categories such as units, prices, costs, totals, and cash inflows$/$outflows$.$ Data Input:o Enter the provided data accurately into the designated cells.o Organize the data logically$,$ grouping related information together for clarity. Formulas and Calculations:o Utilize Excel formulas to perform calculations wherever possible. Extra Credit awarded!o Ensure formulas are correctly applied to calculate totals, subtotals, and any derived values.o Double$-$check formulas for accuracy and adjust cell references as necessary. Formatting:o Apply consistent formatting throughout the workbook for uniformity and readability.oo Use appropriate number formatting $($e$.$g$.,$ currency, percentage$)$ for numerical data.o Format headers, titles, and important data points to make them stand out. Documentation and Comments:o Provide explanations or documentation where necessary to clarify assumptions or methodologies used in the budget.o Add comments to cells or sections to provide additional context or instructions for users. Rounding:o All dollar amounts should be rounded to two decimalso For units or production in the Production budget, units should be rounded to the nearest whole unit, using the normal rules of rounding. EXTRA CREDIT up to $24$ POINTS! Complete all calculations WITHIN Google Sheets using formulas. $3$ points earned for each budget that contains a minimum of $5$ formulas. o The exception is the S&A Budget. Only $4$ formulas are required. The case is to be completed in teams or individually. You may ask Sheila, other instructors, or tutors for help or clarification but collaborating with other students outside of your group is not permitted. Any evidence of collaboration will result in a zero for the assignment and possible withdraw from the class. The project will be worth $12\mathrm{.}5\%$ of your final grade.Wild Grill Innovations DataSelected information concerning sales and production for May $2024$ is summarized as follows:a$.$ The sales budget must be divided up by product $($Backyard and Master$).$ Estimated sales for May by sales territory:Indiana:Value Grill$188$ units @ $$465$ per unitBackyard $100$ units at $$775$ per unitMaster $200$ units at $$1,550$ per unitOhio:Value Grill$199$ units @ $$558$ per unitBackyard $345$ units at $$855$ per unitMaster $215$ units at $$1,777$ per unitMichigan:Value Grill$301$ units @ $$422$ per unitBackyard$175$ units @ $$888$ per unitMaster$199$ @ $$1600$ per unit Estimated sales for June for all territories:Value Grills $1,182$ grillsBackyard Grills $1,258$ grillsMaster Grills$1,173$ grills Wild Grill Innovations has a beginning accounts receivable balance of $$2,450,000$ in the month of May and expects to collect $40\%$ of that balance. In addition, their accounting department has estimated that $60\%$ of sales for the Master Grill will be paid in cash and $70\%$ of the Backyard grill will be paid in cash and $90\%$ of the Value Grill will be paid in cash. b$.$ Inventory of GrillsBeginning Inventory of Grills:Value Grill$240$ unitsBackyard $360$ unitsMaster $180$ units Wild Grill Innovations has established a new ending inventory policy to take effect the month of May. From May and going forward, the ending inventory should be: Ending Inventory:Value Grill$6\%$ of Junes salesBackyard $4\%$ of Junes salesMaster $2\%$ of Junes sales.Please round to the nearest whole until using the normal rules of rounding. c$.$ Estimated direct material inventories at May $1$: Direct materials available in the warehouse:Cast Iron Grates$300$ lbsSteel$1,800$ lbs$.$Burner subassemblies$150$ unitsAluminum shelves$300$ unitsPlastic$400$ lbsThermometers and Gauges$200$ unitsDesired ending quantity of direct materials in the warehouse on May $30$th : Cast Iron Grates?Steel?Burner subassemblies?Aluminum shelves?Plastic?Thermometers and Gauges?Desired ending inventory is based material needed for the number of grills calculated as ending inventory in the production budget. Value Grill$6\%$ of Junes sales Backyard $4\%$ of Junes sales Master $2\%$ of Junes sales. d$.$ Direct materials used in production:Direct Materials required to produce one Value Grill:Cast Iron Grates$2$ units per unit of productSteel$18$ lbs$.$ per unit of productBurner subassemblies$1$ units per unit of productAluminum Shelves$2$ units per unit of productPlastic$10$ lbs per unit of productThermometers and Gauges$1$ unit per unit of product Direct Materials required to produce one Backyard Chef Grill:Cast Iron Grates$3$ units per unit of productSteel$24$ lbs$.$ per unit of productBurner subassemblies$2$ units per unit of productAluminum Shelves $4$ units per unit of productPlastic$6$ lbs per unit of productThermometers and Gauges$2$ units per unit of productDirect Materials required to produce one Master Chef grill:Cast Iron Grates$6$ units per unit of productSteel$42$ lbs$.$ per unit of productBurner subassemblies$4$ units per unit of productAluminum Shelves$5$ units per unit of productPlastic$4$ lbs per unit of productThermometers and Gauges$4$ units per unit of productAnticipated purchase price for direct materials: Cast Iron Grates$$21$ per unitSteel$$10\mathrm{.}50$ per lb$.$Burner subassemblies$$173$ per unitAluminum Shelves$$12$ per unitPlastic$$1\mathrm{.}56$ per lb$.$Thermometers and Gauges$$3\mathrm{.}37$ per unit Because of their excellent relationships with the vendors, they pay the following percentages when placing their order: Cast Iron Grates, Plastic and Thermometers and Gauges: $10\%$Steel and Burner subassemblies: $25\%$Aluminum Shelves $2\%,$ if pay the entire balance within $35$ days. e$.$ Direct labor requirements for one grill:Value Grill:Stamping Department$0\mathrm{.}70$ hr$.$ at $$21$ per hr$.$Forming Department$0\mathrm{.}95$ hr$.$ at $$17$ per hr$.$Assembly Department$1\mathrm{.}7$ hrs$.$ at $$14$ per hr$.$ Backyard Chef:Stamping Department$0\mathrm{.}60$ hr$.$ at $$21$ per hr$.$Forming Department$0\mathrm{.}80$ hr$.$ at $$17$ per hr$.$Assembly Department$2\mathrm{.}0$ hrs$.$ at $$14$ per hr$.$ Master Chef:Stamping Department$\mathrm{.}80$ hr$.$ at $$21$ per hr$.$Forming Department$1\mathrm{.}50$ hrs$.$ at $$17$ per hr$.$Assembly Department$2\mathrm{.}50$ hrs$.$ at $$14$ per hr$.$ f$.$ Wild Grill Innovations budgeted the following fixed costs for anticipated production in May. The list below includes fixed MOH expenses and Fixed S&A expenses. Please categorize the fixed cost as MOH or S&A $($Period$)$ prior to completing any calculations. Advertising Expenses $$24,000$Sales monthly salary $($not commission$)$ $$7,500$Factory Insurance $$2,000$Production Supervisor Salary $$9,900$Executive officer salaries $$25,000$Indirect Labor Salaries for Quality Control $$2,800$Factory Depreciation $$4,000$Corporate Office Building Depreciation $$3,100$ FMOH is assigned to each product line $($Value$,$ Backyard and Master$)$ using a PDOR and Direct Labor Hours as the activity driver. g$.$ Total estimated VMOH expenses are $$41,000$ for the month of May. VMOH expenses are allocated to the products based on a single PDOR rate using the allocation base, TOTAL DIRECT LABOR HOURS. Requirement: Please calculate a PDOR for the company using Total Direct Labor hours. Hint, you will only have one PDOR for the company. There will NOT be three separate PDORs for the Value, Backyard and Master product. Variable S&A expenses are allocated at rate of $$3$ per grill. h$.$ Other Information:a$.$ Income Tax Rate is $25\%$b$.$ Minimum Cash Balance $$500,000$c$.$ Beginning Cash Balance $$750,000$Grading RubricBudgetPointsSales Budget$12$ pointsProduction Budget$12$ pointsDirect Material Budget$24$ pointsDirect Labor Budget$12$ pointsMOH Budget$16$ pointsS&A Budget$4$ pointsCost Per Unit & COGS calculation$8$ pointsIncome Statement$8$ pointsCash Budget$4$ pointsBudget Layouts$25$ pointsTotal$125$ points $2$