ITEC1010 Assignment #2 – Excel Spreadsheets
Due: August 2, 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
- Create one Excel Workbook containing separate worksheets to each of the problems below.
- 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.
- 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.)
- 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. 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.
- Duplicate the Mortgage Calculator model below in your Excel worksheet. 2. Formulae for cells F4 to F7 are as per Figure 1.
- Name all appropriate cells using their labels and reconstruct the formulae replacing cell address references.
- Test by entering assumption inputs to cells C4 to C7 using input examples in Figure 2.
- Now reconstruct the summary table (as per Figure 2 – B9:E11) and populate cells with appropriate labels and named references.
- Format the whole model as per Figure 2 including:
- Model title font set to 12pts and merge-centered (B2:F2)
- “Initial Assumptions” and “Result” merge-centered across 2 columns with Thick Outside
Borders; other data with All Borders
- Summary table labels with Thick Bottom Borders
- Format values appropriately as Number, Currency, or Percentage
- Color fill areas with 3 different colors of your choice
- Bold face displayed data
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 (column J) 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;
Re-create the model below that search the Top IMDB Rated Movies table and returns the attributes of the Rank holding movie.
- Download database file 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
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:
- 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 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
- 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.
- 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.