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 $(2023),$ with a set of sales growth rates $($SGR$)$ values to be tried out.

This bookstore estimates the following percentages of total sales for various costs and expenses:

Costs & Expense Percent of Sales

Cost of Goods Sold $60\mathrm{.}0\%$

Salary & benefits $26\mathrm{.}0\%$

Advertising $2\mathrm{.}5\%$

General, selling, & administration $4\mathrm{.}8\%$

Miscellaneous expenses $2\mathrm{.}1\%$

The Rent & Utility expense is $$40,000$ for the first year $(2023,$ 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.

ASSIGNMENTS:

Conduct one variable data table analysis for Net Profit After Taxes $($NPAT$)$ for $2025$ by varying SGR on the following

set of rates: $15\mathrm{.}0\%,16\mathrm{.}0\%,17\mathrm{.}0\%,18\mathrm{.}0\%,19\mathrm{.}0\%,20\mathrm{.}0\%$

Q$1.$ Find the SGR value which yields the highest NPAT.

Let the above value be the value for SGR in this question. In this second question, we conduct two$-$variable data table

analysis by varying Salary & Benefits on the set of rates: $25\mathrm{.}0\%,26\mathrm{.}0\%,27\mathrm{.}0\%,28\mathrm{.}0\%,29\mathrm{.}0\%,30\mathrm{.}0\%$; and varying

Advertising on the set: $2\mathrm{.}5\%,3\mathrm{.}0\%,3\mathrm{.}5\%,4\mathrm{.}0\%.$ Altogether there are $24$ combinations.

Q$2.$ Identify which combination of values yields the highest value of NPAT.

Q$3.$ Show the Pro Forma income statement based on the SGR$.$

Please answer question $3($Q$3)$ in an excel spreadsheet with formulas