Build a spreadsheet to solve this problem. You should have 3 tabs: Input, FB, and Variances. There should be no numbers keyed into the FB and variances reports. Une 'IF" formula for identifying favorable or unfavorable Fence LLC makes and sells wooden fence posts for the agricultural industry. The company expected to produce and sell 12,000 posts during the year. Due to poor industry demand, only 9,000 posts were actually produced and sold during the year. The following information is made available: Production data Standard Selling price per unit, per post Direct material cost per unit Le total material cost per post) Direct labor cost per unit Fixed costs Fixed manufacturing overhead 5 70 16 20 Actual $ 55 20 10 170.000 190.000 or Required: (a) Using the template below, prepare pro-forma income statements for the static budget, flexible budget and actual results for year ended December 31. Use your results to compute the flexible budget variances, indicating whether each variance is favorable or unfavorable. Fence LLC Static budget Flexible budget Actual results Flexible Favorable Income Statements budget for year ending I variances unfavorable 12/31 For UF Units (posts $ $ $ $ Sales Variable costs Direct materials Direct labor Contribution margin Fixed costs Fixed Manufacturing overhead Net Income/loss II E (b) Fence's accountant wishes you to prepare the flexible budget variances for direct material. You are provided with the following standard and actual data for wood, the company's only direct material: Pricing and usage data for wood during the year Standard Actual Purchase price for each square foot of wood $2 $5 Quantity of wood needed per fence post (in square feet) Actual units/posts produced during the year 12.000 9,000 Prepare the following flexible budget variances for material, clearly indicating whether each variance is favorable or unfavorable: 1. Material price variance 8 4 II. Material quantity variance III. The total flexible budget variance for material The sheets in excel should look like the following: Input Section I Production data Standard $ 70 16 20 Actual $ 65 20 10 Selling price per unit (.e. per post) Direct material cost per unit (I.e. total material cost per post) Direct labor cost per unit Fixed costs: Fixed manufacturing overhead Pricing and usage data for wood during the year Purchase price for each square foot of wood Quantity of wood needed per fence post (in square feet) Actual units/posts produced during the year 170,000 190,000 Standard $2 8 12.000 Actual $5 4 9,000 FB Section Fence LLC Static budget Flexible budget Actual results Favorable or unfavorable Income Statements for year ending 12/31 Flexible budget variances For UF Units (posts) 12.000 9,000 9,000 $ $ $ 840,000 630,000 585,000 $ (45,000) UF Sales Variable costs Direct materials Direct labor Contribution margin Fixed costs Fixed Manufacturing overhead Net Income/loss 192,000 144,000 180,000 240,000 180,000 90,000 408,000 306,000 315,000 36,000 (90,000) 9,000 UF F F 170,000 170,000 190,000 UF 238,000 20,000 (11,000) 136,000 125,000 UF I Variances Section Variances AP AQ SP SQ $ 5.00 36,000 2.00 72,000 Material Price Variance $ 108,000.00 Material Quantity Variance $ (72,000.00) Total Variance $ 36,000.00 UF F UF Build a spreadsheet to solve this problem. You should have 3 tabs: Input, FB, and Variances. There should be no numbers keyed into the FB and variances reports. Une 'IF" formula for identifying favorable or unfavorable Fence LLC makes and sells wooden fence posts for the agricultural industry. The company expected to produce and sell 12,000 posts during the year. Due to poor industry demand, only 9,000 posts were actually produced and sold during the year. The following information is made available: Production data Standard Selling price per unit, per post Direct material cost per unit Le total material cost per post) Direct labor cost per unit Fixed costs Fixed manufacturing overhead 5 70 16 20 Actual $ 55 20 10 170.000 190.000 or Required: (a) Using the template below, prepare pro-forma income statements for the static budget, flexible budget and actual results for year ended December 31. Use your results to compute the flexible budget variances, indicating whether each variance is favorable or unfavorable. Fence LLC Static budget Flexible budget Actual results Flexible Favorable Income Statements budget for year ending I variances unfavorable 12/31 For UF Units (posts $ $ $ $ Sales Variable costs Direct materials Direct labor Contribution margin Fixed costs Fixed Manufacturing overhead Net Income/loss II E (b) Fence's accountant wishes you to prepare the flexible budget variances for direct material. You are provided with the following standard and actual data for wood, the company's only direct material: Pricing and usage data for wood during the year Standard Actual Purchase price for each square foot of wood $2 $5 Quantity of wood needed per fence post (in square feet) Actual units/posts produced during the year 12.000 9,000 Prepare the following flexible budget variances for material, clearly indicating whether each variance is favorable or unfavorable: 1. Material price variance 8 4 II. Material quantity variance III. The total flexible budget variance for material The sheets in excel should look like the following: Input Section I Production data Standard $ 70 16 20 Actual $ 65 20 10 Selling price per unit (.e. per post) Direct material cost per unit (I.e. total material cost per post) Direct labor cost per unit Fixed costs: Fixed manufacturing overhead Pricing and usage data for wood during the year Purchase price for each square foot of wood Quantity of wood needed per fence post (in square feet) Actual units/posts produced during the year 170,000 190,000 Standard $2 8 12.000 Actual $5 4 9,000 FB Section Fence LLC Static budget Flexible budget Actual results Favorable or unfavorable Income Statements for year ending 12/31 Flexible budget variances For UF Units (posts) 12.000 9,000 9,000 $ $ $ 840,000 630,000 585,000 $ (45,000) UF Sales Variable costs Direct materials Direct labor Contribution margin Fixed costs Fixed Manufacturing overhead Net Income/loss 192,000 144,000 180,000 240,000 180,000 90,000 408,000 306,000 315,000 36,000 (90,000) 9,000 UF F F 170,000 170,000 190,000 UF 238,000 20,000 (11,000) 136,000 125,000 UF I Variances Section Variances AP AQ SP SQ $ 5.00 36,000 2.00 72,000 Material Price Variance $ 108,000.00 Material Quantity Variance $ (72,000.00) Total Variance $ 36,000.00 UF F UF