Resort Bicycle Rental, programming homework help

please do the problem listed in the pictures and answer all ?’s. all necessary files are located below including the excel file at which to do the work on.  there is also a pdf file of corrections that need to be applied to the assignment . when working on the assignment use the numbers listed in the pictures. the purpose is to come up with the correct formulas for the boxes and the formulas need to be on the file, u cant just copy the numbers. if you have any other ?’s feel free to ask ASAP

Dear IS 3100 Students,
Your task is to create, complete, and submit an Excel workbook based on Application Exercise
3-1 on page 507 of the Kroenke textbook.
You are required to complete and save an Excel file and submit it under the Excel Task link on
the Dropbox tab on D2L.
How to approach this task?




Please download and save the attached template document(s) to your computer.
Please follow the instructions in the textbook for all of the requirements.
Please use the Excel1Template.xls file.
Please save the workbook with your LastnameExcel1.xlsx.
Your response is required as follows:
1. Your completed workbook must be authentic; it must be your own original work. Any
plagiarism will result in a failing grade for the course.
2. Please make sure the file is attached before you submit.
3. You may use the Questions about Excel Task thread on the Discussion Board to ask
questions about the task and exchange ideas with your classmates.
Criterion
Description
Structure
A completed and organized workbook is present and
according to instructions.
20
Formulas
All formulas are present and correct. Each incorrect
formula will be a 5 point deduction.
30
Figures
(numbers)
All figures are present and correct to reflect a logical
response to the problem raised in the business case. Each
incorrect figure will be a 5 point deduction.
30
Solution
A complete and correct logical response to questions f and
g is present as based on the facts, formulas, and figures of
the case.
20
Total:
Maximum
points
100
Your
Points
0
Excel Task Corrections
Please use the following corrected items (b and d) for Excel Exercise Project on page 507.
b. Place the current date so that it is centered in cells C3, D3, and E3, which must be merged.
d. Figure AE-1 uses the following formulas:
Cost of Current Inventory = Bike Cost X 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
Tutoring is available for this course:
http://coles.kennesaw.edu/programs/undergraduate/tutoring.php
Criterion
Description
Structure
A completed and organized workbook is present and according
to instructions.
Formulas
All formulas are present and correct. Each incorrect formula
will be a 5 point deduction.
Figures
(numbers)
All figures are present and correct to reflect a logical response
to the problem raised in the business case. Each incorrect figure
will be a 5 point deduction.
Solution
A complete and correct logical response to questions f and g is
present as based on the facts, formulas, and figures of the case.
Total:
Maximum
points
Your
Points
20
30
30
20
100
0
Please respond to questions f and g below:
Response to question f:
Response to question g:
Chapter 3
3-1.
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. Note the
following:
.
The top heading is in 20-point Calibri font. It is centered in the spreadsheet. Cells
Al through Hi have been merged.
The second heading, Bicycle Inventory Valuation, is in 18-point Calibri, italics. It
is centered in cells A2 through H2, which have been merged.
The column headings are set in 11-point Calibri, bold. They are centered in their
cells, and the text wraps in the cells.
a. Make the first two rows of your spreadsheet similar to that in Figure AE-1. Choose
your own colors for background and type, however.
b. Place the current date so that it is centered in cells C3, C4, and C5, which must be merged.
c. Outline the cells as shown in Figure AE-1.
507
C
G
H
1
Resort Bicycle Rental
Bicycle Inventory Valuation
2
Saturday, May 31, 2014
3
Number of
Rentals
85
34
Make of Bike
5 Wonder Bike
6 Wonder Bike II
7 Wonder Bike Supreme
8 LiteLift Pro
9 LiteLift Ladies
10 LiteLift Racer
Bike Cost
$325
$385
$475
$655
$655
$795
Number on Cost of Current
Hand
Inventory
12
$3,900
4
$1,540
8
$3,800
8
$5,240
4
$2,620
3
$2,385
Total Rental
Revenue
$6,375
$4,570
$5,200
$2,480
$6,710
$5,900
Revenue as
Revenue per Percent of Cost
Bike of Inventory
$531
163.5%
$1,143
296.8%
$650
136.8%
$310
47.3%
$1,678
256.1%
$1,967
247.4%
44
25
Figure AE-1
Excel Spreadsheet
40
37
yo
d. Figure AE-1 uses the following formulas:
Cost of Current Inventory = Bike Cost X Number on Hand
Revenue per Bike = Rental Revenue / Nu
Number on Hand
Revenue as a Percent of Cost of Inventory = Total Rental Revenue Cost
of Current Inventory
Use these formulas in your spreadsheet, as shown in Figure AE-1.
e. Format the cells in the columns, as shown.
f. Give three examples of decisions that managernent of the bike rental agency might
make from this data.
g. 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 your worksheet
document that has this calculation.