Excel Case: Baylor Electronics

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 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.

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 1, you notice that the ending inventory is 12 percent of total sales on 2022. 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.

Excel Case: Baylor Electronics
 – Pro Forma Income Statement

Instructions for Completing the Exercise

Design Specifications:

In this section, you will format the data and the worksheet (see Figure below):

1. First download the starter file for this assignment

2. Save a copy of that file on your computer.

3. Rename the starter file. .

4. Select the first four rows, activate the drop-down button and insert New rows.

5. Enter the following Title in rows C1, C2 and C3.
Figure 3: Starter File

6. Now select row 1 and cells A1:H1 and Right-Click. Then select Merge & Center. Repeat the procedure for A2-H2 and A3:H3 and A4:H4.

7. Format the title to be Arial 14, bold. You must enter the correct date in your work.

8. Now format the section for sales as shown in the next figure. Please note that the heading for each section must be bold and underlined. Also, you must use bold borders for the top and lower sections only. Also, use a different color to shade the rows for sales and the row for cost of goods sold.

9. Format the next rows of the table as shown below (see a, b and c below).
 (a)
 (b)
 ( c )

10. Now, enter equations that will create the totals for each table. Use Excel’s SUM function. Do this for Total Sales, Total Cost of Goods Sold, Total Non-Operating Income, Total Operating Expenses, and Total Non-Recuring Expense,

11. Now use Excel equations o calculate the following variables. Enter the equation in the respective cells for 2022.  the following variables in your table:
Gross Profit = Total Sales – Total Cost of Goods Sold
Total Income = Gross Profit + Total Non-Operating Income
Total expenses = Total non-recuring Expense + Total Operating Expenses
Net Income Before Taxes = Total Income – Total Expenses
Net Income = Net Income Before Taxes – Tax Expenses

12. Now, enter a new column in location E and move the 2023 year to it. When you are done, your work will look like the following.

13. Now create a new Tab and rename it as Assumptions. Create the following table in the worksheet. Make sure that you create the title. The six cells, in each row of the title should be merged together. The table contains assumptions about what the growth rate would be each year. compared to the base year of 2022. You have to create an equation in the third cell of the third column to multiply the factor with the sales_QTR1 (a). Click Enter and your screen will be similar to (b). Then copy the equation downwards. Your screen will be similar to ( c ).
  (a)  
    (b)       ( c )

14. Now repeat this process for all the remaining columns. Your finished work will be similar to the following image. Please note that the growth rate for each year has to be multiplied with the 2022 base year. Also, note that the “Other” row should be left unchanged.

15. Now, switch back to the Information Tab and link the data in the upper section to the results in the assumptions table. See example below. The Excel equation pulls the data for cell D7 in the Assumptions Tag and makes it the contents of cell 2023 for Sales quarter 1.

16. In the Information Tab, link all the data in the Assumptions Tab to the respective cells for sales. Then use the copy function of Excel to copy the data to all the other cells below. To do this well, you will need to use both absolute and relative addresses. (e.g. $F4, F$4, $F$4)

17. Now calculate the totals for each column using an Excel equation (see below). To do this well, you will need to use absolute and relative addresses.

18. Next, calculate the data for the beginning inventory for 2023. In doing this calculation, you are assuming that the proportion of items that you had in 2022, with not change much in the forecasted years. That means, you will use the % value in 2022 to multiply the Total forecasted sales for each new year. Please note that the forecasted total sales value will for each year. You take the forecasted from the assumptions tab. Then you will assume that the % sales that occurred in 2022, will occur in each forecasted year. For example, if payroll expenses were 0.10% of total sales in 2022, you will assume that in 2025, payroll expenses will be 0.10% of the forecasted total sales for 2025.

19. Similarly, calculate the forecast inventory for each year by using the % factor to multiply the respective sales forecast for a year. The competed table for COGS will be similar to the following. You have to use this approach to calculate the forecasted data for nonoperating income.

20. Now complete the calculations for all the rows in the tables. After you are done, you have to format the information page such that it fits on two pages (use landscape orientation layout).

21. Please note that the assumptions that we are using, i.e. multiplying the % of total sales in 2022 by the forecasted sales is only for the account values NOT for the totals. The total sales, total COGS, gross profit, total operating income, total income, total operating expense, total non-recurring income, and net income, must be derived as simple additions (or subtractions total values as in the case of gross profit, net income before taxes, and net income. For example, gross profit is derived from total Sales – total COGS. Net income before taxes is derived from total sales – total costs. Net Income is derived from Net Income before Taxes – forecasted total tax expense.

22. The net income section would be like the following. Highlight the row for Net income before taxes, to remind you that it is a calculated cell Note that income tax expense will also be forecasted as the % total sales * forecasted total sales for the year.

Creating the Charts

1. Create a pie chart for the Cost of Goods Sold. It should have font size 13. Include a title and a legend.

2. Create a line graph that would include only the Total Sales, Total Cost of Goods Sold and gross Profits for 2022-2027). To do this, you have to select each of the rows and skip over the ones with the arrows (a) with CTRL key pressed down. Then insert a line graph. Add a title to the chart and make it bold font. When you are done, your chart will be similar to ( b ).
  ( a )
   ( b )

Calculating the Net Profit Margin for an ERP Project

1. Create a new Tab with the name NetProfit Margin Calculator.

2. You want to create a model that will allow you to see changes in NetProfit Margin as your forecast assumptions change. You need to create three rows. More specifically, create equations that will transfer data from the Information tab to the NetProfit Margin Calculator Tab.

3. Delete column D, which is currently empty. Update the equations to ensure that it is getting data from the corresponding Information tab. See example below (=Information!C12)

4. When you are done, your NetProfit Margin Calculator Tab will look similar to the following.

5. There are different kinds of profit margins. The gross profit margin divides gross profit by net sales (multiplied by 100). It reflects the percentage of each revenue dollar that is retained as profit after paying for the cost of production.  This is a high-level profit calculation, as it does not factor in operating expenses. YOU WILL NOT USE THIS EQUATION.

  Gross Profit = Net Sales – Cost of Goods Sold

6. Another profit margin calculation is Net Profit Margin. The net profit margin reflects a company’s overall ability to turn income into profit. It is also called the Net Income. It reflects the total amount of revenue left over after all expenses and additional income streams are accounted for.

7. Now create a row that will copy the Net Income from the Information Tab into the NetProfit Margin Calculator Tab. Your screen will be similar to the following.

8. Now calculate the Net Profit Margin using Net Income /Total sales and the equation provided on the last section. Your work will be similar to the figure shown below.

9. Now create a table under the first one as shown below. This table will present the calculations for the annual benefits of the ERP for the next 5 years. The first column is used to quantify the positive impact that ERP will have on sales. It will have a 3 percent increase on the pre-ERP estimate Total sales value. It also shows that it will reduce cost of goods sold by 5% (relative to the pre-ERP value) due to reduction in staff and on-hand inventory. These impacts were used to calculate the gains in Total Sales and on the COGS. The net Impact on Net income is calculated by subtracting the negative value of COGS from impact on Total Sales. You will now be able to calculate the Net Income Post ERP and the Net Profit Margin (Post-ERP). .

10. Now calculate the forecasted values for the total sales, COGS and Net Income after the deployment of the ERP. Complete the calculation for all the years.