Chapter 21 Budgeting Excel Exercise Your unique assignment is provided by email sent from Dr. Christine Jonick to each student. Objectives/Skills. The Learning Objectives of the Assignment are to: Illustrate in Excel how to calculate budget variables for a manufacturing business; use an analytical tool to perform accounting analyses. The Desired Learning Outcome is to: reinforce student learning on calculating budget variables, presented in Chapter 21 of the Textbook, in an Excel worksheet using various formulas to make such computations. Student Engagement: Peer to peer learning and sharing knowledge is encouraged for this self-study exercise. Requirements. All of Katherine Morris' ACCT 2102 students are required to take this Excel exercise. The assignment is required to be submitted in D2L on or before Friday, April 22, 2022. This exercise provides a review of concepts introduced in CH 21 and will assist students in studying for Exam 4 on CH 21, 22 & 24.) Assignment Instructions: Step 1 - Read and study the attached "Excel Spreadsheet Basics for ACCT 2102 Formula Assignments" instructions. (You may want to download and save this document for future reference in your other business courses.) The instructions explain to you how to navigate and input formulas in Excel. Step 2 - Download and Save the attached Excel spreadsheet as follows: Lastname_Firstinitial_CH21_Budgeting. For example, Morris_K_CH21_Budgeting. (This is your unique spreadsheet that has been algorithmically adjusted so that each student can complete the spreadsheet resulting in different answers in different cells.) Step 3 - Complete the open required cells by entering the formulas is the appropriate cell (Refer to Step 1 and use the "Excel Spreadsheet Basics" instructions to input the formula.) Input formulas or amounts - e.g., to display the correct amounts of debits and credits in the journal entries. Students are permitted to discuss the exercise with a classmate and share knowledge by discussing the ideas learned. However, students are required to submit their own work. Step 4 -Submit your saved Excel spreadsheet in the course's D2L Assignments Tab Assignments after naming your spreadsheet in the following manner: Lastname_Firstinitial_CH21_Budgeting "Assignments" tab. Do not email your spreadsheet to Ms. Morris but submit the spreadsheet under the Assignments tab. Grade. The spreadsheet will be graded and assigned a point value not to exceed 5 points. The grade earned will be posted by 5/6. I J K L M N P Q R S T U V W x Y Z AA AB AC AD 6 F%F% 7 BUDGET PROBLEM Instructions B 9 10 11 12 Jonick Sports, Inc. manufactures a unique type of protective helmet. The following is the company's budget information for April: A. Estimated sales of helmets for April: Use the information given and/or your answers to #1 through 6 to determine the following budget-related amounts for April. a Enter answers in the yellow cells for questions #1 through 7 using formulas and/or functions. b. All answers except #2 are asking for dollar amounts. c. Use variables/cell references rather than typing numbers into the formulas. d. Use the SUM and SUMPRODUCT functions at least once each 13 14 Quantity 5,200 Unit price $295 15 16 17 18 B. Estimated inventories at April 1: Direct materials: 19 20 21 Plastic Pounds 120 Foam lining 70 1. Estimated sales 2. Number of units to produce 3. Total direct materials to be purchased Work in process: Amount $27,610 Finished helmets: Quantity 360 4. Total direct labor cost Unit cost 5. Total factory overhead $181 6. Total manufacturing costs 7. Cost of goods manufactured 22 23 24 25 C. Desired/anticipated inventories at April 30: Direct materials: 26 27 28 Plastic Foam lining Pounds 140 60 Work in process: Amount $20,700 Finished helmets: Quantity 600 Unit cost $181 29 30 D. Direct materials used to produce each helmet: 31 32 Plastic 33 Pounds 2.5 0.5 34 35 36 37 38 Cost per lb. $28 $19 39 40 Foam lining E. Anticipated cost per pound of purchases and inventory amounts of direct materials: Plastic Foam lining F. Anticipated direct labor requirements to produce each helmet: 41 42 43 44 45 Hours Hourly cost Molding Department 2.0 $32 Assembly Department 0.5 20 Finishing Department 0.25 16 Packing Department 0.25 12 46 47 48 49 G. Estimated factory overhead costs for April: Indirect factory wages Amount $96,600 50 Depreciation of plant and equipment 16,900 51 Utilities 10,900 52 Insurance 5,500 53 Property tax 4,100