MIS 3003 Chapter 3
Be careful! All instructions here are not for your homework questions, but just some very similar questions!!!
1. Excel Part
Figure AE-1 shows an Excel spreadsheet that the resort bicycle rental business uses to value and analyze its bicycle inventory. Examine this figure to understand the meaning of the data. Now use Excel to create a similar spreadsheet. (4 pts)
Figure AE-1 uses the following formulas:
Cost of Current Inventory = Bike Cost * Number on Hand.
Revenue per Bike = Total Rental Revenue / Number on Hand.
Revenue as a Percent of Cost of Inventory = Total Rental Revenue / Cost of Current Inventory.
Use these formulas in our spreadsheet, as shown in Figure AE-1.
Input raw data as the Figure AE-1.
|Make of Bike||Bike Cost||Number on Hand||Number of Rentals||Total Rental Revenue|
|Wonder Bike II||$385||4||34||$4,570|
|Wonder Bike Supreme||$475||8||44||$5,200|
It’s a simple question, you can input or copy & paste the existing data into a blank excel spreadsheet.
Now, add three new column titles: Cost of Current Inventory, Revenue per Bike, and Revenue as a Percent of Cost of Inventory.
Remember in Ch2, how to solve the problem, here we are defining our evaluation criteria.
In F5, enter the formula: “=B5*C5”, refer to: Cost of Current Inventory = Bike Cost * Number on Hand.
In G5, enter the formula: “=E5/C5”, refer to: Revenue per Bike = Total Rental Revenue / Number on Hand.
In H5, enter the formula: “=E5/F5”, refer to: Revenue as a Percent of Cost of Inventory = Total Rental Revenue / Cost of Current Inventory.
Use fill handle to copy the formula through F5 to F10, G5 to G10, H5 to H10.
More detail about fill handle and automatic fill data, check: https:// support.office.com/en-us/article/Fill-data-automatically-in-worksheet-cells-74e31bdd-d993-45da-aa82-35a236c5b5db
You can press ctrl + ` to check all formulas, more details in: https:// support.office.com/en-us/article/Display-or-hide-formulas-f7f5ab4e-bf24-4efc-8fc9-0c1b77a5356f.
B. Give three examples of decisions that management of the bike rental agency might make from his data.
Use your own words, and thinking to answer this question, for example, LiteLift Pro Bike have lowest revenue on invest, you may want to reduce further investment on this model.
C. What other calculation could you make from this data that would be useful to the bike rental management? Create a second version of this spreadsheet in you worksheet document that has this calculation.
You can copy and paste to create a new spreadsheet, and you can do any simple calculation on this raw data. For example: Rental times = Number of Rentals / Number on Hand, and more rental times means higher usability.
Do not use my example to answer this question.
2. Access Part
In this exercise, you will learn how to create a query based on data that a user enters and how to use that query to create a data entry form.
Download the Microsoft Access file Ch03Ex02_U7e.accdb. Open the file and familiarize yourself with the data in the Customer table.
Click Create in the Access ribbon. Click the icon labeled Query Design. Select the Customer table as the basis for the query by double-clicking on Customer. Close the Show Table dialog. Drag CustomerName, CustomerEmail, DateOfLastRental, BikeLastRented, TotalNumberOfRentals, and TotalRentalRevenue into the columns of the query results pane (the table at the bottom of the query design window).
In the CustomerName column, in the row labeled Criteria, place the following text:
[Enter Name of Customer:]
Type this exactly as shown, including the square brackets. This notation tells Access to ask you for a customer name to query.
In the ribbon, click the red exclamation mark labeled Run. Access will display a dialog box with the text “Enter Name of Customer:” (the text you entered in the query Criteria row). Enter the value Maple, Rex and click OK.
Save your query with the name Parameter Query.
Click the Home tab on the ribbon and click the Design View (upper left-hand button on the Home ribbon). Clear the text in the Criteria column of the CustomerName column with the following text. Type it exactly as shown:
Like “*”&[Enter part of Customer Name to search by:]&”*”
Run the query by clicking Run on the ribbon. Enter Maple when prompted Enter part of Customer Name to search by. Notice that the two customers who have the name Maple and displayed. If you have any problems, ensure that you have typed the phrase above exactly as shown in the Criteria row of the CustomerName column of your query.
“Save as” your query again under the name Parameter Query2. Close the query window.
Click Create on the Access ribbon. Under the Forms group, choose Form Wizard. In the dialog that opens, in the Table/Queries box, click the down arrow. Select Query: Parameter Query2. Click the double chevron << symbol and all of the columns in the query will move to the Selected Field area.
Click Next two times. In the box under What title do you want for your form? Enter Customer Query Form and click Finish.
Enter Maple in the dialog box that appears. Access will open a form with the values for Maple, Rex. At the bottom of the form, click the right-facing arrow and the data for Maple, Nichole will appear.
Close the form. Select Object Type and Forms in the Access Navigation Pane. Double click the Customer Query Form and enter the value Amanda. Access will display data for all four customers having the value Amanda in their name.