代写Excel Case: Baylor Electronics代做留学生SQL 程序

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

	2022		2023	2024	2025	2026	2027
Sales
Sales_QTR 1	17005000	17.9%
Sales_QTR 2	28405000	29.9%
Sales_QTR 3	28405000	29.9%
Sales_QTR 4	20425000	21.5%
Other	760000	0.8%
Total Sales ($)	$    95,000,000	1
Cost of Goods Sold
Beginning Inventory	$    13,585,000	14.3%
Purchases and Production Costs	$   20,705,000	21.79%
Shipping and Delivery	$         380,000	0.4%
Labor  (wages and payroll)	$      7,975,000	8.39%
Other	$         380,000	0.4%
Less Ending Inventory	$      9,400,000	9.89%
Total Cost of Goods Sold	$   52,425,000
GROSS PROFIT	$  42,575,000
Non-Operating Income
Interest Income	544965	0.57%
Rental Income	308813	0.33%
Other Income	422348	0.44%
Total Non-Operating Income	$1,276,126	1.3%
Total INCOME	$43,851,126	46%

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

	2022	2023	2024	2025	2026	2027
	Growth rate:	1.03	1.05	1.09	1.12	1.15
Sales_QTR 1	17005000	17515150	17855250	18535450	19045600	19555750
Sales_QTR 2	28405000	29257150	29825250	30961450	31813600	32665750
Sales_QTR 3	28405000	29257150	29825250	30961450	31813600	32665750
Sales_QTR 4	20425000	21037750	21446250	22263250	22876000	23488750
Other	760000	760000	760000	760000	760000	760000

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.