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. Construct a spreadsheet template that could be used to calculate price and usage variances. The template should be constructed so that it could be used for any problem in the chapter that refers to price and usage variances by changing the data in the spreadsheet. The screen capture on this page represents a template for price and usage variances.

Spreadsheet Tip

(1) The shaded cells can be changed according to the data in each problem. All other cells are formulas based on the numbers in the shaded cells.

(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 D13 is = IF (B11>E11,'U','F'). The formula evaluates the expression B11>E11. If this expression is true (B11 is greater than E11), the text U is inserted in cell D13. 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(B11>E11,B11−E11,E11−B11) 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 C13 and F13 would be = ABS(left number − right number).

(4) The lines around the variances are produced by using the borders in Excel (Format, Cells, Border).

Template for price and Quantity Variances

Transcribed Image Text:

Tomplate for Prico and Quantity Varianceo 404 ,000 Aclual Unils х Standard per unit 200 Astual Quantity Artual Quartity Standard Quantity Standard Price Actual Price Standard Price 0.26 $213,200.00 0.25 0.25 $205 p00.00 $202,000.00 $3,000,00 U Usage Variance $8.200.00 U Price Variance