Please answer all and provide the formulas used in Excel, Thank you (:

The Pickachip Company produces and sells organic plain potato chips. The one- pound family size bag of chips has two direct materials potatoes and packaging. Pickachip is preparing budgets for the second quarter ending June 30, 2022. For each requirement below prepare budgets by month for April, May, and June, and a total budget for the quarter. 1. The previous years sales (2021) for the corresponding period were: April 85,000 bags May 90,000 bags June 100,000 bags July 95,000 bags August 100,000 bags The company expects the above volume of sales to increase by 10% for the period April 2022 August 2022. The budgeted selling price for 2022 is $11.50 per bag of chips. The company expects 10% of its sales to be cash (COD) sales. The remaining 90% of sales will be made on credit. Prepare a Sales Budget for Pickachip. 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. On March 31, 2022, Pickachip expects to have 10,285 bags of chips on hand. Prepare a Production budget. 3. The final product (bags of chips) requires two direct materials: potatoes and packaging. Potatoes 4 pounds of raw potatoes are required for each one-pound bag of potato chips. Management desires to have materials on hand (i.e., pounds of potatoes) at the end of each month equal to 20 percent of the following month's production needs. The beginning materials inventory, April 2022, is expected to be 75,284 pounds. Potatoes cost $1.20 per pound. Packaging Packaging material is purchased by the roll and 50 bags of chips are produced from each roll. The packaging is made from biodegradable, organic plant fiber that extends the shelf life of the potato chips while preserving its freshness. Management desires to have packaging on hand at the end of each month equal to 12 percent of the following month's production needs. The beginning inventory of packaging (i.e.,

rolls of packaging material) in April 2022 is expected to be 226 rolls. Packaging is expected to cost $14 per roll. Note, budgeted production in July is required in order to complete the direct materials budget for June. Also, use the @ROUNDUP function to round up to the nearest whole number rolls of packaging material to purchase (DM units to purchase for packaging). Further, because two direct materials are required for production potatoes and rolls of packaging - you will need a separate schedule for each direct material. Prepare a Direct Materials budget. *** This assignment requires you to prepare budgets for the second quarter (April, May, and June). Thus, you do not need to make budgets for July and August except for Sales, and Production and DM budgets. You need some information from July and August to complete June budget schedule in Production budget and DM budget. However, there is not enough information to complete August in Production and July and August in DM budgets, so please fill out July and August as much as you can with given information and let them have blanks (incomplete), because you just need partial info from those months for June budget (i.e., to calculate Junes ending inventory). 4. Each bag of chips requires 0.12 hours of direct labor. Each hour of direct labor costs the company $18. Prepare a Direct Labor budget. 5. Pickachip budgets indirect materials (e.g., salt, oil) at $0.06 per bag. Pickachip 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, $25,000, utilities, $5,000, and other, $12,000. Prepare a Manufacturing Overhead budget. 6. Variable selling and administrative expenses are $1.20 per bag of chips sold. Fixed selling and administrative expenses are $45,000 per month. These costs are not itemized, i.e., the budget has only two line items variable operating expenses and fixed operating expenses. 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 900,000 bags. 8. Prepare a Budgeted Income Statement for the quarter for Pickachip. Assume interest expense of $0, and income tax expense of 22% of income before taxes. Pickachips goal for the quarter is to make its net income greater than 10% 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*10%) 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 $11.50 to $12.00 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 Pickachip 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 bag, 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 years 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 (two decimal places), use a comma (,) for a thousands- separator, etc...

B21 fx E F $ HREE A B D 4 5 Sales Budget Assumptions 6 Unit Price 15.00 7 Percentage of Cash Sale 35% 8 Percentage of Credit sale 65% 9 Expected sales increase 8% 10 11 2019 Sales 12 April 50,000 bags 13 May 55,000 bags 14 June 90,000 bags 15 July 75,000 bags 16 August 60,000 bags 17 18 19 Production Budget Assumptions 20 21 22 23 Direct Materials Budget Assumptions 24 25 26 27 Direct Labor Budget Assumptions 28 29 30 31 Manufacturing Overhead Budget Assumptions 32 33 34 35 Operating Expense Budget Assumptions 36 37 38 39 Budgeted Manufacturing Cost Per Unit Budget Assumptions 40 41 D H L 1 GBCoffee Company 2 2020 Budget Assumptions 3. Prepared by: Student Name April May June Quarter July August NO April Mar Quarter August 54,000.00 59,400.00 $ 15.00 $ 15.00 $ 810,000.00 $831,000.00 97,200.00 210,600.00 $ 15.00$ ######## ##### 15.00 81,000.00 64,800.00 $ 15.00$ 15.00 $1215,000.00 $372,000.00 35% $283,500.00 $ 311,850.00 $ 510,300.00 $1,105,650.00 65% 526,500.00 579,150.00 947,700.00 2.053.350.00 $ 810,000.00 $ 891,000.00 $ 425,250.00 $340,200.00 789,750.00 631.800.00 $1,215,000.00 $372,000.00 ######## ######## April Mar June Quarter July August 8 Sales Budget Unit Sales 10 Unit Price 11 Sales Revenue 12 13 Cash Sale 14 Credit Sale is Total 16 17 18 Production Beddet 19 Unit colep 20 Desired ending inventory 21 Total needed 22 Beginning inventory 23 24 2s 26 27 Direct Material Budget 28 April Mar June Quarter July August April May June 30 31 32 Direct Labor Budget 33 Quarter 35 36 37 MOH Budget April Hay June Quarter 39 40 April May Quarter 42 Operating Expense Budget 45 47 Badget Manufacturing Cost per Unit SO 5. Bedget lacone Statencat 52 for the Quarter ended June 30) 53 9998 REASSE 55 56 57 58 63