PROBLEM 24-1A
PROBLEM 23-1A Preparing a flexible budget statement of financial performance and graphing cost behaviour
Link to Chapter 22, Exhibit 22-7 (page 971).
Cellular Technologies manufactures capacitors for cellular base stations and other communications applications. The company's static budget statement of financial performance for October 20X4 follows. It is based on expected sales volume of 9,000 units.
CELLULAR TECHNOLOGIES
Static Budget Statement of Financial Performance
For month ended 31October 20X4
Sales revenue $189,000
Variable expenses:
Cost of goods sold 72,000
Sales commissions 9,450
Shipping expense 4,500
Fixed expenses:
Salary expense 27,500
Depreciation expense 13,250
Rent expense 11,250
Insurance expense 2,750
Total expenses 140,700
Net profit $ 48,300
Cellular Technologies’ plant capacity is 9,500 units. If actual volume exceeds 9,500 units, Cellular Technologies must rent additional space. In that case, salaries will increase by 15%, rent will double, and insurance expense will increase by $1,000. Depreciation will be unaffected.
Required:
1. Prepare flexible budget statement of financial performance for 7,500, 9,000, and 11,000 units.
2. Graph the behaviour of the company’s total costs.
3. Why might Cellular Technologies’ managers want to see the graph you prepared in Requirement 2 as well as the columnar format analysis in Requirement 1? What is the disadvantage of the graphic approach in Requirement 2?
Instructions:
1. Review the printed template called Problem 23-1A that follows these instructions. Most of the problem data have been entered for you in the data input section of the spreadsheet.
Enter the amounts where indicated.
PROBLEM 23-1A
Continued
2. There are 14 FORMULAS in the template that you need to provide to complete your analysis. Using the spaces provided below, write the formulas where requested in the template.
FORMULA 1 _________________ FORMULA 8 _________________
FORMULA 2 _________________ FORMULA 9 _________________
FORMULA 3 _________________ FORMULA 10 _________________
FORMULA 4 _________________ FORMULA 11 _________________
FORMULA 5 _________________ FORMULA 12 _________________
FORMULA 6 _________________ FORMULA 13 _________________
FORMULA 7 _________________ FORMULA 14 _________________
3. Click the Excel icon from the program manager screen to start the spreadsheet program. Click on Open File to retrieve the template for the problem. Enter the missing amounts in the appropriate cells on the template. Then enter the 14 FORMULAS where indicated on the template.
Type the answer where indicated.
4. Click the Save File Button to save your work.
5. Click the Print Button to print your work.
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.