| 这是York University经济带点编程的代写案例 |
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.
- 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
- Duplicate the Mortgage Calculator model below in your Excel worksheet.
- 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:
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
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 (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;
Problem 3: Movies
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 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
数论考试认准Uprivateta
信息论代写
AP/ITEC1010 3.0 N: Information and Organizations
Offered by: School of Information TechnologyPrint
Session
Winter 2021
Term
W
Format
LECT
Calendar Description / Prerequisite / Co-Requisite
The value and importance of information to organizations, how it is used, stored and processed; emphasizes the uses of information technologies of various kinds, the benefits of the technologies, and the associated costs and problems; use of desktop applications. Course credit exclusion: GL/ITEC 1010 3.00.
Course Start Up
Course Websites hosted on York’s “eClass” are accessible to students during the first week of the term. It takes two business days from the time of your enrolment to access your course website. Course materials begin to be released on the course website during the first week. To log in to your eClass course visit the York U eClass Portal and login with your Student Passport York Account. If you are creating and participating in Zoom meetings you may also go directly to the York U Zoom Portal.
For further course Start Up details, review the Next Steps webpage.
For IT support, students may contact University Information Technology Client Services via [email protected] or (416) 736-5800. Please also visit Students Getting Started UIT or the Getting Help – UIT webpages.
Relevant Links / ResourcesSenate Policies
- Academic Honesty
- Student Rights and Responsibilities
- Religious Observance
- Grading Scheme and Feedback
- 20% Rule
No examinations or tests collectively worth more than 20% of the final grade in a course will be given during the final 14 calendar days of classes in a term. The exceptions to the rule are classes which regularly meet Friday evenings or on Saturday and/or Sunday at any time, and courses offered in the compressed summer terms. - Academic Accommodation for Students with Disabilities
Academic Information and Services
Other Services for Students
