Create an Excel Workbook containing the solutions to the problems and ave the workbook as your full name, e.g., peggy_carter.xlsx

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

 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.

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 PROCEEDING 

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.

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

Attachments:

Related Questions

. The fundamental operations of create, read, update, and delete (CRUD) in either Python or Java

CS 340 Milestone One Guidelines and Rubric  Overview: For this assignment, you will implement the fundamental operations of create, read, update,

. Develop a program to emulate a purchase transaction at a retail store. This  program will have two classes, a LineItem class and a Transaction class

Retail Transaction Programming Project  Project Requirements:  Develop a program to emulate a purchase transaction at a retail store. This

. The following program contains five errors. Identify the errors and fix them

7COM1028   Secure Systems Programming   Referral Coursework: Secure

. Accepts the following from a user: Item Name Item Quantity Item Price Allows the user to create a file to store the sales receipt contents

Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip

. The final project will encompass developing a web service using a software stack and implementing an industry-standard interface. Regardless of whether you choose to pursue application development goals as a pure developer or as a software engineer

CS 340 Final Project Guidelines and Rubric  Overview The final project will encompass developing a web service using a software stack and impleme