Generating a Flexible Budget; Spreadsheet Application Crane Corporation’s master (static)budget for the year is shown below:Sales (60,000 units) $1,860,000Cost of goods sold:Direct materials $168,000Direct labor 450,000Overhead (variable overheadapplied at 40% of direct labor cost) 240,000 858,000Gross profit $1,002,000Selling expenses:Sales commissions (all variable) $167,400Rent (all fixed) 40,000Insurance (all short-term fixed) 30,000General expenses:Salaries (all short-term fixed) 92,000Rent (all short-term fixed) 77,000Depreciation (all short-term fixed) 50,000 456,400Operating income $ 545,600Required1. During the year, the company manufactured and sold 55,000 units of product. Prepare an Excel spreadsheet that contains a flexible budget for this level of output. Round all budget figures to nearest wholedollar. 2. Now suppose that the actual level of output was 65,000 units. Rerun your spreadsheet to generate a flexible budget for this output level. Round all budget figures to the nearest whole dollar. 3. Of what relevance is the notion of the “relevant range” when preparing pro forma budgets or a flexiblebudget for control purposes?
Master Budget
A master budget can be defined as an estimation of the revenue earned or expenses incurred over a specified period of time in the future and it is generally prepared on a periodic basis which can be either monthly, quarterly, half-yearly, or annually. It helps a business, an organization, or even an individual to manage the money effectively. A budget also helps in monitoring the performance of the people in the organization and helps in better decision-making.
Sales Budget and Selling
A budget is a financial plan designed by an undertaking for a definite period in future which acts as a major contributor towards enhancing the financial success of the business undertaking. The budget generally takes into account both current and future income and expenses.
Generating a Flexible Budget; Spreadsheet Application Crane Corporation’s master (static)
budget for the year is shown below:
Sales (60,000 units) $1,860,000
Cost of goods sold:
Direct materials $168,000
Direct labor 450,000
Overhead (variable overhead
applied at 40% of direct labor cost) 240,000 858,000
Gross profit $1,002,000
Selling expenses:
Sales commissions (all variable) $167,400
Rent (all fixed) 40,000
Insurance (all short-term fixed) 30,000
General expenses:
Salaries (all short-term fixed) 92,000
Rent (all short-term fixed) 77,000
Depreciation (all short-term fixed) 50,000 456,400
Operating income $ 545,600
Required
1. During the year, the company manufactured and sold 55,000 units of product. Prepare an Excel spreadsheet that contains a flexible budget for this level of output. Round all budget figures to nearest whole
dollar.
2. Now suppose that the actual level of output was 65,000 units. Rerun your spreadsheet to generate a flexible budget for this output level. Round all budget figures to the nearest whole dollar.
3. Of what relevance is the notion of the “relevant range” when preparing pro forma budgets or a flexible
budget for control purposes?
Trending now
This is a popular solution!
Step by step
Solved in 2 steps