Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Part 1 : Relates to Module 2 2 and I have support videos in the Module 2 2 Resources in the applicable weekly folder. I
Part : Relates to Module and I have support videos in the Module Resources in the applicable weekly folder. 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 will earn zero credit if you type in the numbers and do not use cell referencing. 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 whatif 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 April May June July Sales are the following type: Cash sales collected in month of sale Credit sales collected in the following month of sale The following data pertains to the manufacturing process. Finished goods inventory March st units $ budgeted cost to make a unit Desired ending finished goods for each month of next month's sales volume Direct materials used: Direct Material PerUnit Usage Cost per Pound Metal pounds $ The beginning balance of each month needs to be able to produce of that month's estimated sales volume Beginning material in pounds as of April st Direct materials paid in month purchased. The direct labor used per unit hours $ per hour Direct labor paid in month incurred. 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 $ Power Maintenance $ Supervision Depreciation Taxes Total $ $ 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 $ Commissions $ Depreciation Shipping Total $ $ Unit selling price $ per unit Cash balance as of April st $ 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. Sales Budget per month and quarter. Production Budget per month and quarter. Direct materials purchase budget per month and quarter. Manufacturing Cost budget per month and quarter. Selling and administrative expenses budget per month and quarter. Cash budget per month and quarter. Budgeted income statement ignore income tax for the quarter. What if the company decides to lay off one of the parttime administrative staff. The monthly salaries will be reduced by $ list all budgets that will change? Why? What is the New Net incomeLoss for the quarter?
Part : Relates to Module and I have support videos in the Module Resources in the applicable weekly folder.
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 will earn zero credit if you type in the numbers and do not use cell referencing.
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 whatif 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
April
May
June
July
Sales are the following type: Cash sales collected in month of sale
Credit sales collected in the following month of sale
The following data pertains to the manufacturing process.
Finished goods inventory March st units $ budgeted cost to make a unit
Desired ending finished goods for each month of next month's sales volume
Direct materials used:
Direct Material PerUnit Usage Cost per Pound
Metal pounds $
The beginning balance of each month needs to be able to produce of that month's estimated sales volume
Beginning material in pounds as of April st
Direct materials paid in month purchased.
The direct labor used per unit hours $ per hour
Direct labor paid in month incurred.
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 $
Power
Maintenance $
Supervision
Depreciation
Taxes
Total $ $
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 $
Commissions $
Depreciation
Shipping
Total $ $
Unit selling price $ per unit
Cash balance as of April st $
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.
Sales Budget per month and quarter.
Production Budget per month and quarter.
Direct materials purchase budget per month and quarter.
Manufacturing Cost budget per month and quarter.
Selling and administrative expenses budget per month and quarter.
Cash budget per month and quarter.
Budgeted income statement ignore income tax for the quarter.
What if the company decides to lay off one of the parttime administrative staff. The monthly salaries will be reduced by $ list all budgets that will change? Why?
What is the New Net incomeLoss for the quarter?
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access with AI-Powered 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