# Excel代写 | ITEC1010 Assignment #2 – Excel Spreadsheets

这个作业是用Excel表格完成发票、电影等相关的数据操作

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

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 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

solutions.

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

solution.

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.

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 – 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.

Figure 2

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;

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.

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;

Properties_Sold; Commision_Earned.

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.

Enjoy!