uluuLLI Iauul nnu $0.50 pct umpmy cupuumu nun uuuucs. \"n. .uuuwmg uALu Luain per month are budgeted for indirect labor. $50,000. utilities. $10,000. and other, 5365.800. Prepare 2 Manufacturing Overhead budget. 6. Variable selling and administrative expenses are $100.50 per display cupboard sold. Fixed selling and administrative expenses are 580.000 per month. These costs are not itemized. i.e.. the budget has only two line items 7 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-1 1 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 50.000 display cupboards. 8. Prepare a Budgeted Income Statement for the quarter for DecorIdeas. Assume interest expense of $0. and income tax expense of 24% of income before taxes. Decorldeas's goal for the quarter is to make its net income greater than 10% ofits sales revenue. To determine whether the company achieves the goal. use @IF function. In the 1P 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 ofthe 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 specic 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, ifyou change the 'sales volume increase' from 15% to 12% you should see effects of that change throughout the other budgets. Likewise. ifthe budgeted selling price per display cupboard changes from $785 to $790 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 Decorldeas 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 7 e.g.. selling price. budgeted volume increase. feet per display cupboard. ending inventory percentage. etc. You may use the \"Assumptions\" tab ofthe sample spreadsheet or a designated area within your budget spreadsheet. as long as the input area is clearly labeled and neatly organized . 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 ofgood spreadsheet design is to keep just one copy ofeach 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 of24% 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: 7subtota1*E5 Don't: subtotal*24\"/a 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 7 e.g.. use $ to format dollar amounts, format cells for decimal places (two decimal places). use a comma (.) for a thousands- separator. etc... Submission Requirements: l. Decorldeas Budget Project ACC 202 Fall 2022 Due Thursday, November 17 The Decorldeas Company manufactures hand-crafted natural wood display cupboards. Currently the company makes only one size of display cupboard. which is 60-inch height. 48-inch width. and 19inch depth; and has 6 shelves. The nal product consists of a routed. sanded. assembled. and stained oak wood cupboard. Direct materials include oak shelving wood and hickory plywood backing board. Other materials. such as wood screws. sand paper. stain, wood glue. and packaging. are treated as indirect materials. Decorldeas is preparing budgets for the fourth quarter ending December 31, 2022. For each requirement below prepare budgets by month for October. NOL'WHbe'I'. and December and a ram! budget for the quarter. The previous year's sales (2021/22) for the corresponding period were: October 2.000 display cupboards November 2.800 display cupboards December 4.000 display cupboards January 4.400 display cupboards February 3.600 display cupboards The company expects the above volume of display cupboard sales to increase by 15% for the period October 2022 7 February 2023. The budgeted selling price for 2022 is $785.00 per display cupboard. The company expects 5% of its sales to be cash (COD) sales. The remaining 95% of sales will be made on credit. Prepare a Sales Budget for Decorldeas. The company desires to have finished goods inventory on hand at the end of each month equal to 15 percent ofthe following month's budgeted unit sales. On September 30. 2022. there were 450 display cupboards on hand. (Note. an estimate of sales in January is required in order to complete the production budget for December). Prepare a Production budget. The display cupboards require two direct materials: oak shelving wood and hickory plywood backing board. Oak shelving wood: Thirty (30) feet of 12X] oak shelving wood are required for each cupboard produced. Management desires to have materials on hand at the end of each month equal to 10 percent of the following month's cupboard production needs. The beginning inventory of oak wood. October 2022. was 10.150 feet ofwood. Oak wood is expected to cost $5.00 per foot. Hickory plywood backing boartL Hickory plywood backing board is purchased by the sheet and 2 backing boards can be cut from each sheet. Management desires to have plywood on hand at the end of each month equal to 15 percent ofthe following month's production needs. The beginning inventory of plywood. October 2022. was 100 sheets. Hickory plywood is expected to cost $45 per sheet. (Note. budgeted production in January is required in order to complete the direct materials budget for December. Also. use the @ROUND function to round up to the nearest whole number the number of sheets of plywood to purchase \"DM quantities to purchase\"). Prepare a Direct Materials budget. Also. because two direct materials are required for production - oak wood and hickory plywood you will need a separate schedule for each direct material. Each display cupboard requires 10 hours ofdirect labor. Decorldeas uses a series of table saws. table routers and sanders set up for specialized operations to achieve production efciencies. Direct labor costs the company $22 per hour. Prepare a Direct Labor budget. Decorldeas budgets indirect materials (e.g.. wood screws. sandpaper. stain. packaging) at $25.50 per display Clipboard. Decorldeas treats indirect labor and utilities as mixed costs. The variable components are 5 10.40 per display cupboard for indirect labor andmowing xed costs per month are bu ge e or in irec a or. . . u l 1 ms. .000. and other