Excel Case: Baylor Electronics Pro Forma Income Statement

Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

Excel Case: Baylor Electronics

Pro Forma Income Statement

Case Overview

Since it’s founding twenty years ago, Baylor Electronics has continued to grow and evolve in response to the changing times. The family-owned business has endured numerous transitions and is now experiencing steady growth. Blake Baylor, the founder and past CEO, stepped aside two years ago and handed over the company to Jake Baylor, his son. Jake wants to expand his family's business by adopting modern enterprise systems. The million-dollar company under Blake was very successful, but it was product-focused. The company worked diligently to deliver high-quality products to its customers.  The company did this while charging a premium for its services. He could charge high fees because there were very few competitors.  The market is now crowded, and the net income in 2022 dropped from 53% to 33% of sales.

There is no doubt in Jake’s mind that the company's profitability can be improved by the deployment of modern enterprise systems. The enterprise system deployment is going to be very expensive. The new CEO has to decide if the company can afford the project and estimate how much money he can afford to spend on the enterprise system. He will need a bank loan for this project. Mr. Baylor needs your assistance in preparing the documents required for obtaining a bank loan.

Before approaching his local bank, Jake needs to prepare a pro forma financial statement. If Mr. Baylor decides to proceed with the project, he will include the pro forma financial statement as part of his loan application. He has retained your group to prepare it for him. Preparation of the pro forma income statement requires you to design a worksheet with assumptions and information sections, properly format the worksheet, construct formulas, perform economic analysis, and generate charts.

Details for Baylor’s Pro Forma Income Statement

Having recently completed an initial analysis of the business, Jake believes that one of the keys to the business's future lies in improving the organization's efficiency. The company currently has 260 full-time employees.  The enterprise software would cost around $ 15,000,000. If he decides to pursue the ERP deployment project, Jake will use the pro forma income statement as part of his loan application package.

Mr. Baylor asks you to use the income statement outline shown in Figure 1 and use the electronic manufacturer’s 2022 total sales as the base period. You will use the 2022 total sales to forecast Mr. Baylor 's sales, cost of goods sold, expenses, taxes, and net income for the next five years. When preparing the pro forma income statement, several assumptions and additional information are necessary. Figure 2 provides these assumptions and additional information.

Figure 1: Baylor Electronics Income Statement 2022

Figure 2: Forecasted Sales Growth and Tax Rates for 2023 – 2027

Design Considerations

Mr. Baylor specifically requests that you include an appropriate header and apply proper formatting to the cells and worksheet.

Figure 1, represents the information section of the report.  Figure 2 provides the necessary data for completing the assumptions section. By creating separate sections, it is easy for Mr. Baylor to not only view the assumptions data for his income statement, but also, if necessary, change the parameters, thus facilitating his decision-making activities. The information section contains the pro forma income statement, and this section provides Mr. Baylor with information about his projected sales, cost of goods sold, operating expenses, and net income for years 2023 – 2027. The assumptions section uses Baylor’s 2022 sales as the basis for these projections.

As you can observe in Figure 1, Mr. Baylor wants his sales, cost of goods sold, and operating expenses expressed as a percentage of total sales. To facilitate Mr. Baylor’s analysis, please place the total sales value in the assumptions section, along with the other assumptions. By doing this, your formulas in the information section can reference the actual total sales amount. As you study Figure 1, you notice that the ending inventory is -30 percent of total sales in 2022. This value is set to negative because we need to deduct his remaining inventory from the initial inventory and the materials purchased during the period. This will give us the actual value of sold inventories. To prevent the formulas in the information section from being accidentally updated, you must protect the cells in the section.

Mr. Baylor requests that the assumptions and information sections be printed on separate pages. For each section's printout, he wants the results printed on a single page. The printouts should be in landscape orientation and centered both horizontally and vertically.

Expectations: Using Excel Functions

The preparation of this case requires you to apply basic spreadsheet construction concepts. Since Mr. Baylor will change the assumptions values during his decision-making activities, you should have a separate assumptions section for the assumed data. Please note that the formulas in the information section will reference the assumption cells. You should use absolute and relative cell references, rather than constant values.