Post with formulas please

The BestCrackers company produces and sells organic garlic crackers. The one- pound family size bag of crackers has two direct materials flour and packaging. The production process includes making, rolling and cutting dough, and baking the crackers. Indirect materials include a small amount of garlic powder, sugar, salt, butter, and olive oil. BestCrackers is preparing budgets for the fourth quarter ending December 31, 2021. For each requirement below prepare budgets by month for October, November, and December, and a total budget for the quarter. 1. The previous year's sales (2020/21) for the corresponding period were: October November December January February 60,520 bags 65,010 bags 81,170 bags 73,550 bags 62,300 bags The company expects the above volume of sales to increase by 10% for the period October 2021 February 2022. The budgeted selling price for 2021 is $8.50 per bag of crackers. The company expects 30% of its sales to be cash (COD) sales. The remaining 70% of sales will be made on credit. Prepare a Sales Budget for BestCrackers, 2. The company desires to have finished goods inventory on hand at the end of each month equal to 10 percent of the following month's budgeted unit sales. On September 30, 2021, BestCrackers expects to have 6,500 bags of crackers on hand. (Note: an estimate of sales in January is required in order to complete the production budget for December). Use the @ROUNDUP function to round up to the nearest whole number the number of cracker bags desired in ending inventory. Prepare a Production budget. 3. The final product (bags of crackers) requires two direct materials: flour and packaging Flour 1.5 pounds of flour are required for each bag of crackers. Management desires to have materials on hand (i.e., pounds of flour) at the end of each month equal to 20 percent of the following month's production needs. Use the @ROUNDUP function to round up to the nearest whole number the number of pounds of flour desired in ending inventory. The beginning materials inventory in October 2021, is expected to be 20,804 pounds. Flour costs $0.33 per pound. Flour is only allowed to be purchased in whole pounds, so use the @ROUNDUP function to round up to the nearest whole number the number of pounds to purchase). Packaging Packaging material is purchased by the roll and 25 bags of crackers are produced from each roll. The packaging is made from biodegradable, organic plant fiber that extends the shelf life of the crackers while preserving its freshness. Management desires to have packaging on hand at the end of each month equal to 15 percent of the following month's production needs. Use the @ROUNDUP function to round up to the nearest whole number the number of rolls desired in ending inventory. The beginning inventory of packaging (i.e., rolls of packaging material) in October 2021 is expected to be 355 rolls. Packaging is expected to cost $11 per roll. Use the @ROUNDUP function to round up to the nearest whole number the number of rolls to purchase. Prepare a Direct Materials budget. (Note: budgeted production in January is required in order to complete the direct materials budget for December. Because two direct materials are required for production flour and rolls of packaging - you will need a separate schedule for each direct material.) 4. Each bag of crackers requires 0.20 hours of direct labor. Each hour of direct labor costs the company $16. Prepare a Direct Labor budget. 5. BestCrackers budgets indirect materials (e.g., garlic powder, salt, oil) at $0.20 per bag. BestCrackers treats indirect labor and utilities as mixed costs. The variable components are $0.40 per bag for indirect labor and $0.20 per bag for utilities. The following fixed costs per month are budgeted for indirect labor, $15,000, utilities, $3,500, and other, $8,000. Prepare a Manufacturing Overhead budget. 6. Variable selling and administrative expenses are $1.10 per bag of crackers sold. Fixed selling and administrative expenses include administration ($38,000 per month) and marketing ($5,000 per month). Prepare an Operating Expenses budget. 7. Prepare a Budgeted Manufacturing Cost per unit budget. Refer to exhibit 9-11 for guidance. To calculate FMOH/unit calculate total FMOH for the year and divide this by budgeted production for the year. The total production volume for the year is budgeted at 775,000 bags. 8. Prepare a Budgeted Income Statement for the quarter for BestCrackers. Assume interest expense of $0, and income tax expense of 24% of income before taxes. BestCrackers's goal for the quarter is to make its net income greater than 7% of its sales revenue. To determine whether the company achieves the goal, use @IF function. In the IF function, you need to label Achieved if it achieves the goal (if the condition is met) or Not Achieved if it does not achieve (if the condition is not met). Use the CELL right next to Net Income' cell to make the IF function that returns one of the labels based on whether the condition (net income > sales revenue*7%) is met or not. Directions: Refer to Chapter 9 (The Master Budget) for guidance in setting up your budgets and schedules. Adapt your schedules for the specific details outlined in the requirements above. Prepare your budgets using Excel. Use formulas and cell references so that any change you make in one budget is carried through to all the budgets. There should be no hard keyed numbers in your formulas. For example, if you change the 'sales volume increase from 10% to 11% you should see effects of that change throughout the other budgets. Likewise, if the budgeted selling price changes from $8.50 to $9.50 your spreadsheet model should be able to quickly and easily accommodate this change, i.e., change the input cell for budgeted selling price and see the effect on income. The spreadsheet will be graded on presentation, correctness, and quality of your spreadsheet model (i.e., does it update correctly for changes in input variables). See the grading rubric on Canvas. You should approach this assignment as if you are the Management Accountant at the Best Crackers Company and you are going to present these budgets in a meeting to the CEO, CFO, and other management personnel. The BestCrackers company produces and sells organic garlic crackers. The one- pound family size bag of crackers has two direct materials flour and packaging. The production process includes making, rolling and cutting dough, and baking the crackers. Indirect materials include a small amount of garlic powder, sugar, salt, butter, and olive oil. BestCrackers is preparing budgets for the fourth quarter ending December 31, 2021. For each requirement below prepare budgets by month for October, November, and December, and a total budget for the quarter. 1. The previous year's sales (2020/21) for the corresponding period were: October November December January February 60,520 bags 65,010 bags 81,170 bags 73,550 bags 62,300 bags The company expects the above volume of sales to increase by 10% for the period October 2021 February 2022. The budgeted selling price for 2021 is $8.50 per bag of crackers. The company expects 30% of its sales to be cash (COD) sales. The remaining 70% of sales will be made on credit. Prepare a Sales Budget for BestCrackers, 2. The company desires to have finished goods inventory on hand at the end of each month equal to 10 percent of the following month's budgeted unit sales. On September 30, 2021, BestCrackers expects to have 6,500 bags of crackers on hand. (Note: an estimate of sales in January is required in order to complete the production budget for December). Use the @ROUNDUP function to round up to the nearest whole number the number of cracker bags desired in ending inventory. Prepare a Production budget. 3. The final product (bags of crackers) requires two direct materials: flour and packaging Flour 1.5 pounds of flour are required for each bag of crackers. Management desires to have materials on hand (i.e., pounds of flour) at the end of each month equal to 20 percent of the following month's production needs. Use the @ROUNDUP function to round up to the nearest whole number the number of pounds of flour desired in ending inventory. The beginning materials inventory in October 2021, is expected to be 20,804 pounds. Flour costs $0.33 per pound. Flour is only allowed to be purchased in whole pounds, so use the @ROUNDUP function to round up to the nearest whole number the number of pounds to purchase). Packaging Packaging material is purchased by the roll and 25 bags of crackers are produced from each roll. The packaging is made from biodegradable, organic plant fiber that extends the shelf life of the crackers while preserving its freshness. Management desires to have packaging on hand at the end of each month equal to 15 percent of the following month's production needs. Use the @ROUNDUP function to round up to the nearest whole number the number of rolls desired in ending inventory. The beginning inventory of packaging (i.e., rolls of packaging material) in October 2021 is expected to be 355 rolls. Packaging is expected to cost $11 per roll. Use the @ROUNDUP function to round up to the nearest whole number the number of rolls to purchase. Prepare a Direct Materials budget. (Note: budgeted production in January is required in order to complete the direct materials budget for December. Because two direct materials are required for production flour and rolls of packaging - you will need a separate schedule for each direct material.) 4. Each bag of crackers requires 0.20 hours of direct labor. Each hour of direct labor costs the company $16. Prepare a Direct Labor budget. 5. BestCrackers budgets indirect materials (e.g., garlic powder, salt, oil) at $0.20 per bag. BestCrackers treats indirect labor and utilities as mixed costs. The variable components are $0.40 per bag for indirect labor and $0.20 per bag for utilities. The following fixed costs per month are budgeted for indirect labor, $15,000, utilities, $3,500, and other, $8,000. Prepare a Manufacturing Overhead budget. 6. Variable selling and administrative expenses are $1.10 per bag of crackers sold. Fixed selling and administrative expenses include administration ($38,000 per month) and marketing ($5,000 per month). Prepare an Operating Expenses budget. 7. Prepare a Budgeted Manufacturing Cost per unit budget. Refer to exhibit 9-11 for guidance. To calculate FMOH/unit calculate total FMOH for the year and divide this by budgeted production for the year. The total production volume for the year is budgeted at 775,000 bags. 8. Prepare a Budgeted Income Statement for the quarter for BestCrackers. Assume interest expense of $0, and income tax expense of 24% of income before taxes. BestCrackers's goal for the quarter is to make its net income greater than 7% of its sales revenue. To determine whether the company achieves the goal, use @IF function. In the IF function, you need to label Achieved if it achieves the goal (if the condition is met) or Not Achieved if it does not achieve (if the condition is not met). Use the CELL right next to Net Income' cell to make the IF function that returns one of the labels based on whether the condition (net income > sales revenue*7%) is met or not. Directions: Refer to Chapter 9 (The Master Budget) for guidance in setting up your budgets and schedules. Adapt your schedules for the specific details outlined in the requirements above. Prepare your budgets using Excel. Use formulas and cell references so that any change you make in one budget is carried through to all the budgets. There should be no hard keyed numbers in your formulas. For example, if you change the 'sales volume increase from 10% to 11% you should see effects of that change throughout the other budgets. Likewise, if the budgeted selling price changes from $8.50 to $9.50 your spreadsheet model should be able to quickly and easily accommodate this change, i.e., change the input cell for budgeted selling price and see the effect on income. The spreadsheet will be graded on presentation, correctness, and quality of your spreadsheet model (i.e., does it update correctly for changes in input variables). See the grading rubric on Canvas. You should approach this assignment as if you are the Management Accountant at the Best Crackers Company and you are going to present these budgets in a meeting to the CEO, CFO, and other management personnel