the stuff in yellow is what I am confused on... I think with how it uploaded... the beginning is at the bottom... If you see other errors elsewhere, please let me know!
(prompt for below) Most of the labor at the manufacturing facility is indirect, but there is some direct labor incurred. Each unit requires 0.10 direct labor hours. The direct labor wage rate is $15 per hour. All direct labor is paid for in the month in which the work is performed. Monthly manufacturing overhead costs are $10,000 for factory rent, $6,000 for other xed manufacturing expenses, and $1.25 per unit for variable manufacturing overhead. No depreciation is included in these gures. All expenses are paid for in the month in which they are incurred. Month January February March April May Total Sales$120,000 $135,000 $129,000 $141,00 $102,000 Sales are 30% cash and 70% credit. All credit sales are collected in the month following the sale. Wasatch Manufacturing has a policy that states that each month's ending inventory of finished goods should be 20% of the following month's sales (in units). Of each month's direct materials purchases, 25% are paid for in the month of purchase, while the remainder is paid for in the month following purchase. 4 pounds of direct materials is needed per unit at $1 .00 per pound. Ending inventory of direct materials should be 15% of next month's production needs. Unit sales =D3/DB BE9/ER EG9/GB Unit seling price 15 15 15 15 15 Total sales Revenue 105000 120090 135000 129090 141000 Cash Collections Budget January February March Cash sales (30%) Quarter -0.5 09) -0.3 169) Credit sales [70% -SUM C15:15) =0,7'09 20.7'9 Total collections SUM C15.F16) Reg. 2 Production Budget January February March Unit sales Quarter -D7 -E7 -F7 Plus: Desired anding inventory (20%) SUM C23.E231 30.2"EZ3) =E24 Total needed -SUM C23:024) -SUM D23-D24) -SUM E23-E24) -SUM C25:E25 Less: Beginning Invention 60.2"C23 C24 2 024 =026 Units to produce -D25-026 -E25-E26 Ang. J... Video 4/11 Direct Materials Budget January February March Units to be produced Quarter -C27 -D27 -E27 -F27 Multiply by: Quantity of DM needed per unit Quantity of DM needed for production -C34.C33 -D34* D33 -E34.E33 -F34"F33 Plus! Desired anding inventory of DM |19% =0.15. D35 -0.15.E35 Total quantity of DM needed -SUM C35:036) -SUM 035-D46) -SUM E35-E36) -SUMIF35:F361 Less! Beginning Inventory of DM 30.15/C35 D35 Quantity of DM to purchase -C37-C38 -D37-098 -E97-69 -137-F3 Multiply by: Cost per pound Total cost of DM purchases Direct Materials Budget January February March Quarter Units to be produced 8,200 8,920 B,760 29,400 Multiply by: Quantity of DM needed per unit Quantity of DM needed for production 32,800 35.680 35,040 117,600 Plus: Desired ending inventory of DM (15%6) 5,352 5,256 5,328 5.328 Total quantity of DM needed 38,152 40.936 40,368 122.928 Less: Beginning Inventory of DM 4,920 5.352 5.256 920 Quantity of DM to purchase 35,232 35,584 35,112 118,008 Multiply by: Cost per pound 51 $1 51 51 Total cost of DM purchases 33.232 35.584 35,112 118.008 April May Unit Sales 9,400 6,800 Plus: Desired End Inventory (20%%??) 1,360 Total Needed 10,760 Less: Beginning Inventory 1,880 Units to produce 8,880 DM needed per unit Quantity of DM needed for production 35.520Sales Budget December January February March April May Unit sales 7,000 8.000 9,000 8,600 9.400 6.800 Linit selling price 15 15 15 15 Total sales Revenue 105,000 120,000 135,000 129.000 141,000 102.000 Req. 1 Cash Collections Budget January February March Quarter Cash sales (30% ) 36,000 40,500 38,700 115,200 Credit sales (70%] 73.500 84,000 94,500 252.000 Total collections 109 500 124,500 133.200 367,200 Req. 2 Production Budget January February March Quarter Unit sales 81000 9.000 8,600 25,600 Plus: Desired ending inventory (20%) 1.800 1.720 1.8.80 Total needed 9:800 10,720 10,480 31,000 Less: Beginning inventory 1.600 1800 1,720 1.607 Units to produce 8.200 8.920 8,760 29 4DOUnit Solar -67 -HT Jar Desired End Inventory flo-37 Total Nodded -CAT-CAN Did needed perunit Quantity of DM needed for production Cath Payments for Direct Material Purchases Budget February December purchases (Hom AP) -0.25*(L41) SUM/ CLO LOO Cash Payments for Direct Labor Costs -DEE 0.1 1.1 Multiply by: Dived Labor tale pur how 15 Cash Payments for Manufacturing Overhead Budget February Munch Quarter Rest Fixed COCCI 10203 10000 Total disbursements =SUM(C80 580] production.. not sales.. BDirect Materials Budget January February March Quarter Units to be produced 8,200 8.920 8,760 29.400 Multiply by: Quantity of DM needed per unit Quantity of DM needed for production 32,800 35.680 35,040 117.600 Plus: Desired ending inventory of DM (1596) 5,352 5,256 5,328 5.328 Total quantity of DM needed 38, 152 40.936 40,368 122.928 Less: Beginning Inventory of DM 4,920 5.352 5.256 4.920 Quantity of DM to purchase 33,232 35,584 35,112 118,008 Multiply by: Cost per pound $1 51 $1 $1 Total cost of DM purchases 33,232 35.584 35,112 1.18.008 April May Unit Sales 9,400 5,800 Plus: Desired End Inventory (20%??) 1,360 Total Needed 10,760 Less: Beginning Inventory 1,880 Units to produce 8,880 DM needed per unit Quantity of DM needed for production 35,520 Req. 4 Cash Payments for Direct Material Purchases Budget January February March Quarter December purchases ( From AP) 37,000 37,000 January purchases 8,308 24,924 33,232 February purchases 6.896 26,688 35,584 March purchases 8,778 8,778 Total disbursements 45.308 33.820 35.466 114,594-10 07 Total dieucome Combined Curb Chadget COLIt 38000 1060 Cich balong andin Budgeted Manufacturing Contour Unit 19 East of manufacturing sash int -WHICHIS CIEN TemplateCash Payments for Direct Labor Costs January February March Quarter Units Produced 3,200 8,920 8,760 29,400 Multiply by: Hours per unit 0.10 1.10 2.10 3.10 Direct Labor Hours 820 9,812 18,396 91,140 Multiply by: Direct Labor rate per hour 15 16 17 18 Direct Labor Cost 12,300 156.992 312,732 1,640.520 Req. 6 Cash Payments for Manufacturing Overhead Budget January February March Quarter Rent (fixed) 10,000 10,000 10,000 30,000 Other MOH (fixed) 6,000 6,000 6,000 18,000 Variable manufacturing overhead 10,250 11,150 10,950 32,350 Total disbursements 26.250 27.150 26,950 80,350 production.. not sales..^ Req. 7 Cash Payments for Operating Expenses Budget January February March Quarter Variable operating expenses 10,400 11,700 11,180 33,280 Fixed operating expenses 2,200 2,200 2,200 6,600 Total disbursements 12,600 13,900 13,380 39,880 hint: units sold X var. op. exp per unit sold (1.3) Operating expenses are budgeted to be $1.30 per unit sold plus fixed operating expenses of $2,200 per month. All operating expenses are paid in the month in which they are incurred.Computer equipment for the administrative ofces will be purchased in the upcoming quarter. In January, Wasatch Manufacturing will purchase equipment for $20,000 (cash), while February's cash expenditures will be $8,000, and March's cash expenditure will be $25,000. Wasatch Manufacturing has a policy that the ending cash balance in each month must be at least $15,000. It has a line of credit with a local bank. The company can borrow in increments of $1,000 at the beginning of each month, up to a totai outstanding loan balance of $100,000. The interest rate on these loans is 1.25% per month simple interest {not compounded}. The company would pay down on the line of credit balance in increments of $1,000 if it has excess funds at the end of the quarter. The company would also pay the accumulated interest at the end of the quarter on the funds borrowed during the quarter. m -m-mmlllam maxim-1mm \"mm-Imam Tml mill \"Elihu Depreciation on the building and equipment for the general and administrative offices is budgeted to be $10,000 for the entire quarter, which includes depreciation on new acquisitions. For purposes of the Budget Manufacturing Cost Per Unit calculation Fixed MOH is $.75. The company's income tax rate is projected to be 23% of operating income less interest expense. The company pays $28,000 cash at the end of February in estimated taxes.Reg. 9 Budgeted Manufacturing Cost per Unit Direct materials cost per unit 4 Direct labor cost per unit 1.5 Variable MOH cost per unit (given data] 1.25 Fixed MOH per unit (given data) 0.75 Cost of manufacturing each unit =SUMIC116:C119) Req. 10 Wasatch Manufacturing Budgeted Income Statement For the Quarter Ended March 31 Sales -SUM(D9:F9) Cost of goods sold -C120 F23 Gross profit =C127-C128 Operating expenses Depreciation expense (given data) 10000 Operating Income -C129-C130-C131 Less: Interest expense F109 Less: provision for income tax 0.23 (C132+C133] Net Income =C132+C133-C134Req. 9 Budgeted Manufacturing Cost per Unit Direct materials cost per unit $4 Direct labor cost per unit 1.50 Variable MOH cost per unit (given data) 1.25 Fixed MOH per unit (given data) 0.75 Cost of manufacturing each unit $8 Req. 10 Wasatch Manufacturing Budgeted Income Statement For the Quarter Ended March 31 Sales 384,000 Cost of goods sold 192,000 Gross profit 192,000 Operating expenses 39,880 Depreciation expense (given data) 10,000 Operating income 142,120 Less: interest expense ess: provision for income tax 32,688 Net income 109,432To demonstrate your understanding of the master budget and all of the supporting budgets, use the data set provided below to complete a comprehensive budget problem for WASATCH MANUFACTURING. Submit the nished document to your instructor through Canvas and upload it to your SLCC ePortfolio. You must prepare the assignment in Excel using the template provided (located in Module 10, Assignment Overview). You must use formulas and link the budgets wherever possible, as indicated below in the grading criteria. Data Set Wasatch Manufacturing is preparing its master budget for the rst quarter of the upcoming year. The following data pertain to Wasatch Manufacturing's operations: Relevant Account Balances as of December 31 (prior year): a Cash - $17,000 a Accounts Receivable, net - $73,500 0 Accounts Payable - $372000 NOTE: Cells highlighted in blue contain static numbers (inputs) and not formulas. Prom pt: Actual sales in December were $105,000. Selling price per unit is projected to remain stable at $15 per unit throughout the budget period. Sales for the rst ve months of the upcoming year are budgeted to be as follows
