Question

pleas provide excel spreadsheet showing calculations thank you

Read each question carefully! Make sure that you have answered all parts of each question. Please write your final answers in the spaces provided. Please attach any supporting documents for your answers. Spreadsheets should be well labelled and easy to read. Good Luck! Problem 1 - Master Budget Jensen Manufacturing has the following information relating to their master budget for 2020 Units Sold Selling Price 5,000 units $210 per unit Pounds of Direct Materials Per Unit Manufactured DL Hours Per Unit Manufactured OH Rate Per DL Hour 4 pounds 3 hours $22 per hour Current Inventory of Finished Product Target Inventory of Finished Product 200 units 300 units 200 pounds 100 pounds Current Inventory of Direct Materials Target Inventory of Direct Materials Cost per Pound of Direct Material Cost per Direct Labor Hour $5.00 per pound $22.00 per hour Required • Prepare the revenue (sales) budget in dollars Prepare the production budget in units • Prepare the direct materials budget in units and dollars Prepare the direct labor budget in hours and dollars Prepare the overhead budget in dollars • Prepare the budgeted gross profit in dollars Problem 1: Write Your Answers Here Answer Question Total Revenue Budget Production Budget (in units) Direct Materials Budget (in direct materials) Direct Materials Budget (in dollars) Direct Labor Budget (in hours) Direct Labor Budget (in dollars) Overhead Budget (in dollars) Budgeted Gross Profit

Answer 1

Problem 1

Question	Answer
Total Revenue Budget	$    1,050,000
Production budget	5,100 units
Direct materials budget	20,300 pounds
Direct materials budget	$       101,500
Direct labor budget	15,300 hours
Direct labor budget	$       336,600
Overhead budget	$       336,600
Budgeted GP	$       275,300

-Total Revenue Budget = Units sold X Selling price = 5,000 X 210 = $ 1,050,000

-Production budget in units = Target inventory + Sales - Current Inventory = 300 + 5,000 - 200 = 5,100 units

-DIrect material in units = Target inventory + Sales - Current Inventory = 100 + (5,100 X 4) - 200 = 20,300 pounds

-Direct materials in dollars = 20,300 X 5 = $101,500

-Direct labor in hours = 5,100 X 3 = 15,300 hours

-Direct labor in dollars = 15,300 X 22 = 336,600

-Overhead budget in dollars = 5,100 X 3 X 22 = 336,600

-Budgeted Gross Profit = Sales - Material - Labor - Overhead = 1,050,000 - 101,500 - 336,600 - 336,600 = 275,300