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 (lbs) 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 be 1,489 pounds. Cacao beans are expected to cost $7 per pound. (Note: budgeted 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 direct 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 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 5% to 7%, then you should see effects of that change throughout the other budgets. Likewise, if the budgeted selling price per bar changes from $8.00 to $9.00, then 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 PecansPlus company and you are going to present these budgets in a meeting to the CEO,CFO, and other management personnel. Some general principles to follow in constructing your Excel spreadsheet model: 1. Prepare an input area in which you enter all input variables - e.g., selling price, budgeted volume increase, pounds per bar, ending inventory percentage, etc. You may use the "Assumptions" tab of the sample spreadsheet or a designated area within your budget spreadsheet, as long as the input area is clearly labeled and neatly organized. 2. Each schedule should refer to the input area for each constant data value (see sample spreadsheet file). To the extent possible, keep all constant values together in one area of the worksheet. An important principle of good spreadsheet design is to keep just one copy of each constant value. That is, enter a constant value in only one location in the worksheet. Then if you use the value in another cell, use a cell reference that refers to the constant value's unique location. Example (hypothetical): You enter the constant value of 6% for sales tax in cell E5. When you write a formula in your worksheet that requires sales tax, reference E5 in the formula instead of "hard coding" in the 6% value. Do: = subtotal E5 Don't: = subtotal 6% 3. Use cell references for constant data values and to calculate formulas within your spreadsheet. There should be no hard-keyed numbers in your formulas. For example, the formula to determine current period sales in units should reference an input cell with last year's sales volume and a cell with the volume percentage increase. 4. Label and format appropriately - e.g., use $ to format dollar amounts, format cells for decimal places, etc