i am trying to get this MBA 642 Bellevue University Project 4 to equal the check figure given by the teacher: 2.) I am sending out a check figure for the Project 4 so you have an idea if you are on track. The Budgeted Cost of Goods Sold figure should be $20,836,640.
Name: Randy J Bendorf Project 4 worth 15 pts. Save you file using your first initial, last name, and name of project. Due by April 29th 6:15 pm Central time. Upload to project 4 link. This project covers material in chapter 9. I have extensive budget demonstrations in the chapter 9 course documents folder. I have a template set up on the solution worksheet that you should use to complete the required budgets that are stated on the solution worksheet tab. You need to use cell references in the development of your budgets. You must use this worksheet to reference the data that is being inputted onto the budgets on the budget worksheet. If you type in any numbers in the solution, I will take off 5 pts., since we use Excel so that we can update budgets or do what if analysis without retyping numbers. You should use this worksheet as your data field and only use cell references and formulas in your budgets. Your grade will be based on accuracy of your solution and correct usage of excel. The budget worksheet has formatted budgets for you to complete. The beauty behind excel is that managers can perform what-if analysis just by changing the data, so you do not need to retype the budgets if you have used cell references and formulas throughout. Grading Rubric: #2,4,5,6,8,10,11 #1,3,7,9 Project 4 Objectives: 1. Develop operating budgets 2. Analyze the cash flow of the company. 3. Analyze how changes in cost or revenue impact the budgets. 4. Provide recommendations to management. I will take off partial pts. For each error within budget 1 pt. for each budget 2 pts. for each budget Data Scenario: You have just been hired into a management position which requires the application of your budgeting skills. You find out that budgeting has not been a priority of the company. You have contacted various areas on the organization and have accumulated the information below to assist you in preparing a comprehensive budget. Manufacturing Inc. produces a part used in the production of engines. Actual Sales and Projected sales in units: March (Actual) 38,000 April 40,000 May 50,000 June 60,000 July 65,000 Sales are the following type: 60% Cash sales collected in month of sale 40% Credit sales collected in the following month of sale The following data pertains to the manufacturing process. 1. Finished goods inventory Desired ending finished goods for each month March 31st 2. Direct materials used: Direct Material Per-Unit Usage Cost per Pound Metal 10 pounds The beginning balance of each month needs to be able to produce Beginning material in pounds as of April 1st 3. The direct labor used per unit 4 hours 32,000 units $148.71 budgeted cost to make a unit 80% of next month's sales $8 50% 200,000 of that month's estimated sales $9.25 per hour 4. Overhead each month is estimated based on direct labor hours per variable cost. All costs that use cash are paid in month incurred. Fixed cost Variable cost Supplies $1.00 Power 0.50 Maintenance $30,000 0.40 Supervision 16,000 Depreciation 200,000 Taxes 12,000 Other 80,000 1.50 Total $338,000 $3.40 5. Monthly selling and administrative expenses are based on units sold per variable cost. All costs that use cash are paid in month incurred. Fixed cost Variable cost Salaries $50,000 Commissions $2 Depreciation 40,000 Shipping $1 Other 20,000 0.6 Total $110,000 $3.60 Less Depreciation Costs $70,000 6. Unit selling price $175 per unit 7. Cash balance as of April 1st $200,000 Required: Prepare the following 2nd quarter budgets on the budget worksheet, which contains a template of budgets you should use. I have adapted the budget model to meet the needs of this company. 1. Sales Budget and cash collection of sales for month and quarter. 2. Production Budget for month and quarter. 3. Direct materials purchase budget for month and quarter. 4. Direct labor budget for month and quarter. 5. Overhead budget for month and quarter. 6. Selling and administrative expenses budget for month and quarter. 7. Cash budget for month and quarter. 8. Based on the quarterly cash budget you prepared, do you have any recommendations on cash management. 9. Cost of goods sold budget for the quarter. 10. Budgeted income statement (ignore income tax) for the quarter. 11. What if the company decides to lay off one of the administrative staff. The salaries will be reduced by $10,000, what budgets are effected? Why? What is the New Net income(Loss) for the quarter? Manufacturing Inc. For quarter ended June 30, 20XX Guidance: Make sure you are using cell references or formulas throughout your budgets. #1 Schedule 1: Sales Budget March April May June QTR July Aug Units Selling Price Sales March (PJ4) April 38,000 $175 $6,650,000 May $40,000 $175 $7,000,000 April Cash sales collected in month sale Credit sales Collected in following month Total Cash receipts June $50,000 $175 $8,750,000 May $4,200,000 2,660,000 $6,860,000 Quarter $150,000 $175 $26,250,000 June $5,250,000 2,800,000 $8,050,000 Check Formula's $150,000 $175 $26,250,000 $15,750,000 8,960,000 $24,710,000 $60,000 $175 $10,500,000 $15,750,000 8,960,000 $24,710,000 Quarter $6,300,000 3,500,000 $9,800,000 #2 Schedule 2: Production Budget April Sales(Schedule 1) Desired ending inventory Total Needs Less: Beginning Inventory (March 32,000) Units to be produced Unit Cost Per Unit & Totals #3 Schedule 3: Direct Material Purchases Budget May 40,000 40,000 80,000 32,000 48,000 $7,138,080.00 80% 32,000 $148.71 April Units to be produced (Schedule 2) Direct Materials per unit (10 pounds) Production needs in (pounds of DM) Total Desired ending inventory Total needs Less: Beginning inventory (50%) Direct materials to be purchased Cost per pound Total Cost May 48,000 10 480,000 40,000 60,000 20,000 600,000 $8 $4,800,000 10 80% 50% June 50,000 48,000 98,000 40,000 58,000 $8,625,180.00 Quarter 60,000 52000 112,000 48,000 64,000 $9,517,440.00 June 58,000 10 580,000 48,000 73,000 25,000 730,000 $8 $5,840,000 150,000 140,000 290,000 120,000 170,000 $25,280,700.00 Quarter 64,000 10 640,000 52,000 82,000 30,000 820,000 $8 $6,560,000 170,000 10 1,700,000 52,000 215,000 75,000 2,150,000 $8 $17,200,000 #4 Schedule 4: Direct Labor Budget April Units to be produced(from Schedule 2) Direct labor time per unit (hours) Total hours needed Cost per hour Total cost May 48,000 4 192,000 $9.25 $1,776,000.00 4 9.25 June 58,000 4 232,000 $9.25 $2,146,000.00 Quarter 64,000 4 256,000 $9.25 $2,368,000.00 Ck Figure (HRS) 170,000 4 680,000 $9.25 $6,290,000.00 680,000 #5 Schedule 5: Overhead Budget April Budgeted direct labor hours needed (Schedule 4) Variable overhead rate Budgeted variable overhead Budgeted Fixed overhead Total overhead May 192,000 $3.40 $652,800 338,000 $990,800 $3.40 $338,000 June 232,000 $3.40 $788,800 338,000 $1,126,800 Quarter 256,000 $3.40 $870,400 338,000 $1,208,400 Ck Figures OH 680,000 $3.40 $2,312,000 1,014,000 $3,326,000 $3,326,000 #6 Schedule 6: Selling and Administrative Expenses Budget April Planned Sales (Schedule 1) Variable Selling & Administrative expenses per unit Total variable expenses Fixed Selling and Administrative expenses Total selling and administrative expenses May 40,000 $3.60 $144,000 70,000.00 214,000.00 $3.60 $70,000.00 June 50,000 $3.60 $180,000 70,000.00 250,000.00 Quarter Ck figure's 150,000 150,000 $3.60 N/A $540,000 $540,000 210,000.00 750,000.00 750,000.00 60,000 $3.60 $216,000 70,000.00 286,000.00 #7 Schedule 7: Cash Budget April Beginning cash Cash Receipts Cash Available Less Cash Disbursements Purchases (Schedule 3) Direct Labor (Schedule 4) Overhead that uses cash Selling and administrative expenses that use cash Total Cash Disbursements Ending Cash May $200,000 $6,860,000 $7,060,000 $70,000 $4,800,000 $1,776,000 $866,800 $144,000 $7,656,800 -$596,800 June -$596,800 $8,050,000 $7,453,200 $70,000 $5,840,000 $2,146,000 $1,038,800 $180,000 $9,274,800 -$1,821,600 Quarter -$1,821,600 $9,800,000 $7,978,400 $70,000 $6,560,000 $2,368,000 $1,156,400 $216,000 $10,370,400 -$2,392,000 -$2,218,400 $24,710,000 $22,491,600 $210,000 $17,200,000 $6,290,000 $3,062,000 $540,000 $27,302,000 -$4,810,400 Ck figure's -$2,218,400 $24,710,000 $22,491,600 $210,000 $17,200,000 $6,290,000 $3,062,000 $540,000 $27,302,000 -$4,810,400
