*****Please show the formula in the cell with the answer*****

******I need the formula in the cell******

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 sales growth rate 18% in year 2022, 19% in year 2023, and slowing down to the range of 15%in 2024, and 14% in 2025. 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. The formula is: PMR Profiter Sales xa-Costet Sales Sales The same formulas for the other three products In addition, this bookstore estimates the following percentages of sales for various expenses: Expense Percent of Sales Salary & benefits 25.0% Advertising 3.2% General, selling, & administration 4.5% Miscellaneous expenses 1.9% 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: 65% + 0 2%*(number of years away from the base year) 2 Taxes are paid only when the Earnings Before Taxes is non-negative. ASSIGNMENTS There are only three (tasks for you to do. Enter formulas into the cells with golden color for generating a completed worksheet. Additional information is also provided here Perform Data Table for variables: SGR, product mixed ratios (Textbook, Software, NB, and General). Format of this data table is displayed here as well Use Index and Match to retrieve the Net Profit for the product nixed ratio: Textbook 40%, Software 20%, NB 30%, General 10%, and SGR 18% . B c D E F 1 Pro Forma Assumptions 2 3 4 3 Product Mix Ratios Profit Margin ratios 6 Text 5096 7 Software 3596 Pl-Sheng Deng 8 Non-book 2596 9 General 20% 10 OFFSET 11 12 13 Expense Assumptions (all based co the Sales of the year) Salary & Benefits 25.0% Advertising 3.296 Selling. Gen & Adem 45% Misc 1.996 14 15 16 Tax Rate 35.096 17 Base Year 2015 18 H K L M N o P G 1 2 3 4 5 6 7 8 9 10 Nmin SGR: 14%, 15%, 16%, 17%,18%,19% Textbook product mix ratios: 50%, 40% Software product mixed ratios: 30% and 20% NB product mixed ratios: 30%,20% General items productt mixed ratios: 10% and 0% Add up to 100% 11 Textbooks Software NB Items General Items 40% 30% 30% 0% 2 40% 30% 20% 10% 3 40% 20% 30% 10% 4 50% 30% 20% 50% 20% 30% 0% 6 50% 20% 20% 10% 7 0% I 5 12 13 14 15 16 17 18 19 20 Sales Growth Rate: 14% 15% 16% 17% 18% 19%