Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

I need help putting together a spreadsheet to solve this problem Below are the directions for the questions. All one problem. Build a spreadsheet to

image text in transcribedimage text in transcribed

I need help putting together a spreadsheet to solve this problem Below are the directions for the questions. All one problem.

image text in transcribedimage text in transcribedimage text in transcribed
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. Use '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 Actual $ $ Selling price per unit (i.e. per post) 70 65 Direct material cost per unit (i.e. total material cost per post) 16 20 Direct labor cost per unit 20 10 Fixed costs Fixed manufacturing overhead 170,000 190,000 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 or for year ending variances unfavorable 12/31 F or UF Units (posts) $ $ $ $ Sales Variable costs Direct materials Direct labor Contribution margin Fixed costs Fixed Manufacturing overhead Net Income/loss(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) 8 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 II. Material quantity variance Ill. The total flexible budget variance for material The sheets in excel should look like the following: Input Section Production data Standard Actual $ $ Selling price per unit (i.e. per post) 70 65 Direct material cost per unit (i.e. total material cost per post) 16 20 Direct labor cost per unit 20 10 Fixed costs: Fixed manufacturing overhead 170,000 190,000 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) 8 Actual units/posts produced during the year 12,000 9,000FB Section Fence LLC Flexible Favorable or Static Flexible Actual unfavorable Income Statements for budget budget budget results year ending 12/31 variances F or UF Units (posts) 12,000 9,000 9,000 $ $ $ Sales 840,000 630,000 585,000 (45,000) UF Variable costs Direct materials 192,000 144,000 180,000 36,000 UF Direct labor 240,000 180,000 90,000 (90,000) F Contribution margin 408,000 306,000 |315,000 9,000 F Fixed costs Fixed Manufacturing overhead 170,000 170,000 190,000 20,000 UF Net Income/loss 238,000 136,000 125,000 (11,000) UF Variances Section Variances AP S 5.00 AQ 36,000 SP S 2.00 SQ 72,000 Material Price Variance $ 108,000.00 UF Material Quantity Variance $ (72,000.00) F Total Variance $ 36,000.00 UF

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Cornerstones of Managerial Accounting

Authors: Maryanne Mowen, Don Hanson, Dan Heitger, David McConomy, Bradley Witt, Jeffrey Pittman

3rd Canadian edition

176530886, 176721231, 978-0176721237

More Books

Students also viewed these Accounting questions

Question

Discuss the reporting of variances. AppendixLO1

Answered: 1 week ago

Question

Define line and staff authority

Answered: 1 week ago

Question

Define the process of communication

Answered: 1 week ago

Question

Explain the importance of effective communication

Answered: 1 week ago

Question

* What is the importance of soil testing in civil engineering?

Answered: 1 week ago

Question

The relevance of the information to the interpreter

Answered: 1 week ago

Question

The background knowledge of the interpreter

Answered: 1 week ago