Can you remake it$?$

Problem solutions must make full use of defined name ranges using the labels indicated for each

cell$/$range$.$ Excepting Problem $4$: Forecasting, cell addresses should not be used in the formulas.

Problem $4$: Forecasting

You have an idea for a new cake shop, but you want to get an idea of how your business could grow by

capturing portions of the potential market in the next $5$ years $(60$ months$).$

Construct a forecasting worksheet to calculate:

the number of new clients each month $($period$),$ and

the total client base $($cumulative number of clients signed up$)$ each month.

There are the three key parameters values that impact your projections $($Figure $5)$:

Total market potential

$\%$ remaining captured$/$month

Market growth$/$month

Figure $5$: Forecasting Parameters

In your model, make projections for $60$ months based on these parameters for the two separate

scenarios described below to project the number of new customers $($Figure $6).$

Scenario $1$: Constant Market $-$ Total market potential is $5,000,000$ customers. Each month you sign up

$1\mathrm{.}0\%$ of customers in the market that have not yet signed up$.$

Scenario $2$: Growing Market $-$ Total market potential is initially $5,000,000$ customers but grows at $2\%$

per month. Each month you sign up $1\%$ of customers in the market that have not yet signed up$.$

Both scenarios begin at month $0$ and have $0$ new clients and $0$ total clients.

The Constant Market scenario: Month $1($and onwards$),$ calculations must consider the previous

month's Total$\_$Clients values as well as the current month's values resulting from capturing

New$\_$Clients.

The Growing Market Scenario: starting from Month $2$ the Total$\_$Market calculations must also

account for the Market growth$/$month parameter value.

MAIN TASK: READ CAREFULLY BEFORE PROCEDING

Create one Excel Workbook containing separate worksheets to each of the problems below.

Save the workbook as your full name, e$.$g$.,$ anna$\_$watson.xlsx $($all lowercase$).$ Be sure to save

your workbook as a normal Excel workbook file with $.$xlsx extension. To confirm, after the

submission, download your file into a new location on your computer $($or $-$ better $-$ another

computer$)$ and try to open it$.$ If it opens in Excel, you're probably fine.

Each problem must be solved in its own worksheet within one workbook, so rename each sheet

tab with the specific problem title, e$.$g$.,$ Loan, Students, Tiers, etc. $($Ensure to keep everything

compact so that they are easily viewable when opened.$)$

You may use the raw data as provided in the examples and supplementary files to test your

solutions.

Be sure to include all required components in appropriate format for each problem solution.

Up to $50\%$ penalty will be applied for not using named ranges optimally.

Important note about named ranges in Office $365$: Due to some new features added to Excel recently,

named references may result in #N$/$A or #SPILL errors. If these are returned because of names in your

formulae, begin the named reference with an $\text{'}$at$\text{'}$ sign $($@$),$ e$.$g$.,$ @SALES instead of just SALES.