ACCT 285 Excel Case #4 Ba sic 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 he applied as explained later in the instructions as well. 5] Each tab in your template will match the headings throughout the instructions document. 6] Check your answer against the check figures provided as you work. 7] 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 clue 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 avors of ice cream: strawberry cheesecake. cookies and cream. and caramel nut. All three flavors require cream. sugar. and toppings to produce the final product. out the ratios of how much material is needed differ across the flavors. The table below summarizes the necessary materials to create each product: Quantity {ozfcarton} Strawberrv Cheesecake Cookies 3. Cream Caramel Nut Cream 3.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 {Hoe} Average Cream Sugar Strawberry Cheesecake Topping Cookies 8; Cream Topping Caramel Nut To . .i_n The only labor necessary for production is provided by the owner who makes $13 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 nal 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: Strawberry Cheesecake Cookies 8: Cream Caramel Nut E'itra'lnlberrilI Cheesecake Cookies 8: Cream Caramel Nut Costnertanon 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 Ifx) 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 {A153653'More than a year", "Less than a year")} Once you have the variances labeled as "Favorable\Favorable with \"Green Fill with Dark Green Text" and your cells equaling Unfavorable with "Light Red Fill with Dark Red Text\". Make sure you ll out all 12 boxes {4 materials for each avor of ice cream] before moving on. . Move to the "DL Variances\" tab. Compute the direct labor variances for the prior year. You lmrill need to use the same tools and followI the same highlighting and standard quantity rules as mentioned for the previous tab. . Check your spreadsheet against the check figures provided to ensure you have completed the spreadsheet correctly. . Submit your assignment by uploading your completed Excel file to the assignment link in Canvas. A B C D E 1 Name: 2 Budget Input Information W Cartons Produced Strawberry Cheesecake 4,000 7 Cookies & Cream 3,000 8 Caramel Nut 2,400 9 Direct Materials 10 Quantity (oz/carton) Strawberry Cookies & 11 Cheesecake Cream Caramel Nut 12 Cream 8.00 10.00 11.00 13 Sugar 2.00 1.00 1.00 14 Toppings 6.00 5.00 4.00 15 16 Cost ($/oz) Average 17 Cream 0.25 18 Sugar 0.07 19 Strawberry Cheesecake Topping 0.33 20 Cookies & Cream Topping 0.20 21 Caramel Nut Topping 0.15 22 23 Cost ($/carton) Average 24 Carton $ 0.25 25 Direct Labor 26 Cost (hr) Time (min) 27 Mix 18.00 28 Fill 18.00 W W O 29 Label 18.00 30 31 minutes per hour 60 minutesN lo-meow A B C D Actual Input Information Production 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 S 0.26 Sugar 2.50 S 0.06 Toppings 6.50 S 0.38 Cookies & Cream Cream 11.00 S 0.26 Sugar 0.50 S 0.06 Toppings 4.50 S 0.18 Caramel Nut Cream 10.50 S 0.26 Sugar 2.00 S 0.06 Toppings 3.50 S 0.18 Cost per Carton Carton 5 0.22 Labor Hours Labor Cost 2,000 5 35,000.00 B C D E G K O Direct Material Variances Strawberry Cheesecake Cream Sugar Toppings Cartons UIAWN Standard Quantity Standard Quantity Standard Quantity Standard Quantity Actual Quantity 28,000 Actual Quantity Actual Quantity Actual Quantity Standard Price Standard Price Standard Price Standard Price Actual Price Actual Price Actual Price Actual Price Co AQ X AP AQ x SP SQ x SP AQ x AP AQ x SP SQ x SP AQ x AP AQ x SP SQ x SP AQ x AP AQ x SP SQ x SP 10 11 12 Amount Favorable/Unfavorable Amount Favorable/Unfavorable Amount Favorable/Unfavorable Amount Favorable/Unfavorable 13 Material Price Variance Material Price Variance Material Price Variance Material Price Variance 14 Material Quantity Variance Material Quantity Variance Material Quantity Variance Material Quantity Variance 15 16 Cookies & Cream 17 Cream Sugar Toppings Cartons 18 Standard Quantity Standard Quantity Standard Quantity Standard Quantity 19 Actual Quantity Actual Quantity Actual Quantity Actual Quantity 20 Standard Price Standard Price Standard Price Standard Price 21 Actual Price Actual Price Actual Price Actual Price 22 23 AQ x AP AQ x SP SQ x SP AQ x AP AQ x SP SQ x SP AQ X AP AQ x SP SQ x SP AQ x AP AQ x SP SQ x SP 24 25 26 Amount Favorable/Unfavorable Amount Favorable/Unfavorable Amount Favorable/Unfavorable Amount Favorable/Unfavorable 27 Material Price Variance Material Price Variance Material Price Variance Material Price Variance 28 Material Quantity Variance Material Quantity Variance Material Quantity Variance Material Quantity Variance 29 30 Caramel Nut 31 Cream Sugar Toppings Cartons 32 Standard Quantity Standard Quantity Standard Quantity Standard Quantity 33 Actual Quantity Actual Quantity Actual Quantity Actual Quantity 34 Standard Price Standard Price Standard Price Standard Price 35 Actual Price Actual Price Actual Price Actual Price 36 37 AQ x AP AQ x SP SQ x SP AQ x AP AQ x SP SQ x SP AQ x AP AQ x SP SQ x SP AQ x AP AQ x SP SQ x SP 38 39 40 Amount Favorable/Unfavorable Amount Favorable/Unfavorable Amount Favorable/Unfavorable Amount Favorable/Unfavorable 41 |Material Price Variance Material Price Variance Material Price Variance Material Price Variance 42 Material Quantity Variance Material Quantity Variance Material Quantity Variance Material Quantity VarianceA B C Direct Labor Variances 2 Standard Hours 3 Actual Hours 4 Standard Rate 5 Actual Rate 6 AH x AR AH x SR SH x SR 1 00 10 Amount Favorable/Unfavorable 11 Labor Rate Variance 12 Labor Efficiency Variance 13