程序代写代做代考 Excel database algorithm CS 2034 Data Analytics: Principles and Tools

CS 2034 Data Analytics: Principles and Tools

Assignment 2

Learning Outcomes:

By completing this assignment, you will gain and demonstrate skills relating to

 creating functions and subroutines,

 using compound structures in VBA,

 calling and reusing functions,

 developing algorithms and writing code,

Problem Description:

Let’s imagine a world in which Blockbuster still exists. As an employee of London’s local

Blockbuster, you have been tasked with analyzing data as it relates to customer’s rental

habits. Your boss is trying to purchase new DVDs and wants to know the following:

 How many movies exist in the movie database for each genre.

 The total number of movies borrowed for each genre

 The genre with the largest number of Movies

 The genre with the largest number of total Rentals

 The movie with the largest number of rentals

 The most expensive movie to make

 The movie with the highest profit percent

 The movie with the highest profit for a particular genre

To do this, you will create a series of VBA functions which will be described in detail. For

this assignment you are not allowed to use any of the in-built EXCEL/VBA functions that

analyze data such as MAX, SORT, or MATCH. However, you can use LBound, UBound,

and Debug.Print. You can also use methods for Range to access the properties such as

Cells, Value, Count, Row, and Column.

Download the Assign2.xlsx file. Save as a macro-enabled file (xlsm) to complete the

tasks outlined. This file has sample data and shows you the structure of the file that

should be handed in. Keep in mind that when grading the TA may change the data in the

Jamie

cells. In terms of the genre, it is safe to assume that there will be only 4 genres (Comedy,

Action, Drama, Musical) in the final data set.

The first functions that you will create are helper functions that can be used by other

functions.

 Function FindMax(valueArray, nameArray) As String

This function takes in two arrays; the first array has the numeric values while the

second array has the names. The function will find the value in the first array that

is the largest and return the corresponding name (from the second array).

For instance, if the valueArray = (10, 20, 5, 9) and nameArray is (“House”, “Chair”,

“Table”, “Book”). The function will return Chair

 Function FindMaxRange(valueRng As Range, nameRng As

Range) As String

Similar to the FindMax function, the only difference is that the input values are

ranges.

 Function printMovieData(title As String, arrayTopic,

arrayOther)

This function prints out information about the movies to the Immediate Window.

For instance, if the title is “Movie by Genre”, arrayTopic = (“Horror”, “Comedy”,

“Action”) and arrayOther is (5, 6, 1)

The output to the Immediate Window will be

*****************Movies By Genre**************

Horror : 5

Comedy : 6

Action : 1

********************************************

The functions in this task should use the FindMaxRange helper function

 Function MovieWithLargestRental(rngMovie As Range,

rngMovieRental As Range) As String

This function finds and returns the movie with the largest number of rentals.

There are two input parameters, the first is a range from the worksheet that has

the name of the movies, while the second is a range from the worksheet that has

the number of times each movie has been rented.

 Function MostExpensiveMovieToMake(rngMovie As Range,

rngMovieProduction As Range) As String

This function returns the most expensive movie to make. The first parameter is a

range of the names of the movies, the second parameter is the range of

production costs.

The functions in this task should use the FindMax and PrintMovieData helper

functions

 Function MoviesByGenre(genreRng As Range) As String

This function will do two things. First of all, it will determine the number of

movies in each genre and print it to the immediate window. Secondly, it will

return the genre that has the most number of movies.

 Function MoviesByRental(genreRng As Range, rentalRng As

Range) As String

This function is similar to the previous one. First of all, it will determine the total

number of rentals for each genre and print to the immediate window. Secondly,

it will return the genre that has the most number of rentals.

 Function FindMovieProfit(nameRng As Range,

productionCostRng As Range, boxOfficeRng As Range) As

String

This function will take in a range for the name of the movies, a range for the

production cost of the movies, and a range for the box office earnings for each

movie. The function will determine and return the name of the movie that has

the highest profit percentage. Profit percentage is calculated as

𝑝𝑟𝑜𝑓𝑖𝑡 =
𝑏𝑜𝑥𝑂𝑓𝑓𝑖𝑐𝑒𝐸𝑎𝑟𝑛𝑖𝑛𝑔𝑠 − 𝑝𝑟𝑜𝑑𝑢𝑐𝑡𝑖𝑜𝑛𝐶𝑜𝑠𝑡

𝑝𝑟𝑜𝑑𝑢𝑐𝑡𝑖𝑜𝑛𝐶𝑜𝑠𝑡
× 100

 Function FindMovieProfitByGenre(nameRng As Range,

productionCostRng As Range, boxOfficeRng As Range,

genreRng As Range, genre As String) As String

As a variation on the previous function, this function will determine the movie

that has the highest profit percentage for a specified genre.

Additional helper functions can be created and used.

The name of the file you submit should be your UWO userid_Assign2.xlsm. For instance,

my assignment would be oola_Assign2.xlsm. Make sure you attach the file to your

assignment, DO NOT put the code inline in the textbox.

What You Will Be Assessed On:

1. Formatting
2. Reusability/Functional Modularity