The following screenshots show the completed work for the question a) - h) and the question itself below it. I am looking for help in a similar layout with cell referencing if possible (Id like to be able to know how you got to the numbers you have). If you can submit with a second screenshot with the cell references (by clicking CTRL + ~ in the excel doc) that would be really helpful! Thank you!
WEAR IT Manufacturer 190 a. June Revenue Budget Number of Seahawks sold Selling Price per Unit Budgeted Total Sales $ 125 $ 23,750.00 190 b. Production Budget Budgeted Unit Sale Add: Desired Ending Inventory Total Needed Less: Beginning Inventory Budgeted Production 25 215 -35 180 Blue Wool Seahawks Logo 180 180 c. Direct Material Usage Budget Budgeted Production *number of units required Budgeted Direct Material Usage 1.25 1 225 180 Blue Wool Seahawks Logo 225 180 15 10 d. Direct Material Purchase Budget Budgeted Direct Material Usage Add: Desired Ending Inventory Total Needed Less: Beginning Inventory Budgeted Direct Material Purchase in Units Unit Cost of Materials Budgeted Direct Material Purchase in $ 190 -40 240 -30 210 8 $ 1,680 $ 150 4 $ $ 600 180 e. Direct Manufacturing Labor Budget Budgeted Production x Hours Required/Unit Total Labour Hours Required 1.5 270 $ f. Manufacturing overhead budget (Fixed) Budgeted Direct Labour Hours 270 x Variable Overhead Rate $ 14 Total Variable Manufacturing $ 3,780 Fixed Manufacturing Overhead $ 4,185 Total Manufacturing Overhead $ 7,965 Overhead/Direct Labour Hour $ 29.50 $7.965/270 g. Unit Cost of Finished Goods Blue Wool Seahawks Logo Direct Labour Hours Manufacturing Overheads Unit Cost of Finished Goods Units Requir Output Unit Cos Input Unit Cost 1.25 $ 8.00 $ 10.00 1 $ 4.00 $ 4.00 1.5 $ 25.00 $ 37.50 1.5 $ 29.50 $ 44.25 $ 95.75 h. Ending Inventory Budget Ending Inventory of Finished Goods $ 2,393.75 25 Desired Ending Units * $95.75 i. Cost of Goods Sold Budget j. budgeted Income Statement June Sale: $ 23,750.00 COGS b The overall purpose of this assignment is to enhance your skills with Excel relating to linking raw data into a schedule that will be useful to managers. It will help you integrate and understand the various components of an operating budget in master budget. Required: In Excel, create a solution with all of the following items. Any cells that can be linked need to be. Your solution needs to have the following items labeled in the following order and rounded to TWO (2) decimal places: a. Revenue Budget (2 marks) b. Production Budget (2 marks) c. Direct material usage budget (2 marks) d. Direct material purchase budget (2 marks) e. Direct manufacturing labor budget (2 marks) f. Manufacturing overhead budget (both variable and fixed) (2 marks) g. Computation of Unit Costs of Finished Goods (2 marks) h. Ending inventory budget (2 marks) i. Cost of Goods Sold budget (2 marks) j. Budgeted Income Statement (2 marks) Question. Wear It Manufacturer creates hoodies for recent Superbowl Champions. The company sews the Hoodies from wool fabric and attaches a team logo patch. Data relating to the current team is as follows: Seahawks - blue hoodies with a Seahawk logo. The budgeted direct cost inputs for each hoodie this year are as follows: Blue Wool fabric 1.25 metres Seahawks logo patches 1 Direct manufacturing labour 1.5 hours Unit data pertaining to the direct materials for June are as follows: Actual Beginning Direct Materials Inventory (June 1st) Blue Wool fabric 30 metres Seahawks logo patches 40 patches Target Ending Direct Materials Inventory (June 30th) Target Ending Direct Materials Inventory (June 30th) Blue Wool fabric 15 metres Seahawks logo patches 10 patches Unit cost data relating to direct-cost inputs for May and June are as follows: May Actual June Budgeted Blue Wool fabric (per metre) $ 6.00 $ 8.00 Seahawks logo patches (per patch) $ 4.00 $ 4.00 Direct manufacturing labour cost per hour $ 24.00 $ 25.00 Manufacturing overhead (both variable and fixed) is allocated to each hoodie on the basis of budgeted direct manufacturing labour-hours per hoodie. The budgeted variable manufacturing rate for June is $14 per direct manufacturing labour-hour. The budgeted fixed manufacturing overhead for June is $4,185. Both variable and fixed manufacturing overhead costs are allocated to each unit of finished goods. Non- manufacturing costs for Selling, General & Administration are $1,500. Data relating to the finished goods inventory for June are as follows: Beginning Inventory 35 Beginning inventory in dollars $ 3,200 Target ending inventory in units 25 Budgeted sales for June are 190 units of the Seahawks hoodies. The budgeted selling price per unit in June is $125. Assume the following in your answer: Work in process inventory are negligible and ignored. Direct materials inventory and finished goods inventory are costed using the FIFO method. WEAR IT Manufacturer 190 a. June Revenue Budget Number of Seahawks sold Selling Price per Unit Budgeted Total Sales $ 125 $ 23,750.00 190 b. Production Budget Budgeted Unit Sale Add: Desired Ending Inventory Total Needed Less: Beginning Inventory Budgeted Production 25 215 -35 180 Blue Wool Seahawks Logo 180 180 c. Direct Material Usage Budget Budgeted Production *number of units required Budgeted Direct Material Usage 1.25 1 225 180 Blue Wool Seahawks Logo 225 180 15 10 d. Direct Material Purchase Budget Budgeted Direct Material Usage Add: Desired Ending Inventory Total Needed Less: Beginning Inventory Budgeted Direct Material Purchase in Units Unit Cost of Materials Budgeted Direct Material Purchase in $ 190 -40 240 -30 210 8 $ 1,680 $ 150 4 $ $ 600 180 e. Direct Manufacturing Labor Budget Budgeted Production x Hours Required/Unit Total Labour Hours Required 1.5 270 $ f. Manufacturing overhead budget (Fixed) Budgeted Direct Labour Hours 270 x Variable Overhead Rate $ 14 Total Variable Manufacturing $ 3,780 Fixed Manufacturing Overhead $ 4,185 Total Manufacturing Overhead $ 7,965 Overhead/Direct Labour Hour $ 29.50 $7.965/270 g. Unit Cost of Finished Goods Blue Wool Seahawks Logo Direct Labour Hours Manufacturing Overheads Unit Cost of Finished Goods Units Requir Output Unit Cos Input Unit Cost 1.25 $ 8.00 $ 10.00 1 $ 4.00 $ 4.00 1.5 $ 25.00 $ 37.50 1.5 $ 29.50 $ 44.25 $ 95.75 h. Ending Inventory Budget Ending Inventory of Finished Goods $ 2,393.75 25 Desired Ending Units * $95.75 i. Cost of Goods Sold Budget j. budgeted Income Statement June Sale: $ 23,750.00 COGS b The overall purpose of this assignment is to enhance your skills with Excel relating to linking raw data into a schedule that will be useful to managers. It will help you integrate and understand the various components of an operating budget in master budget. Required: In Excel, create a solution with all of the following items. Any cells that can be linked need to be. Your solution needs to have the following items labeled in the following order and rounded to TWO (2) decimal places: a. Revenue Budget (2 marks) b. Production Budget (2 marks) c. Direct material usage budget (2 marks) d. Direct material purchase budget (2 marks) e. Direct manufacturing labor budget (2 marks) f. Manufacturing overhead budget (both variable and fixed) (2 marks) g. Computation of Unit Costs of Finished Goods (2 marks) h. Ending inventory budget (2 marks) i. Cost of Goods Sold budget (2 marks) j. Budgeted Income Statement (2 marks) Question. Wear It Manufacturer creates hoodies for recent Superbowl Champions. The company sews the Hoodies from wool fabric and attaches a team logo patch. Data relating to the current team is as follows: Seahawks - blue hoodies with a Seahawk logo. The budgeted direct cost inputs for each hoodie this year are as follows: Blue Wool fabric 1.25 metres Seahawks logo patches 1 Direct manufacturing labour 1.5 hours Unit data pertaining to the direct materials for June are as follows: Actual Beginning Direct Materials Inventory (June 1st) Blue Wool fabric 30 metres Seahawks logo patches 40 patches Target Ending Direct Materials Inventory (June 30th) Target Ending Direct Materials Inventory (June 30th) Blue Wool fabric 15 metres Seahawks logo patches 10 patches Unit cost data relating to direct-cost inputs for May and June are as follows: May Actual June Budgeted Blue Wool fabric (per metre) $ 6.00 $ 8.00 Seahawks logo patches (per patch) $ 4.00 $ 4.00 Direct manufacturing labour cost per hour $ 24.00 $ 25.00 Manufacturing overhead (both variable and fixed) is allocated to each hoodie on the basis of budgeted direct manufacturing labour-hours per hoodie. The budgeted variable manufacturing rate for June is $14 per direct manufacturing labour-hour. The budgeted fixed manufacturing overhead for June is $4,185. Both variable and fixed manufacturing overhead costs are allocated to each unit of finished goods. Non- manufacturing costs for Selling, General & Administration are $1,500. Data relating to the finished goods inventory for June are as follows: Beginning Inventory 35 Beginning inventory in dollars $ 3,200 Target ending inventory in units 25 Budgeted sales for June are 190 units of the Seahawks hoodies. The budgeted selling price per unit in June is $125. Assume the following in your answer: Work in process inventory are negligible and ignored. Direct materials inventory and finished goods inventory are costed using the FIFO method
