xuebaunion@vip.163.com

3551 Trousdale Rkwy, University Park, Los Angeles, CA

留学生论文指导和课程辅导

无忧GPA：https://www.essaygpa.com

工作时间：全年无休-早上8点到凌晨3点

微信客服：xiaoxionga100

微信客服：ITCS521

excel代写-ITEC1010

时间：2021-03-29

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!

学霸联盟

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!

学霸联盟