This project is about preparing a 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 $(2024),$ with an increase $($randomly determined$)$ up to the range of $17\mathrm{.}5\%18\mathrm{.}5\%$ in year $2,18\mathrm{.}0\%20\mathrm{.}0\%$ in year $3,$ and slowing down to the range of $15\mathrm{.}0\%$~$17\mathrm{.}5\%$ and $14\mathrm{.}5\%$~$16\mathrm{.}0\%$ in years $4$ and $5$ respectively.

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

Salary & benefits

Advertising

General, selling & administration $($SG&A$)$

Miscellaneous expenses

Percent of Sales

$26\mathrm{.}0\%$

$3\mathrm{.}5\%$

$4\mathrm{.}8\%$

$2\mathrm{.}1\%$

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

Use the Lab$2-$Workbook.xlsx Template $($please do NOT change any item's cell address$)$ to prepare five$-$year pro forma income statements.

ASSIGNMENTS: $($Make sure that worksheet data, B$22$~E$39,$ are formatted as currency with no decimals. The rest of the worksheet assumptions are formatted as percent with one decimal$).$ Note: All calculations should be done with formulas.

$$Fill in the Product Mix percentages so that Text Books are at least two times the smallest product mix component.

$$Calculate the Cost of Goods Sold based on provided Cost Margin Ratios.

$$In cells C$3$ through E$3$ use the RAND$()$ function to randomly generate a sales increase based on the ranges previously provided.

$$Complete the Income Statement for Year $2024$ using formulas in every cell possible. This will allow the cells to update dynamically if you change the numbers.

$$Choose a set of profit margin ratios $($only percentages of the format xx$\mathrm{.}0\%$ are allowed$)$ so that the predicted NET PROFIT AFTER TAXES for year $2026$ is as large as possible within the range $$130,000$ and $$135,000.$ Please note that for any two products, the higher profit margin ratio cannot be more than one and half times as much as the lower profit margin ratio.

Based on the identified profit margin ratios from the previous task, make a copy of the worksheet and perform the next steps on the new worksheet. Seek a set of product mix ratios $($i$.$e$.$ the percentage combination for Text, Software, Non$-$Book, and General items$)$ so that the predicted NET PROFIT AFTER TAXES for year $2024$ is as large as possible within the range $$120,000$ and $$130,000($Constraints for $2026$ do not apply here$).$ Each component in a product mi