CIS $3700$

Information Technology for Management

LAB Project $3$

Spring $2024$

Due: $3/15/202411$:$59$pm

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

Cost of Goods Sold

Salary & benefits

Advertising

General, selling, & administration

Miscellaneous expenses

Percent of Sales

$60\mathrm{.}0\%$

$26\mathrm{.}0\%$

$2\mathrm{.}5\%$

$4\mathrm{.}8\%$

$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: number of years away from the base year$){?}^{\frac{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 add excel spreadsheet of what its supposed to look like with formulas