This project is about preparing a pro forma income statement for a newly opened private bookstore. This bookstore expects sales of $1,500,000 in the first year (2021), with an set of SGR values to be tried out.. The usual product mix in a collegiate bookstore is textbooks, software, non-book supplies (such as clothing, pens, notebooks, etc.), and general books (such as fiction, classics, reference, cookbooks, etc.). Such a product mix is usually necessary for offsetting both the low margins allowed for texts and the expenses of doing business.

The Profit Margin Ratio (PMR) for each product is: Profit of the product divided by the Sales of the product. In addition, this bookstore estimates the following percentages of sales for various expenses:

Expense Percent of Sales Salary & benefits Advertising General, selling, & administration 4.8% Miscellaneous expenses 2.1%

The Rent & Utility expense is $40,000 for the first year (2021), and grows at a different rate for each year as determined by the following formula: 6.5% + 0.2%*(number of years away from the base year)1/2. Taxes are paid only when the Earnings Before Taxes is non-negative.

ASSIGNMENTS: (Make sure that worksheet data, B22~E39, are formatted as currency with no decimals. The rest of the worksheet assumptions are formatted as percent with one decimal.)

Conduct three-variable data table analysis for the Net Profit After Taxes for 2022. by using the OFFSET function. You are required to use the following three sets of values for this task:

-Sales Growth Rates: 15.0%, 16.0%, 17.0% -Salary & Benefits: 25.0%, 26.0%, 27.0% -Advertising: 2.5%, 3.0%, 3.5%

All together there are 27 combinations of assumption rates Lookup your data table to find out which combination of SGR, Salary & Benefits, and Advertising Will yield the highest Net Profit After Taxes?

A B C D E F 1Pro Forma Assumptions 2 3Sales Growth 15.0%, 16.0%, 17.0% 4 5 Product Mix RatiosProfit Margin Ratios 6 Text0.45 32.8% 7 Software0.3028.0% 8 Non-book0.20 36.0% 9 General0.0525.0% 10 11Expense Assumptions 12 Salary & Benefits 25.0%, 26.0%, 27.0% 13 Advertising 2.5%, 3.0%, 3.5% 14 Selling, Gen. & Adm. 4.8% 15 Misc. 2.1% 16 Tax Rate 35.0% 17Base Year2020 18 19 INCOME STATEMENT 20 2021 202220232024 21SALES 22 Text 23 Software 24 Non-book 25 General Total Sales1,500,000 27Cost of Goods Sold Gross Profit 29 30OPERATING EXPENSES 31 Salary & Benefits 32 Rent & Utilities40,000 33 Advertising 34 Selling, Gen. & Adm. 35 Misc. Total Operating Exp. 37Earnings Before Taxes 38Taxes Net Profit After Taxes

Please help to complete all required tasks using the provided data and the OFFSET function. Please show all formulas used throughout the computations. Thank You!