The PecansPlus Company produces and sells chocolate bars filled with pieces of pecans. The 5-oz chocolate bar has two direct materials: 100% organic cacao beans and 100% organic pecans. The production process includes creating the chocolate from scratch using the cacao beans (along with other ingredients) and chopping the pecans into the pieces that fill the chocolate bar. Indirect materials include very minimal amounts of sugar, milk, and salt, in addition to some packaging materials. PecansPlus is preparing budgets for the 3rd quarter ending September 30, 2023. For each requirement below prepare budgets by month for July, August and September, and a total budget for the quarter. 1. The previous year's sales (2022) for the corresponding period were: The company expects the above volume of bar sales to increase by 5% for the period July 2023 - November 2023. The budgeted selling price for 2023 is $8.00 per chocolate bar. The company expects 90% of its sales to be cash (COD) sales. The remaining 10% of sales will be made on credit. Prepare a Sales Budget for PecansPlus. 2. The company desires to have finished goods inventory on hand at the end of each month equal to 11 percent of the following month's budgeted unit sales. The beginning inventory, in July 2023, is expected to be 3,581 chocolate bars on hand. (Note: an estimate of sales in October is required in order to complete the production budget for September). Use the @ROUND function to round to the nearest whole number the number of chocolate bars desired in ending inventory. Prepare a Production budget. 3. The chocolate bars require two direct materials: Cacao Beans and Pecans. Cacao Beans Each chocolate bar requires 0.40 pounds (Ibs) of Cacao beans. Management desires to have materials on hand at the end of each month equal to 12 percent of the following month's chocolate bar production needs. Use the @ROUND function to round to the nearest whole number the number of pounds of cacao beans desired in ending inventory. The beginning inventory of cacao beans, in July 2023, is expected to bc 1,489 pounds. Cacao beans are expected to cost $7 per pound. (Note: budgeted production in October is required in order to complete the direct materials budget for September). Our supplier only allows purchases in whole pounds, so use the (a)OUND function to round to the nearest whole number the number of pounds to purchase. Pecans Each chocolate bar also requires 0.08 pound of pecans. Management desires to have pecans on hand at the end of each month equal to 10 percent of the following month's production needs. Use the @ROUND function to round to the nearest whole number the number of pounds of pecans desired in ending inventory. The beginning inventory, in July 2023, is expected to be 249 pounds of Pecans. Pecans are expected to cost $10 per pound. (Note: budgeted production in October is required in order to complete the direct materials budget for September). Use the @ROUND function to round to the nearest whole number the number of pounds of pecans to purchase. Prepare a Direct Materials budget. Also, because two direet materials are required for production - cacao beans and pecans - you will need a separate schedule for each direct material. 4. Each chocolate bar requires 0.02 hours of direct labor. Direct labor costs the company $18 per hour. Prepare a Direct Labor budget. 5. PecansPlus budgets indirect materials (e.g., sugar, salt, packaging materials) at $0.21 per chocolate bar. Other variable components are $0.11 per bar for indirect labor and $0.13 per bar for utilities. The following fixed costs per month are budgeted for indirect labor, $10,000, depreciation, $8,000, and other, $4,000. Prepare a Manufacturing Overhead budget. 6. Variable selling and administrative expenses consist of outward freight ( $100 per 1,000 chocolate bars) and sales commission (5 percent of the selling price per bar). Fixed selling and administrative expenses include administration (\$20,000 per month) and marketing ($30,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 1,000,000 chocolate bars. 8. Prepare a Budgeted Income Statement for the quarter for PecansPlus. Assume interest expense of $0, and income tax expense of 18% of income before taxes. PecansPlus has set a goal for the quarter of achieving its net income greater than 14% 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 interest expense of $0, and income tax expense of 18% of income before taxes. PecansPlus has set a goal for the quarter of achieving its net income greater than 14% 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*14\%) is met or not. BCoife Company 020 Budget Assumptions repared by; Student Name Production Budget Assumptions Direct Materials Budget Assumptions Direct Labor Budget Assumptions Manufacturing Overhead Budgot Assumptions Operating Expense Budget Assumptions