Phelps Glass Inc. Reported Financial Data ✓ Solved

18-22 Phelps Glass Inc has reported the following financial data: net revenues of $10million, variable cost of $ 5 million, controllable fixed cost of $2 million, non-controllable fixed cost $1 million, and untraceable cost of $500,000. The accounting manager has supplied you with this data and asked you to come up with the controllable margin total contribution, cpc, and operating income. Thank you

This assignment requires creating a well-formatted Excel spreadsheet to display and analyze the financial data of Phelps Glass Inc. You need to organize the given financial figures, perform calculations for controllable margin, total contribution, contribution per customer (cpc), and operating income, and present the results in a clear, professional Excel format. The task involves using appropriate cell formatting, formulas, and structure to make the data understandable and visually appealing.

Sample Paper For Above instruction

Introduction

This paper presents an Excel-based analysis of Phelps Glass Inc.’s financial data, focusing on calculating key financial metrics such as controllable margin, total contribution, contribution per customer (cpc), and operating income. Proper formatting and organization enhance the clarity and professionalism of the financial report, aiding decision-making processes.

Data Organization in Excel

To begin, structured data input is essential. Create an Excel worksheet with clear labels and proper formatting. The data can be organized in a table format, as shown below:

Calculations

Using the data, perform the following calculations:

1. Contribution Margin

   This is calculated as Net Revenues minus Variable Costs:

Contribution Margin = $10,000,000 - $5,000,000 = $5,000,000

2. Controllable Margin (Total Contribution)

   This is Contribution Margin minus controllable fixed costs:

Controllable Margin = $5,000,000 - $2,000,000 = $3,000,000

3. Total Contribution

   Equal to Contribution Margin, which is $5,000,000.

4. Contribution per Customer (CPC)

   Assuming the number of customers or sales units is needed; however, since it is not provided, if the total sales are considered, CPC can be calculated if total customer count or units sold is known. For this analysis, if we assume 10,000 customers or units, then:

CPC = Contribution Margin / Number of Customers or Units

For example, CPC = $5,000,000 / 10,000 = $500 per customer/unit (assuming data).

5. Operating Income

   This is calculated by subtracting all fixed costs, including non-controllable and untraceable costs, from the Contribution Margin:

Operating Income = Contribution Margin - Non-Controllable Fixed Costs - Untraceable Costs

= $5,000,000 - $1,000,000 - $500,000 = $3,500,000

Excel Formatting Tips

• Use bold headers for each section.
• Apply currency formatting to monetary cells.
• Use cell borders to group related data.
• Include formulas directly in the cells for calculations to ensure accuracy.
• Provide clear labels for each metric and calculation.

Final Presentation

The Excel sheet should present the data clearly, with a summary section highlighting key metrics such as contribution margin, controllable margin, cpc, and operating income. Charts or conditional formatting may be added to emphasize important figures, but clarity should be maintained.

References

• Garrison, R. H., Noreen, E. W., & Brewer, P. C. (2020). Managerial accounting. McGraw-Hill Education.
• Horngren, C. T., Sundem, G. L., Stratton, W. O., Burgstahler, D., & Schatzberg, J. (2021). Introduction to management accounting. Pearson.
• Drury, C. (2018). Management and cost accounting. Cengage Learning.
• Hilton, R. W., & Platt, D. (2019). Managerial accounting. McGraw-Hill Education.
• Weygandt, J. J., Kimmel, P. D., & Kieso, D. E. (2021). Managerial accounting: Tools for business decision making. Wiley.
• Blocher, E., Stout, D., Cokins, G., & Lincoln, W. (2019). Cost management: A strategic emphasis. McGraw-Hill Education.
• Anthony, R. N., & Govindarajan, V. (2019). Management control systems. McGraw-Hill Education.
• Otley, D. T. (2016). The contingency theory of management accounting and control: 1980–2014. Management Accounting Research.
• Chenhall, R. H. (2003). Management control systems design within its organizational context: Findings from contingency-based research and directions for the future. Accounting, Organizations and Society.
• Granlund, M., & Mouritsen, J. (2003). Problematizing the relationship between strategies and management accounting practices. Management Accounting Research.