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 =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.
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 the actual quantity, standard quantity, actual price, standard price, actual units, and standard per unit to see the resulting changes in the
āŖ Sales Revenue $150.00
āŖ Variable manufacturing cost
o Materials $26.00
o Labor $32.50
o Overhead $16.80
o Variable SG&A $29.00
āŖ Contribution Margin
āŖ Fixed Cost
āŖ Net Income
BudgetedActualSales$ 8,000$ 10,200Cost of Goods Sold(4,200)(5,400)Gross Margin3,8004,800Variable Cost(1,600)(2,100)Fixed Cost(1,700)(1,300)Net Income$ 500$ 1,400
Based on this information Ashton Company has a
Multiple Choice
$500 favorable variable operating cost variance
$400 unfavorable variable operating cost variance
$500 unfavorable variable operating cost variance
$400 favorable variable operating cost variance