ATC 8-7 (Static) Spreadsheet Assignment: Mastering Excel Wilkin Fruit Drink Company planned to make 400,000 containers of apple juice. It expected to use two cups of frozen apple concentrate to make each container of juice, thus using 800,000 cups (400,000 containers 2 cups) of frozen concentrate. The standard price of one cup of apple concentrate is $0.25. Actually, Wilkin produced 404,000 containers of apple juice and purchased and used 820,000 cups of concentrate at $0.26 per cup. Required a. Complete the spreadsheet template to calculate price and usage variances. Spreadsheet Tips 2. The cells that label the variances as F or U (favorable (F) or unfavorable (U)) are based on a function called IF. The IF function is needed because the variance can be either favorable or unfavorable. The formula must determine whether actual expenditures exceed budgeted expenditures to determine whether the variance is unfavorable or favorable. As an example, the formula in cell D22 is =F(B21>E21, ,"U","F"). The formula evaluates the expression B21>E21. If this expression is true (B21 is greater than E21), the text U is inserted in cell D22. The IF function can also be used to place formulas or numbers in a cell based on whether an expression is true or false. For example, the formula =IF(B21>E21,B21E21,E21B21) would calculate the amount of the variance as a positive number regardless of which amount is larger. 3. An easier way to make the variance a positive number regardless of whether it is favorable or unfavorable is to use the absolute value function. The number). 5. Explore the power of formulas by copying cells A13:H23 into the scratchpad area below. There you will be able to modify the actual quantity, standard quantity, actual price, standard price, actual units, and standard per unit to see the resulting changes in the variances. Navigation: 1. Use the Open Excel in New Tab button to launch this question. 2. When finished in Excel, use the Save and Return to Assignment button in the lower right to return to Connect. ATC 8-7 (Static) Spreadsheet Assignment: Mastering Excel Wilkin Fruit Drink Company planned to make 400,000 containers of apple juice. It expected to use two cups of frozen apple concentrate to make each container of juice, thus using 800,000 cups (400,000 containers 2 cups) of frozen concentrate. The standard price of one cup of apple concentrate is $0.25. Actually, Wilkin produced 404,000 containers of apple juice and purchased and used 820,000 cups of concentrate at $0.26 per cup. Required a. Complete the spreadsheet template to calculate price and usage variances. Spreadsheet Tips 2. The cells that label the variances as F or U (favorable (F) or unfavorable (U)) are based on a function called IF. The IF function is needed because the variance can be either favorable or unfavorable. The formula must determine whether actual expenditures exceed budgeted expenditures to determine whether the variance is unfavorable or favorable. As an example, the formula in cell D22 is =F(B21>E21, ,"U","F"). The formula evaluates the expression B21>E21. If this expression is true (B21 is greater than E21), the text U is inserted in cell D22. The IF function can also be used to place formulas or numbers in a cell based on whether an expression is true or false. For example, the formula =IF(B21>E21,B21E21,E21B21) would calculate the amount of the variance as a positive number regardless of which amount is larger. 3. An easier way to make the variance a positive number regardless of whether it is favorable or unfavorable is to use the absolute value function. The number). 5. Explore the power of formulas by copying cells A13:H23 into the scratchpad area below. There you will be able to modify the actual quantity, standard quantity, actual price, standard price, actual units, and standard per unit to see the resulting changes in the variances. Navigation: 1. Use the Open Excel in New Tab button to launch this question. 2. When finished in Excel, use the Save and Return to Assignment button in the lower right to return to Connect
