ACCT 285 - Fall 2022 Excel Case #4 Scoops is a local business that produces homemade ice cream. The ice cream is produced and sold to customers in 16 oz cartons. Currently, Scoops produces three different flavors of ice cream: strawberry cheesecake, cookies and cream, and caramel nut. All three flavors require cream, sugar, and toppings to produce the final product, but the ratios of how much material is needed differ across the flavors. The table below summarizes the necessary materials to create each product: Quantity (oz/carton) Strawberry Cheesecake Cookies & Cream Caramel Nut Cream 8.00 10.00 11.00 Sugar 2.00 1.00 1.00 Toppings 6.00 5.00 4.00 Prices for these ingredients change throughout the course of the year in accordance to the market. For budgeting purposes, ownership has decided to use an average of prior year costs. The following table summarizes the costs for the different ingredients per ounce: Cost ($/oz) Average Cream $ 0.25 Sugar $ 0.07 Strawberry Cheesecake Topping $ 0.33

Cookies & Cream Topping $ 0.20 Caramel Nut Topping $ 0.15 The only labor necessary for production is provided by the owner who makes $18 per hour. The production process involves three steps that must be done manually. The first step is to mix all of the ingredients together using a machine which typically takes 6 minutes to complete. The second step consists of removing the mixed product from the machine and emptying it into a plastic carton. This process takes about 3 minutes and each carton costs 25 cents. The final step of the labor process involves applying a label to the carton to indicate which flavor it is. The process also takes 3 minutes to complete. Shortly after the end of the year, Scoops has compiled its actual production expense data for the prior year and wants to compare it to the budget. The owner wants to see what areas of their business did better than expected and what areas need improvement. Actual data from the year is presented below: Actual Input InformationProduction Cartons Produced Strawberry Cheesecake 4,000 Cookies & Cream 3,000 Caramel Nut 2,400 Materials Quantity per 16 oz Carton Cost per oz Strawberry Cheesecake Cream 7.00 $ 0.26 Sugar 2.50 $ 0.06 Toppings 6.50 $ 0.38 Cookies & Cream Cream 11.00 $ 0.26 Sugar 0.50 $ 0.06 Toppings 4.50 $ 0.18 Caramel Nut Cream 10.50 $ 0.26 Sugar 2.00 $ 0.06 Toppings 3.50 $ 0.18 Cost per Carton Carton $ 0.22 Labor Hours Labor Cost 2,000 $ 35,000.00

Required: 1. Insert your name on the Budget Input tab. 2. Move to the "DM Variances" tab. Compute the direct materials variances for the prior year. HINTS:? Remember, the standard and actual price of the materials will require you to multiply the quantity per carton by the total units actually produced. Remember that when you are calculating the standard quantities, you are calculating the STANDARD quantities for ACTUAL production. You will need to use "IF" statements and Conditional Highlighting for the variances. Simple "IF" statements can be created using the function (fx) tool at the top of the page, but you will need to create your nested "IF" statement by hand by typing in your desired rules at the top of the page in the formula bar right next to the function (fx) button. HINTS:? The following is an example of an "IF" statement that assigns a category of '1 year', 'More than a year', or 'Less than a year' to the number of days contained in selected cells. =IF(A1=365, "1 year",if (A1>365,"More than a year", "Less than a year")) . Once you have the variances labeled as "Favorable", "Unfavorable", or "No Variance", you will need to color code those cells in order to make them more noticeable and easier to analyze. HINTS:? You can color code them by using conditional formatting. You can find conditional formatting on the "Home" menu in Excel. You will want to select all of your cells containing the text "Favorable", "Unfavorable", or "No Variance" in your spreadsheet. Then you will want to go to the "Highlight Cells Rules" drop down menu, and choose the option of "Equal To...". You will then need to select to highlight your cells equaling Favorable with "Green Fill with Dark Green Text" and your cells equaling Unfavorable with "Light Red Fill with Dark Red Text". Make sure you fill out all 12 boxes (4 materials for each flavor of ice cream) before moving on. 3. Move to the "DL Variances" tab. Compute the direct labor variances for the prior year. You will need to use the same tools and follow the same highlighting and standard quantity rules as mentioned for the previous tab.

4. Check your spreadsheet against the check figures listed below to ensure you have completed the spreadsheet correctly. 5. Submit your assignment by uploading your completed Excel file to the assignment link in Canvas. CHECK FIGURES Tab Cell Check Figure DM Variances Standard Quantity of Cream - Strawberry Cheesecake flavor 32,000 oz DM Variances Materials Quantity Variance for Cream - Strawberry Cheesecake flavor $1,000.00 Favorable DM Variances Materials Price Variance for Toppings - Strawberry Cheesecake flavor $1,300.00 Unfavorable DM Variances Materials Quantity Variance for Cartons - Strawberry Cheesecake flavor $0 No Variance DL Variances Labor Rate Variance $1,000 Favorable DL Variances Labor Efficiency Variance $2,160 Unfavorable

Excel Case #4 Due November X + 6 C I canvasiastateedu ll Iowa State Univer... VP Stadium View Portal ACCT 285 , Excel Case #4 Instructions , Fall 2022.tlocx ACCT 285 - Fall 2022 Excel Case #4 Basic Instructions: 1) It is expected that you work individually on this assignment. Working with another student is a violation of the academic integrity pledge. 2) Please use the Excel template provided in the Excel Case #4 assignment link in Canvas. You must complete this assignment using Microsoft Excel and submit an Excel file for grading. 3) It is required that you use links and formulas to fill in the shaded cells within the assignment template. Failure to formula drive your cells will result in a zero grade. 4) It is required on this Excel case to use IF statements to label each variance as Favorable, Unfavorable, or No Variance. A color coding scheme must be applied as explained later in the instructions as well. 5) Each tab in your template will match the headings throughout the instructions document. 6) Once you have completed your template, please upload your Excel file to the Excel Case #4 assignment link in Canvas for grading. You must upload your completed template before the due date and time in order to receive credit. Scoops is a local business that produces homemade ice cream. The ice cream is produced and sold to customers in 16 oz cartons. Currently, Scoops produces three different flavors of ice cream: strawberry cheesecake, cookies and cream, and caramel nut. All three flavors require cream. sugar, and toppings to produce the final product, but the ratios of how much material is needed differ across the flavors, The table below summarizes the necessary materials to create each product: Quantity (oz/carton) Excel Case #4 Due November X + 6 C I canvasiastateedu ll Iowa State Univer... VP Stadium View Portal ACCT 285 . Excel Case #4 Instructions . Fall 20 2.tlocx Quantity (oz/carton) Strawberry Cheesecake Cookies & Cream Caramel Nut Cream 8.00 10.00 11.00 Sugar 2.00 1.00 1.00 TOFPiHES 6.00 5.00 4.00 Prices for these ingredients change throughout the course of the year in accordance to the market. For budgeting purposes, ownership has decided to use an average of prior year costs. The following table summarizes the costs for the different ingredients per ounce: Cost (5/01) Average Crea m 025 Sugar 007 Strawberry Cheesecake Topping 0.33 Cookies & Cream Topping S 0.20 Caramel Nut Topping S 0.15 The only labor necessary for production is provided by the owner who makes $18 per hour. The production process involves three steps that must be done manually. The first step is to mix all of the ingredients together using a machine which typically takes 6 minutes to complete. The second step consists of removing the mixed product from the machine and emptying it into a plastic carton. This process takes about 3 minutes and each carton costs 25 cents. The final step of the labor process involves applying a label to the carton to indicate which flavor it is. The process also takes 3 minutes to complete. Shortly after the end of the year, Scoops has compiled its actual production expense data for the prior year and wants to compare it to the budget. The owner wants to see what areas of their business did better than expected and what areas need improvement. Actual data from the year is presented below: Excel Case #4 Due November X + 6 C I canvasiastateedu ll Iowa State Univer... VP Stadium View Portal ACCT 285 . Excel Case #4 Instructions . Fall 20 2.tlocx The only labor necessary for production is provided by the owner who makes $18 per hour. The production process involves three steps that must be done manually. The first step is to mix all of the ingredients together using a machine which typically takes 6 minutes to complete. The second step consists of removing the mixed product from the machine and emptying it into a plastic carton. This process takes about 3 minutes and each carton costs 25 cents. The final step of the labor process involves applying a label to the carton to indicate which flavor it is. The process also takes 3 minutes to complete. Shortly after the end of the year, Scoops has compiled its actual production expense data for the prior year and wants to compare it to the budget. The owner wants to see what areas of their business did better than expected and what areas need improvement. Actual data from the year is presented below: Actual Input Information Production Cartons Produced Strawberry Cheesecake 4,000 Cookies & Cream 3,000 Caramel Nut 2,400 Materials Quantity per 16 o: Carton Cost per 01 Strawberry Cheesecake Cream 7100 S 0126 Sugar 2'50 5 0106 Toppings 6150 $ 038 Cookies & Cream Cream 11.00 $ 0126 Sugar 0.50 s 0.06 Toppings 4.50 $ 0.18 Caramel Nut Cream 10.50 5 0.26 Sugar 2.00 S 0.06 Toppings 3.50 S 0.18 Excel Case #4 Due November X + 6 C I canvasiastateedu Iowa State Univer... VP Stadium View Portal ACCT 285 , Excel Case #4 Instructions , Fall 2022,tlocx Cost per Carton Labor Cost $ 35,00000 Required: 1. Insert your name on the Budget Input tab. 2. Move to the \"DM Variances" tab. Compute the direct materials variances for the prior year. HINTS: . Remember, the standard and actual price of the materials will require you to multiply the quantity per carton by the total units actually produced. Remember that when you are calculating the standard quantities, you are calculating the STANDARD quantities forACTUAL production, You will need to use "IF" statements and Conditional Highlighting for the variances. Simple \"IF" statements can be created using the function (fx) tool at the top of the page, but you will need to create your nested \"IF" statement by hand by typing in your desired rules at the top of the page in the formula bar right next to the function (fx) button. HINTS: - The following is an example of an "IF" statement that assigns a category of '1 year', 'More than a year', or 'Less than a year' to the number of days Excel Case #4 Due November X + of 4 - ZOOM + 3. Move to the "DL Variances" tab. Compute the direct labor variances for the prior year. You will need to use the same tools and follow the same highlighting and standard quantity rules as mentioned for the previous tab. 4. Check your spreadsheet against the check figures listed below to ensure you have completed the spreadsheet correctly. 5. Submit your assignment by uploading your completed Excel file to the iment link in Canvas. CHECK FIGURES Tab Cell Check Figure DM Variances Standard Quantity of Cream - Strawberry Cheesecake flavor 32,000 oz DM Materials Quantity Variance for Cream - Strawberry $ 1,000.00 Variances Cheesecake flavor Favorable DM Materials Price Variance for Toppings - Strawberry $1,300.00 Variances Cheesecake flavor Unfavorable DM Materials Quantity Variance for Cartons - Strawberry Variances Cheesecake flavor $0 No Variance DL Variances Labor Rate Variance $1,000 Favorable DL $2,160 Variances Labor Efficiency Variance Unfavorable