ITEC1010 Assignment #2 – Excel Spreadsheets
*Ensure that you have complied with the Academic Integrity Quiz requirement (10/10) before
submission in order to receive credit for this assignment. The access window is now open.
• Section. N Due Wednesday, March 25 @11:00 PM. via the upload link.
• Section. O Due Thursday, March 26, @11:00 PM via the upload link.
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
RECOMMENDED: Solve each assignment problem below by following the 5-step problem-solving
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 an Excel Workbook containing the solutions to the problems below.
2. Save the workbook as your full name, e.g., peggy_carter.xlsx (all lowercase). Be sure to save
your workbook as a normal Excel workbook file.
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, Movies, etc. (Ensure to
keep everything compact so that they are easily found and viewable when opened.)
4. You may use the raw data as provided in the examples and supplementary files to test your
5. Be sure to include all required components in appropriate format for each problem
6. Up to 50% penalty will be applied for not using named ranges optimally.
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.
3. Name all appropriate cells using their labels and reconstruct the formulae replacing cell address
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 – B10:E11) and populate cells B11 to E11
with appropriate references.
6. Finally format the whole model as per Figure 2 including:
a. Model title font set to 14pts 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 as shown;
f. Bold face displayed data.
Problem 2: Invoice
Re-create the Sales Invoice model below for purchased products with the format and features shown in
Figure 3. You may use the Item, Quantity and List_Price data as provided.
• Discount Rate, Discount Threshold and HST_Rate amounts should be specified as shown and
referenced in formulae so that any changes in those values will be automatically applied to the
Sales Invoice calculations.
• Compute the Discount values so that e.g., 3.30% is displayed for items when Quantity exceeds
the Discount Threshold (48) but remain blank otherwise (not zeros).
• Sale_Price = the List_Price except when discount applies.
• Total = the item total price for the quantity ordered;
• Subtotal = the total of all line item totals;
• HST = the specified rate (13%) of the Subtotal
• Amount_Due = the sum of Subtotal and HST
Hint: Key functions to use: IF; ISNUMBER;
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.
Hint: Key functions to use: VLOOKUP; MATCH
Figure 4 – Database: Top10
Figure 5 – Searcher
Problem 4: Estimates
A company purchases Olive oil according to this price schedule:
• For the first 1000 Litres, $9.20 per Litre
• For any of the next 3000 Litres $8.80 per Litre
• For any oil beyond 4,000 Litres, $8.50 per Litre
The Purchase Estimate spreadsheet calculates the total price of buying x Litres of oil, where x is a
number input to a cell (Ordered) on the worksheet with values that may fall into any or all of the 3 Tiers
of pricing; Total is the total of Qty, not simply Ordered.
Re-create the model as per below:
1. The model can handle any/all 3 tiers of order scenarios.
2. Format with differentiating colors as per example below.
Hint: Key functions to use: IF
Here are 3 possible output examples you can use to test your solutions:
Figure 6 – Scenario1: Tier 3 Ordered (>4000)
Figure 7 – Scenario2: Tier 2 Ordered (0 -4000)
Figure 8 – Scenario: Tier 1 Ordered (0 – 1000)
Problem 5: Commissions
Re-create the model below that identifies the attributes of the Top Performer of the Month (the sales
Associate with the most accrued sales).
• Download/import the MonthlySales.csv and complete the Monthly_Sales table (Figure 9 Left)
used to record each Associate’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
associate’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 Associate;
Key Functions to use: LOOKUP; COUNTIF; SUMIF; MAX; INDEX; MATCH;
Figure 9 – Monthly Sales Table and Commission Lookup Table
Figure 10 – Performance Table
Figure 11 – Top Performer of the Month
Problem 6: Forecasting
You have an idea for a new service that offers customized training 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 12 – 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.1% 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
0.25% per month. Each month you sign up 1.1% 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 13 – Two Scenarios of Market Capture (Partial View)
Problem 7: Charting
Re-create the Excel chart below (Figure14) that draws on the Forecasting worksheet data comparing the
total customer base under each of the two scenarios.
Figure 14 – 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/Command-C) to the Windows Clipboard.
• Click the edge of the existing chart and paste (CTRL/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 to modify various chart elements.
• 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.
本网站支持 Alipay WeChatPay PayPal等支付方式
E-mail: firstname.lastname@example.org 微信号:vipnxx