ITEC1010 Assignment #2 – Excel Spreadsheets

Due: March 30, 10 PM
This is a MS Excel assignment. The software is freely available as part of the Office 365 package for
students. Download and install to your system if you have not done so already.
Be sure to aim for appropriate formulas and functions that are flexible, elegant and optimally
comprehensible/meaningful as discussed in lectures.
Problem solutions must make full use of defined name ranges using the labels indicated for
each cell/range. Excepting Problem 6: Forecasting, cell addresses should not be used in the
formulas.

RECOMMENDED: Solve each assignment problem below by following the 5-step problem-solving
strategies below:
STEP 1: Understand the problem clearly -- read the problem instruction carefully more than once.
STEP 2: Strategize -- begin by thinking roughly as to how the problem can be solved -- write your ideas
out in words.
STEP 3: Design solutions-- write a more structured algorithm or draw a flow chart of the ordered set of
steps required to solve the problem.
STEP 4: Implementation -- translate your design algorithm to formulae that Excel can execute -- use
appropriate functions and name-defined cell ranges for full credit.
STEP 5: Verification -- ensure that the results of your formulae match the example values.
MAIN TASK: READ CAREFULLY BEFORE PROCEDING
1. Create one Excel Workbook containing separate worksheets to each of the problems below.
2. Save the workbook as your full name, e.g., diana_prince.xlsx (all lowercase). Be sure to save
your workbook as a normal Excel workbook file with .xlsx extension.
3. Each problem must be solved in its own worksheet within one workbook so rename each sheet
tab with the specific problem title, e.g., Mortgage, Invoice, etc. (Ensure to keep everything
compact so that they are easily viewable when opened.)
4. You may use the raw data as provided in the examples and supplementary files to test your
solutions.
5. Be sure to include all required components in appropriate format for each problem solution.
6. Always remember that up to 50% penalty will be applied for not using named ranges optimally.
Important note about named ranges in Office 365: Due some new features added to Excel, named
references may result in #N/A errors. If these are returned because of names in your formulae, begin
the named reference with an 'at' sign (@), e.g., @SALES instead of just SALES.
Problem 1: Mortgage

1. Duplicate the Mortgage Calculator model below in your Excel worksheet.
2. Formulae for cells F4 to F7 are as per Figure 1.



Figure 1

3. Name all appropriate cells using their labels and reconstruct the formulae replacing cell address
references.
4. Test by entering assumption inputs to cells C4 to C7 using input examples in Figure 2.
5. Now reconstruct the summary table (as per Figure 2 – B9:E11) and populate cells with
appropriate labels and named references.
6. Format the whole model as per Figure 2 including:
a. Model title font set to 12pts and merge-centered (B2:F2)
b. "Initial Assumptions" and "Result" merge-centered across 2 columns with Thick Outside
Borders; other data with All Borders
c. Summary table labels with Thick Bottom Borders
d. Format values appropriately as Number, Currency, or Percentage
e. Color fill areas with 3 different colors of your choice
f. Bold face displayed data



Figure 2

Problem 2: Staff Payroll

Re-create the Staff Payroll model below for weekly pay calculations with the format and features shown
in Figure 3.
• Range names should correspond to their respective labels.
• Calculating the Total_Pay involves applying the following rules: hours worked amount that is 37
or less is calculated by multiplying it and the standard hourly rate; beyond that any extra hours
up to 10 hours are calculated at time-and-half, or 50% more than the standard hourly rate; and
hours logged greater than 47 are calculated at double-time, or 100% higher than the hourly rate.
Key functions to use: IF and ISNUMBER;
Example:

Figure 3





Problem 3: Movies

Re-create the model below that searches the Top IMDB Rated Movies table and returns the attributes
of the Rank holding movie.
• Download database file Top10Movies.csv and import into your workbook (Figure 4).
• The model works by having the user enter the rank # and Excel returns the details stored in the
database table. Figure 5: Rank # 8 is entered by the user and Excel returns the related Info data.
• Use the rank number input as the lookup value for VLOOKUP.
• The solution should be a single 'master' formula that would work for any attribute i.e., the
formula that returns the correct Title in the searcher Info is appropriate for being Auto-Filled
down the column to return the rest of the movie attributes in the Database.

Key functions to use: VLOOKUP; MATCH

Figure 3 - Database: Top10

Figure 4 - Searcher

Problem 4: Tier Pricing

A company purchases iBuddy toys according to this price schedule:
• For the first 1000, $9.20 per unit
• For any of the next 3000 $8.80 per unit
• For any order beyond 4,000 , $8.50 per unit
The Tier Pricing spreadsheet calculates the total price of buying x iBuddy units, where x is a number
input to a cell (Ordered, e.g., cell F2) on the worksheet with values that may fall into any or all 3 Tiers of
pricing; Total is the total of Qty (e.g., C4:C6).
Re-create the model as per below:

1. One model must be able to handle all 3 possible tiers of order scenarios.
2. Format with differentiating colors as per example below.

Key functions to use: IF (nested)
Here are 3 possible scenario output examples you can use to test your solutions:

Figure 5 - Scenario1: Tier 1

Figure 6 - Scenario2: Tier 2

Figure 7 - Scenario: Tier 3
Problem 5: Commissions

Re-create the model below that identifies the attributes of the Top Performer of the Month (the sales
Sales with the most accrued sales).
• Download/import the MonthlySales.csv and complete the Monthly_Sales table (Figure 9 Left)
used to record each Sales's individual sales for the month as per image below.
o To do this, re-create the Commissions Lookup table (Figure 9 Right) defining the
commission % for the corresponding SalePrice that falls within a range.
• Recreate the Performance Table (Figure 10) which is the summary of the individual Sales
Region's Monthly Sales with formulas for Properties_Sold; Total_Sales; Commision_Earned
• Finally, recreate the Top Performer of the Month table (Figure 11) returning the highest
Total_Sales from the Performance Table (Figure 10) identifying the Region; Properties_Sold;
Commision_Earned.

Key Functions to use: LOOKUP; COUNTIF; SUMIF; MAX; INDEX; MATCH;


Figure 8 - Monthly Sales Table and Commission Lookup Table


Figure 9 - Performance Table



Figure 10 - Top Performer of the Month

Problem 6: Forecasting

You have an idea for a new service that offers customized products for subscribers, 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 (period).

There are the three key parameters values that impact your projections (Figure 12):
1. Total market potential
2. % remaining captured/period
3. Market growth/period

Figure 11 - Forecasting Parameters

In your model, make projections for 60 Periods (Months) based on these parameters for the two
separate scenarios described below to project the number of new customers (Figure 13).

Scenario 1: Constant Market - Total market potential is 10,000,000 customers. Each month you sign
up 1.07% of customers in the market that have not yet signed up.

Scenario 2: Growing Market - Total market potential is initially 10,000,000 customers but grows at
2.5% per month. Each month you sign up 1.07% of customers in the market that have not
yet signed up.


• Both scenarios initially (Period 0) have 0 new customers and 0 total customers.
• Beginning Period 1, calculations must consider the previous period's Total_Clients values as well
as the current period's New_Clients values.
• In the case of Scenario 2, the Period 2 Total_Market calculations accounts for the Market
growth/period parameter value.



Hint: Whenever you need to reference a value calculated in a previous row you need to use
its cell address, not a range name; but remember that the rest of the formula should
make use of named ranges.



Figure 12 - Two Scenarios of Market Capture (Partial View)


Problem 7: Charting
Re-create the Excel chart below (Figure 14) that draws on the Forecasting worksheet data comparing the
total customer base under each of the two scenarios.


Figure 13 - Market Capture Projections
One way to easily create a chart like this is to select the column of values for “Total Customers” for the
first scenario and create a simple chart.
• Select the data and select: Insert > Insert Line Chart > (There are several chart types available;
you may use line or scatter chart type.)

• Now from the second scenario select the values from the “Total Customers” column and copy
(CTRL-C or Command-C) to the system Clipboard.
• Click the edge of the existing chart and paste (CTRL-V or Command-V) the Clipboard values to
add the second, comparison line.

• You can click anywhere on the chart to reveal the three tool buttons beside the chart; use the
top tool (or choose the option from the tool bar) to modify various chart elements.



Chart Elements

Chart Styles

Chart Styles

• Or, right-click on the specific area of the chart then select Format Chart Area options to refine
your chart elements with all appropriate labels, etc. as per the example.

Enjoy!





























































































































































































































学霸联盟