Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Use Part 1 and the budgets that have been completed to fill out the empty budgets shown and answer question #7 and #9 Part 1
Use Part 1 and the budgets that have been completed to fill out the empty budgets shown and answer question #7 and #9
Part 1 | ||||||||||||
I have a template set up on the Budgetsolution worksheet that you should use to complete the required budgets and requirements stated below. | ||||||||||||
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. | ||||||||||||
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. | ||||||||||||
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 | March 31st | 32,000 | units | $148.71 | budgeted cost to make a unit | |||||||
Desired ending finished goods for each month | 80% | of next month's sales volume | ||||||||||
2. Direct materials used: | ||||||||||||
Direct Material | Per-Unit Usage | Cost per Pound | ||||||||||
Metal | 10 | pounds | $8 | |||||||||
The beginning balance of each month needs to be able to produce | 50% | of that month's estimated sales volume | ||||||||||
Beginning material in pounds as of April 1st | 200,000 | |||||||||||
3. The direct labor used per unit | 4 | hours | $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 | $28,000 | 0.40 | ||||||||||
Supervision | 16,000 | |||||||||||
Depreciation | 200,000 | |||||||||||
Taxes | 12,000 | |||||||||||
Other | 80,000 | 1.10 | ||||||||||
Total | $336,000 | $3.00 | ||||||||||
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 | $1 | |||||||||||
Depreciation | 40,000 | |||||||||||
Shipping | 0.6 | |||||||||||
Other | 20,000 | 0.4 | ||||||||||
Total | $110,000 | $2.00 | ||||||||||
6. Unit selling price | $174 | per unit | ||||||||||
7. Cash balance as of April 1st | $160,000 | |||||||||||
Required: You must use cell references on the BudgetSolution worksheet, by referencing this worksheet that contains the data. | ||||||||||||
Prepare the following second quarter budgets and answer the questions listed on the template provided on the BudgetSolution Worksheet. I have adapted the budget model to meet the needs of this company. | ||||||||||||
If I bolded a line item, that is a header and does not need computation on that row. | ||||||||||||
Please note the quarter column is for the quarter so not all lines should be added across in the quarter column. When you have beginning and ending inventory or cash balances this is for the quarter and should be brought over to the quarter column. | ||||||||||||
1. Sales Budget per month and quarter. | ||||||||||||
2. Production Budget per month and quarter. | ||||||||||||
3. Direct materials purchase budget per month and quarter. | ||||||||||||
4. Manufacturing Cost budget per month and quarter. | ||||||||||||
5. Selling and administrative expenses budget per month and quarter. | ||||||||||||
6. Cash budget per month and quarter. | ||||||||||||
7. Based on the quarterly cash budget you prepared, provide recommendations on cash management. Your comments should be directed at management. | ||||||||||||
8. Budgeted income statement (ignore income tax) for the quarter. | ||||||||||||
9. What if the company decides to lay off one of the administrative staff. The monthly salaries will be reduced by $5,000, what budgets are effected? Why? | ||||||||||||
What is the New Net income(Loss) for the quarter? Use Part 1 and the budgets that have been completed to fill out the empty budgets shown and answer question #7 and #9 |
#6 Cash Budget Beginning cash Add Cash Collections of Sales: Cash sales collected in month sale Credit sales Collected in following month Total Cash receipts Cash Available Less Cash Disbursements: Purchases (Budget 3) Direct Labor (Budget 4) Overhead that uses cash Selling and administrative expenses that use cash Total Cash Disbursements Ending Cash #8 Budgeted Income statement for Second quarter Sales (Budget #1) Cost of Goods Sold: Beginning Finished Goods Total Manufacturing Costs (Budget #4) Cost of Goods Available for Sale # 7 Based on the quarterly cash budget you prepared, provide recommendations on cash management. Your comments should be directed at management. Ending finished goods Budgeted Cost of Goods Sold Gross Profit April Less: Selling and administrative expenses (Budget 5) Income before income taxes Manufacturing Inc. Cash Budget For quarter ended June 30, 20XX May June Manufacturing Inc. Budgeted Income Statement For the Secord Quarter ended June 30, 20XX Quarter If you have linked everything correctly, you should only have to change the monthly salary on the data sheet. Please change the salary back to the original amount of before you submit. #9 What if the company decides to lay off one of the administrative staff. The monthly salaries will be reduced by $5,000, what budgets are effected? Why? What is the New Net income(Loss) for the quarter? Part 1 Solution #1 Sales Budget Units Selling Price Sales #2 Production Budget Sales Budget (Reference Budget 1) Plus desired ending inventory Total Inventory requirements Less: Beginning Inventory Units to be produced #3 Direct Material Purchases Budget Units to be produced (Reference Budget 2) Direct Materials per unit (pounds) Production needs (pounds) Desired ending inventory (pounds) Total needs (pounds) Less: Beginning inventory (pounds) Purchases needed of Direct materials (pounds) Cost per pound Total purchases of direct materials #4 Manufacturing Cost Budget Direct Materials: Production needs(pounds)--found on Budget 3 Cost per pound Total Cost of material issued to production Direct Labor: Units to be produced (Reference Budget 2) Direct labor time per unit (hours) Total hours needed Cost nor hour Guidance: Make sure you are using cell references or formulas throughout your budgets April $ April April April Manufacturing Inc. Sales Budget For quarter ended June 30, 20XX 40,000 $174 6,960,000 $ 40,000 40,000 80.000 32,000 48,000 Manufacturing Inc.. Production Budget For quarter ended June 30, 20XX May May 48,000 10 480,000 250,000 730,000 200,000 530,000 $8 $4,240,000 530,000 $8 $4,240,000 50,000 $174 8,700,000 $ 48,000 4 192,000 $0 25 50,000 48.000 98.000 40.000 58.000 Manufacturing Inc. Direct Material Purchases Budget For quarter ended June 30, 20XX May 58,000 10 June 580,000 300,000 880,000 250,000 630,000 $8 $5,040,000 Manufacturing Inc. Manufacturing Cost Budget For quarter ended June 30, 20XX May 630,000 $8 $5,040,000 June 58,000 4 232,000 to 25 June June 60,000 $174 10,440,000 $ 60.000 52000 112,000 48,000 64,000 64,000 10 640,000 325,000 965,000 300.000 665,000 $8 $5,320,000 665,000 $8 $5,320,000 64,000 4 Quarter 256,000 50 25 Quarter Quarter Quarter 150,000 $174 26,100,000 150.000 290,000 170,000 170,000 10 1,700,000 2,575,000 750,000 1,825,000 $8 $14,600,000 1,825,000 $8 $14,600,000 170,000 4 680,000 40 25 July 65,000 #4 Manufacturing Cost Budget Direct Materials: Production needs(pounds)--found on Budget 3 Cost per pound Total Cost of material issued to production Direct Labor: Units to be produced (Reference Budget 2) Direct labor time per unit (hours) Total hours needed Cost per hour Total cost of Direct Labor Manufacturing Overhead: Budgeted direct labor hours needed (Reference Row 50 above) Variable overhead rate Budgeted variable overhead Budgeted Fixed overhead Total Manufacturing Overhead Total Manufacturing Cost # 5 Selling and Administrative Expenses Budget Budgeted Sales in units (Reference Budget 1) Variable Selling and Administrative expenses per unit Total variable expenses Fixed Selling and Administrative expenses Total selling and administrative expenses #6 Cash Budget Beginning cash Add Cash Collections of Sales: Cash sales collected in month sale. Credit sales Collected in following month Total Cash receipts Cash Available Less Cash Disbursements: Purchases (Budget 3) Direct Labor (Budget 4) Overhead that uses cash April April April Manufacturing Inc. Manufacturing Cost Budget For quarter ended June 30, 20XX May 530,000 $8 $4,240,000 48.000 4 192,000 $9.25 $1,776,000 192,000 3.00 $576,000 336,000 $912,000 $6,928,000 630,000 $8 $5,040,000 40,000 $2.00 $80,000 110,000 $190,000 58,000 4 232,000 $9.25 $2,146,000 232,000 3.00 $696,000 336,000 $1,032,000 $8,218,000 Manufacturing Inc. Selling and Administrative Expenses Budget For quarter ended June 30, 20XX May 50,000 $2.00 June $100,000 110,000 $210,000 June Manufacturing Inc. Cash Budget For quarter ended June 30, 20XX May June 665,000 $8 $5,320,000 64,000 4 256,000 $9.25 $2,368,000 256,000 3.00 $768,000 336,000 $1,104,000 $8,792,000 60,000 $2.00 $120,000 110,000 $230,000 Quarter Quarter Quarter 1,825,000 $8 $14,600,000 170,000 4 680,000 $9.25 $6,290,000 680,000 3.00 $2,040,000 336,000 $2,376,000 $23,266,000 150,000 $2.00 $300,000 110,000 $410,000 #4 Manufacturing Cost Budget Direct Materials: Production needs(pounds)--found on Budget 3 Cost per pound Total Cost of material issued to production Direct Labor: Units to be produced (Reference Budget 2) Direct labor time per unit (hours) Total hours needed Cost per hour Total cost of Direct Labor Manufacturing Overhead: Budgeted direct labor hours needed (Reference Row 50 above) Variable overhead rate Budgeted variable overhead Budgeted Fixed overhead Total Manufacturing Overhead Total Manufacturing Cost # 5 Selling and Administrative Expenses Budget Budgeted Sales in units (Reference Budget 1) Variable Selling and Administrative expenses per unit Total variable expenses Fixed Selling and Administrative expenses Total selling and administrative expenses #6 Cash Budget Beginning cash Add Cash Collections of Sales: Cash sales collected in month sale. Credit sales Collected in following month Total Cash receipts Cash Available Less Cash Disbursements: Purchases (Budget 3) Direct Labor (Budget 4) Overhead that uses cash April April April Manufacturing Inc. Manufacturing Cost Budget For quarter ended June 30, 20XX May 530,000 $8 $4,240,000 48.000 4 192,000 $9.25 $1,776,000 192,000 3.00 $576,000 336,000 $912,000 $6,928,000 630,000 $8 $5,040,000 40,000 $2.00 $80,000 110,000 $190,000 58,000 4 232,000 $9.25 $2,146,000 232,000 3.00 $696,000 336,000 $1,032,000 $8,218,000 Manufacturing Inc. Selling and Administrative Expenses Budget For quarter ended June 30, 20XX May 50,000 $2.00 June $100,000 110,000 $210,000 June Manufacturing Inc. Cash Budget For quarter ended June 30, 20XX May June 665,000 $8 $5,320,000 64,000 4 256,000 $9.25 $2,368,000 256,000 3.00 $768,000 336,000 $1,104,000 $8,792,000 60,000 $2.00 $120,000 110,000 $230,000 Quarter Quarter Quarter 1,825,000 $8 $14,600,000 170,000 4 680,000 $9.25 $6,290,000 680,000 3.00 $2,040,000 336,000 $2,376,000 $23,266,000 150,000 $2.00 $300,000 110,000 $410,000 #6 Cash Budget Beginning cash Add Cash Collections of Sales: Cash sales collected in month sale Credit sales Collected in following month Total Cash receipts Cash Available Less Cash Disbursements: Purchases (Budget 3) Direct Labor (Budget 4) Overhead that uses cash Selling and administrative expenses that use cash Total Cash Disbursements Ending Cash #8 Budgeted Income statement for Second quarter Sales (Budget #1) Cost of Goods Sold: Beginning Finished Goods Total Manufacturing Costs (Budget #4) Cost of Goods Available for Sale # 7 Based on the quarterly cash budget you prepared, provide recommendations on cash management. Your comments should be directed at management. Ending finished goods Budgeted Cost of Goods Sold Gross Profit April Less: Selling and administrative expenses (Budget 5) Income before income taxes Manufacturing Inc. Cash Budget For quarter ended June 30, 20XX May June Manufacturing Inc. Budgeted Income Statement For the Secord Quarter ended June 30, 20XX Quarter If you have linked everything correctly, you should only have to change the monthly salary on the data sheet. Please change the salary back to the original amount of before you submit. #9 What if the company decides to lay off one of the administrative staff. The monthly salaries will be reduced by $5,000, what budgets are effected? Why? What is the New Net income(Loss) for the quarter? #6 Cash Budget Beginning cash Add Cash Collections of Sales: Cash sales collected in month sale Credit sales Collected in following month Total Cash receipts Cash Available Less Cash Disbursements: Purchases (Budget 3) Direct Labor (Budget 4) Overhead that uses cash Selling and administrative expenses that use cash Total Cash Disbursements Ending Cash #8 Budgeted Income statement for Second quarter Sales (Budget #1) Cost of Goods Sold: Beginning Finished Goods Total Manufacturing Costs (Budget #4) Cost of Goods Available for Sale # 7 Based on the quarterly cash budget you prepared, provide recommendations on cash management. Your comments should be directed at management. Ending finished goods Budgeted Cost of Goods Sold Gross Profit April Less: Selling and administrative expenses (Budget 5) Income before income taxes Manufacturing Inc. Cash Budget For quarter ended June 30, 20XX May June Manufacturing Inc. Budgeted Income Statement For the Secord Quarter ended June 30, 20XX Quarter If you have linked everything correctly, you should only have to change the monthly salary on the data sheet. Please change the salary back to the original amount of before you submit. #9 What if the company decides to lay off one of the administrative staff. The monthly salaries will be reduced by $5,000, what budgets are effected? Why? What is the New Net income(Loss) for the quarter?
Step by Step Solution
★★★★★
3.50 Rating (153 Votes )
There are 3 Steps involved in it
Step: 1
Solution Here we have 1 xy1 1xy dy dx 0 Putting 1x c in 1 we get 1 x...Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started