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, with an increase rate of 10.8% in year 2, 16.5% in year 3, and slowing to 13.2% and 12.1% in years 4 and 5, respectively. The usual product mix in a collegiate bookstore is textbooks, 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. Product Mix Ratio = Sales for Product i / Total Sales The profit margin ratio is the gross margin, i.c., Sales - Cost, divided by the Sales. In other words, Profit Margin Ratio for Product / = Sales for Product i - Cost for Product i Sales for Product i This bookstore assumes the profit margin ratio for each product category is: Textbooks 30.0%, Non-book items 40.0%, and General books 35.0%. On the expense side, this bookstore estimates the following percentages of sales for various expenses: Expense Percent of Sales Salary & benefits 16.0% Advertising 1.7% General, selling, & administration 3.8% Miscellaneous expenses 0.6% The Rent & Utility expense is $40,000 for the first year (2019), and grows at a different rate for each year as determined by the following formula: 3.5% + 0.6%*Years_from_the_Base_Year. Taxes are paid only when the Earnings Before Taxes is non-negative. Using Exhibit 1 (please do NOT change any item's cell address) to prepare five-year pro forma income statements. ASSIGNMENTS: Use Create from Selection of the Defined Names function group to assign a name for each of the assumption rates (shaded cells). Complete the worksheet with formulas.B C D E F Pro Forma Assumptions Product Mix Ratio Profit Margin Ratio Text product mix ratio Text PMR Non-book product mix ratio 30 0% Non-book PMR 40.0% General product mix ratio 25.0% General PMR 35.0% Expense Growth Assumptions Salary 160% Advertising 1.7% Selling, Gen. & Adm. 3.8% Misc. Taxes 300% Base Year 2010 2020 2021 2022 2023 Sales Growth Rate 10.8% 16.5% 13.2% 12.19% Rent & Utilities Growth Rate INCOME STATEMENT 2019 2020 2021 2022 2023 Sales Text Non-book General Total Sales 1.500.000 Cost of Goods Sold 30 Gross Profit Operating Expenses Salary & Benefits Bent. Utilities etc 40.000 Advertising Selling. Gen. & Adm. Misc 39 Total Operating Exn. 40 Earnings Before Taxes Taxes Net Profit After Taxes