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 its founding twenty years ago, Baylor Electronics continued to grow and evolve with the times. The family-owned business has survived many transitions and is currently experiencing a 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 hard to deliver great quality products for 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 fifty-three percent to thirty-three percent of sales.

There is no doubt in Jake’s mind that the profitability of the company 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 can he afford to spend on the enterprise system. He will need a bank loan for this project. Mr. Baylor needs your help in preparing documents required for procuring a bank loan.

Before approaching his local bank, Jake needs to prepare a pro forma financial statement. If Mr. Baylor decides to go forward with the project, he will use 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 a first analysis of the business, Jake feels that one of the keys to the business's future would be the improvement of the efficiencies of the organization. Currently, the company 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 sales as the base period. You will use the 2022 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.

Figure1, 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 the 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, you must 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 2, you notice that Mr. Baylor 's salary is 12 percent of gross profit. To keep the information section's formulas from accidentally being updated, you must protect the cells in the section.

Mr. Baylor wants the assumptions and information sections printed on separate pages. For each section's printout, he wants the results printed on a single page. The printouts should utilize a landscape orientation and be centered 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. Keep in mind that the formulas in the information section will reference the assumptions cells. You should use absolute and relative cell references, as opposed to constant values.