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 x 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. 3 2 4 56785 5 Required a. Complete the spreadsheet template to calculate price and usage variances. 8 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 =IF(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,B21-E21,E21-B21) would calculate the amount of the variance as a positive number regardless of which amount is larger. 9 10 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 format of the formula in cells C22 and F22 would be =ABS(left number - right number). 5. Explore the power of formulas by copying cells A13:H23 into the scratchpad area below. There you will be able to modify 11 the actual quantity, standard quantity, actual price, standard price, actual units, and standard per unit to see the resulting changes in the variances. 12 400,000 800,000 404,000 820,000 +

Wakin fruit Drink Compary planned to make 400,000 containers of apple fulce. It expected to use two cups of frosen apple The tandard price of one cup of apple coecentrate is 5025 . Actually Wiain groductd 404,000 containers of apple juce and purchased and used 820,000 cugs of concentrate at $0.26 per oup. \begin{tabular}{|r|r|r|r|} \hline 400,000 & 800,000 & -2 & 5025 \\ \hline 404,000 & 820,000 & 50.26 & \\ \hline \end{tabular} Erequilred a. Complete the ipreacheot template to caiculate price ind usace wariances. Spreadsheet Tipa 2. The cells that label the variances in f or U ifavorable if) or unfavorable (U) ane based on a function called if the if changes in the varances. Template for Hrice and Uace Variances \begin{tabular}{|c|c|c|c|c|c|c|c|} \hline \multicolumn{8}{|c|}{ Template for Price and Usage Variances } \\ \hline & & & & & & Actual Units & \\ \hline & & & & & & x & x \\ \hline & & & & & & Standard per unit & r \\ \hline & & & E & & & = & \\ \hline Actual Quantity & lin2 & & Actual Quantity & & & Standard Quantity & r \\ \hlinex & x & & x & x & & x & x \\ \hline Actual Price & r & & Standard Price & r & & Standard Price & r \\ \hline & r & & & r & & & r \\ \hline & & r & r & & r & r & \\ \hline+ & + & Price Variance & + & & Usage Variance & & \\ \hline \end{tabular} 2. 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 fuice, thus using 800,000 cups (400,000 containers 2 cups) of frozen concentrate. 3 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 50.26 per cup. \begin{tabular}{|r|r|} \hline 400,000 & 800,000 \\ \hline 404,000 & 820,000 \\ \hline \end{tabular} 4 5 Required 6 a. Complete the spreadsheet template to calculate price and usage variances. 7 8 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, 9 is greater than E21), the text U is inserted in cell 022 . 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. 10 3. An easier way to make the variance a posittve number regardtess of whether it is favorable or unfavorable is to use the absolute value function. The format of the formula in cells C22 and $22 would be *ABS(left number - right number). 5. Explore the power of formulas by copying cells A13:H23 into the scratchpad area below. There you will be able to modify 11 the actual quantity, standard quantity, actual price, standard price, actual units, and standard per unit to see the resulting changes in the variances. 12