Course Syllabus, Calendar, Cal X M10: Comprehensive Master BI X Dashboard X + C slcc.instructure.com/courses/729683/assignments/9368459 * a Update : To 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 finished document to your instructor through Canvas. 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. nts Data Set Wasatch Manufacturing is preparing its master budget for the first quarter of the upcoming year. The following data pertain to Wasatch Manufacturing's operations: Relevant Account Balances as of December 31 (prior year): . Cash - $17,000 . Accounts Receivable, net - $73,500 . Accounts Payable - $37,000 Sales & Collections Production & Materials Conversion Costs Operating Exp Cash Data Income Stmt 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 first five months of the upcoming year are budgeted to be as follows: Month January February March April May Total Sales $120,000 $135,000 $129,000 $141,000 $102,000 Sales are 30% cash and 70% credit. All credit sales are collected in the month following the sale. /729683/assignments/9368459#tab-16:; M10: Comprehensive Master BI x (- -) C i slcc.instructure.com/courses/729683/assignments/9368459 r a e- To 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. 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): 0 Cash - $17,000 - Accounts Receivable, net - $73,500 0 Accounts Payable - $37,000 Sales & Collections Production & Materials Conversion Costs Operating Exp Cash Data Income Stmt Wasatch Manufacturing has a policy that states that each month's ending inventory of nished 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. Four 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. Comprehensive Master Budget Project Rubric - 7560 pts I Ratings Pts (->c' i slcc.instructure.com/courses/729683/assignments/9368459 To 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. 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): 0 Cash - $17,000 - Accounts Receivable, net - $73,500 0 Accounts Payable - $37,000 Sales & Collections Production & Materials Conversion Costs Operating Exp Cash Data Income Stmt 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. Mster Budget Project Rubric - 7560 pts 6:; M10: Comprehensive Master Bl x i slcc.instructure.com/courses/729683/assignments/9368459 ldate S l To 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 :all 2021 lome MANUFACTURING. Submit the nished document to your instructor through Canvas. You must 4Odules prepare the assignment in Excel using the template provided (located in Module 10, Assignment . t Overview). You must use formulas and link the budgets wherever possible, as indicated below in the SSI nmen S l g grading criteria. innouncements Mes Data Set )iscussions Wasatch Manufacturing is preparing its master budget for the rst quarter of the upcoming year. The 00m following data pertain to Wasatch Manufacturing's operations: 4ySuccess Relevant Account Balances as of December 31 (prior year): 0 Cash - $17,000 - Accounts Receivable, net - $73,500 l 0 Accounts Payable - $37,000 Sales & Collections Production & Materials Conversion Costs Operatingp Cash Data Income Stmt Operating expenses are budgeted to be $1.30 per unit sold plus xed operating expenses of $2,200 per month. All operating expenses are paid in the month in which they are incurred. Comprehensive Master Budget Project Rubric - 7560 p8 F Criteria Ratings Pts Formatting 2 pts 0 pts Meets All Requirements: All cells should be comma formatted and rounded to the Doesn't meet "1 nearest dollar. Exception: Budgeted Mfg Cost Per Unit should be rounded to two requirement 2 pts decimal places. I . (->c- 5:; M10: Comprehensive Master BI x i slcc.instructure.com/courses/729683/assignments/9368459 Update 3 ) l U I MANUFACTURING. Submit the nished document to your instructor through Canvas. 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): 0 Cash - $17,000 0 Accounts Receivable, net - $73,500 0 Accounts Payable - $37,000 Sales & Collections Production & Materials Conversion Costs Operating Exp_ [ Cash Data Income Stmt 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 total 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. Ig (- -) C i slcc.instructure.com/courses/729683/assignments/9368459 r a e- n U I MANUFACTURING. Submit the nished document to your instructor through Canvas. You must prepare the assignment in Excel using the template provided (located in Module 10, Assignment Fall 2021 Home Overview). You must use formulas and link the budgets wherever possible, as indicated below in the Modules grading criteria. Assignments Data Set Announcements G d Wasatch Manufacturing is preparing its master budget for the rst quarter of the upcoming year. The ra es following data pertain to Wasatch Manufacturing's operations: Discussions Relevant Account Balances as of December 31 (prior year): Zoom 0 Cash - $17,000 MySuccess 0 Accounts Receivable, net - $73,500 0 Accounts Payable - $37,000 Sales & Collections Production & Materials Conversion Costs Operating Exp_ Cash Data Income Stmt Depreciation on the building and equipment for the general and administrative ofces 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. Comprehensive Master Budget Project Rubric - 7560 pts Criteria Ratings Pts Formatting 2 pts 0 pts I _All Requirements: All cells should be comma formatted and rounded to the Doesn't meet - .. -......-.-..... . . not. AutoSave OFF Excel Budget Problem Template Q Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments fx Define Name Ey Trace Precedents v Calculate Now " Trace Dependents Insert AutoSum Recently Financial Logical Text Date & Lookup & Math & More Create from Selection Show Error X Remove Arrows F Calculation Calculate Sheet Function Used Time Reference Trig Functions Formulas Checking Options F62 + X V fx B D E F G H K L M N P 1 WASATCH MANUFACTURING Master Budget W N NOTE: Cells highlighted in blue contain static numbers (inputs) and not formulas. Sales Budget December January February March April May Unit sales 7,000 8,000 9,000 3,600 ,40 6,800 Unit selling price 15 15 15 15 15 15 10 00 Total sales Revenue 105,000 120,000 135,000 129,000 141,000 102,000 10 11 Req. 1 12 13 Cash Collections Budget 14 January February March Quarter 15 Cash sales 36,000 10,500 38,700 115,200 16 Credit sales 73,500 34,000 94,500 252,000 17 Total collections 109,500 124,500 133,200 367,200 18 19 Req. 2 20 21 Production Budge 22 January February March Quarter 23 Unit sales ,000 9,000 8,600 5,600 24 Plus: Desired ending inventory 1,800 1,720 1,880 .,880 25 Total needed 9,800 10,720 10,480 27,480 26 Less: Beginning inventory ,600 ,800 ,720 1,600 27 Units to produce 8,200 8,920 3,760 25,880 28 29 Req. 3 JU 31 Direct Materials Budget Template + + 125% ReadyAutoSave OFF Excel Budget Problem Template Q Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments fx Define Name Ey Trace Precedents " Trace Dependents fix A v Calculate Now Insert AutoSum Recently Financial Logical Text Date & Lookup & Math & More Create from Selection Show Error Calculation Calculate Sheet Function Used Time Reference Trig Functions Remove Arrows Formulas Checking Options F62 + X V fx A B C D E F G H K L M N P 29 Req. 3 JU 31 Direct Materials Budget 32 January February March Quarter 33 Units to be produced 8,200 8,920 3,76 25,880 34 Multiply by: Quantity of DM needed per unit 4 35 Quantity of DM needed for production 32,800 35,680 35,040 103,520 36 Plus: Desired ending inventory of DM i,352 5,256 5,328 5,328 37 Total quantity of DM needed 38,152 40,936 40,368 108,848 38 Less: Beginning inventory of DM 4,920 5,352 5,256 4,920) 39 Quantity of DM to purchase $3,232 35,584 35,112 103,928 40 Multiply by: Cost per pound 1.00 1.00 .00 1.00 41 Total cost of DM purchase 33,232 35,584 35,112 103,928 42 43 44 April May 45 Unit Sales 9,400 ,800 46 Plus: Desired End Inventory 1,360 47 Total Needed 10,760 48 Less: Beginning Inventory 1,880 49 Units to produce 8,880 50 DM needed per unit 51 Quantity of DM needed for production 35,520 52 53 Req. 4 54 55 Cash Payments for Direct Material Purchases Budget 56 January February March Quarter 57 December purchases (From AP) 37,000 37,000 58 January purchases 8,308 24,924 33,232 59 February purchases 3.896 6,688 5,584 60 March purchase 3,778 8,778 61 Total dishurcements 15 3081 33 8201 35 466 114 594 Template + Ready + 125%Excel Budget Problem Template Q AutoSave OFF Share Comments Home Insert Draw Page Layout Formulas Data Review View Tell me Ey Trace Precedents fx Define Name v Calculate Now Ly Trace Dependents Insert AutoSum Recently Financial Logical Text Date & Lookup & Math & More Create from Selection Show Error Calculation Calculate Sheet Used Time Reference Trig Functions Remove Arrows Formulas Checking Options Function F62 fx B C D E F G H K L M N O P A January purchases 8,308 24,924 33,232 58 59 February purchases 3,896 26,688 35,584 60 March purchases 3,778 3,778 61 Total disbursements 45,308 33,820 $5,466 114,594 62 63 Reg. 5 64 65 Cash Payments for Direct Labor Costs 66 January February March Quarter 67 Units Produced 68 Multiply by: Hours per unit 69 Direct Labor Hours 70 Multiply by: Direct Labor rate per hour 71 Direct Labor Cost 72 73 Req. 6 74 75 Cash Payments for Manufacturing Overhead Budget 76 January February March Quarter 77 Rent (fixed) 78 Other MOH (fixed 79 Variable manufacturing overhead 80 Total disbursements 81 82 Reg. 7 83 84 Cash Payments for Operating Expenses Budget 85 January February March Quarter 86 Variable operating expenses 87 Fixed operating expenses 88 Total disbursements 89 90 Req. 8 Template + + 125% Enter