Information Tools For Business

YO16XLCH01GRADERPCHW_-_Golf_Carts_17_Instructions x
Office 2016 – myitlab:grader – Instructions Excel Project

YO16_XL_CH01_GRADER_PC_HW – Golf Carts 1.7

Project Description:
The Red Bluff Golf Course and Pro Shop makes golf carts available to its members for a fee. Recently, the resort has been running out of carts. The time has come for the club to add more golf carts to its fleet. Club manager Barry Cheney, wants to use Microsoft Excel to analyze the purchase of golf carts by model, price, and financing parameters.

Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step Instructions Points Possible
1 Start Excel. Open the downloaded file named e01ch01_grader_pc_GolfCarts.xlsx. Save the file as e01ch01_grader_pc_GolfCarts_LastFirst, using your last and first name. 0.000
2 Click the GolfCartPurchases worksheet tab. In cell E7, type 6495, and then press CTRL+ENTER.
In cell D5, type EZ-GO, and then press ENTER.
Bold cell D13.
In cell A13, type Total Interest Cost: (be sure to add the colon). 4.000
3 Add a Comment to cell A9. Replace the comment title text but not the colon with Annual Interest Rate. After the colon type Annual rate of interest in decimal or percentage format. (Be sure to leave a space after the colon and type a period at the end of the comment.) 4.000
4 Click the Documentation worksheet tab. In cell A8, type the current date. In cell B8, type your name in the Firstname Lastname format. In cell C8, type Added comment to a key heading on the GolfCartPurchases worksheet (no period). 4.000
5 On the GolfCartPurchases worksheet, in cell A2, type Red Bluff Golf Course & Pro Shop.
In cell A3, type Golf Cart Purchase Analysis
In A4, type 6/6/2018
In G4, type Option 5
In D9, type 0.06 4.000
6 On the Documentation worksheet, click cell C8, and then click Wrap Text.
On the GolfCartPurchases worksheet, click cell A3, and then create a hard return after the word Cart. Be sure to delete any spaces between Cart and Purchase. 4.000
7 On the GolfCartPurchases worksheet, cut the contents of cell D5 and paste them to cell C5. Copy the contents of cell D6, and paste them into cell C6. 4.000
8 On the GolfCartPurchases worksheet, copy the contents of cells C5:C6, to cell E5. Copy the contents of cells C5:C6 to cells D5, F5, and G5. 4.000
9 Select cell range A2:A4, and then move the contents of the cells to cell range A1:A3.
Select cell range F7:F13, and then copy the contents of the cells to cell range G7:G13. 4.000
10 In cell D6, type RXV
In cell E6, edit the text to Freedom TXT
In cell F6, type Freedom RXV
In cell G6, type The Drive
In cell G7, type 6995
In cell G8, type 6350
In cell G5, type Yamaha
4.000
11 Move the contents of cell range C4:G13 to the left one column to cell range B4:F13. 4.000
12 Merge and center cell range A1:F1. 4.000
13 Select cell range A2:F3, and then Center Across Selection. 4.000
14 Insert a new row 4
Insert a new row 8
Insert new rows 11:12
Insert new rows 15:16
Delete rows 11 and 15 4.000
15 Insert a column to the left of column C, and then insert a column to the left of columns G, F, and E. 4.000
16 Adjust the column width of columns C, E, G, and I to 2. 4.000
17 Adjust the row height for rows 4, 11 and 14 to 7. 4.000
18 Use AutoFit to adjust the column width of columns A, B, D, F, H, and J. Then,
adjust the width of column A to 26. 5.000
19 Create a new, blank workbook and save the workbook as e01ch01_grader_pc_Mowers_LastFirst.xlsx using your last and first name. Move the MowerPurchase worksheet from the e01ch01_grader_pc_GolfCarts_LastFirst workbook to the e01ch01_grader_pc_Mowers_LastFirst workbook before Sheet1. 4.000
20 Copy the Documentation worksheet from the e01ch01_grader_pc_GolfCarts_LastFirst workbook, to the e01ch01_grader_pc_Mowers_LastFirst workbook. 0.000
21 Create a new worksheet named JuneGolfCartUsage to the right of the MayGolfCartUsage worksheet in the original workbook. Change the worksheet tab color to Blue, Accent 1. Change the tab color of the MayGolfCartUsage worksheet tab to Orange, Accent 2. 4.000
22 Copy the contents of the MayGolfCartUsage worksheet, and paste them to the JuneGolfCartUsage worksheet. On the JuneGolfCartUsage worksheet, clear the contents of A11:A41. Delete row 12. Change the text in H3 to June Totals. In A11, type 06/01/2018. Fill the contents of cell A11 through cell range A12:A40 without formatting. 4.000
23 Move the JuneGolfCartUsage worksheet tab to the left of the MayGolfCartUsage worksheet tab.
Copy the MayGolfCartUsage worksheet tab to a new worksheet named JulyGolfCartUsage.
Change the tab color of the JulyGolfCartUsage worksheet to Gold, Acccent 4. Ensure the JulyGolfCartUsage worksheet is to the left of the JuneGolfCartUsage worksheet.
Change the contents of H3 to July Totals. In cell A11, type 07/01/2018. Fill the contents of cell A11 through cell range A41 without formatting. 7.000
24 Update the Documentation worksheet by inserting the date in A22. In B22, type JuneGolfCartUsage (no period). In C22, type your name in the Firstname Lastname format. In D22, type Golf cart usage analysis (no period).
In A23, enter today’s date. In B23, type JulyGolfCartUsage (no period). In C23, type your name in the Firstname Lastname format. In D23, type Golf cart usage analysis (no period).
Move the Documentation worksheet to the right of the MayGolfCartUsage worksheet. 4.000
25 Scale the MayGolfCartUsage, JuneGolfCartUsage, and JulyGolfCartUsage worksheets to fit all columns on one page, and then to have narrow margins. 6.000
26 Change the Documentation worksheet to Landscape Orientation and change the Print Settings to print the entire workbook. 2.000
27 Export the e01ch01_grader_pc_GolfCarts_LastFirst workbook to a PDF. Save the PDF in a location where you are storing your files as e01ch01_grader_pc_GolfCarts_LastFirst. 0.000
28 Ensure that the worksheets are correctly named and placed in the following order in the workbook: GolfCartPurchases, JulyGolfCartUsage, JuneGolfCartUsage, MayGolfCartUsage, and Documentation. Save the workbook. Close the workbook and exit Excel. Submit the workbook as directed. 0.000
Total Points 100.000

Updated: 09/11/2017 1 Current_Instruction x

Nyarko_e01ch01_grader_pc_GolfCarts.xlsx

Documentation

Create Date
By Whom
Description
Workbook Name

5/15/18
Barry Cheney
Analyze the replacement of the Red Bluff Golf Course fleet of golf carts
ERROR:#N/A

Mod. Date
By Whom
Mod. Description
Last Version Backup Name

: VERSION BACKUP NAME :
Before modifying any worksheet, save the original workbook with the following name format:
Original name_yyyymmdd

Create Date
Sheet Name
Creator
Purpose

5/15/18
GolfCartPurchases
Barry Cheney
Compare price, financing terms, payments, and total cost of golf carts under consideration

5/15/18
MayGolfCartUsage
Barry Cheney
Golf cart usage analysis

5/15/18
MowerPurchase
Barry Cheney
Fairway mower purchase analysis

GolfCartPurchases

Option 1
Option 2
Option 3
Option 4

Golf Cart Brand:

Model:

TXT

Retail Price:

Timothy O’Keefe: Retail Price
Commonly known as Manufacturer’s Suggested Retail Price, or MSRP

8,137
8,195

7,135

Purchase Price:

Timothy O’Keefe: Purchase Price
What you must actually pay to purchase the golf cart

6,495
6,515
5,995
6,495

Loan Annual Interest Rate:

0.060
0.070
0.060
0.060

Number of Payments:

48
48
48
48

Monthly Payment:

152.54
156.01
140.79
152.54

Total Purchase Cost:

7,321.69
7,488.47
6,758.05
7,321.69

826.69
973.47
763.05
826.69

MayGolfCartUsage

Analysis of Cart Usage

Revenue Summary

May Totals
If Additional Carts Existed

$ 25.00
Cost of Cart Rental

1
3

3
Maximum # of times per day a cart may be used

$ Demand Not Met

jkinser: $ Demand Not Met:
If demand was greater than the number available, the resort turned away customers. This is the revenue amount that the resort could have made if customers had not been turned away.

$ 8,800
$ 6,925
$ 3,500
$ 1,425
$ 300
$ 75

$ Demand Not Used

jkinser: $ Demand Not Used:
If demand was less than the number available, the resort has excess carts not being used. This is the revenue amount that the resort could have made if customers had demanded the cart.

$ 525
$ 975
$ 2,200
$ 4,775
$ 8,300
$ 12,725

Total Lost Demand

jkinser: Total Lost Demand:
A combination of both revenue lost to demand not met and demand not used.

$ 9,325
$ 7,900
$ 5,700
$ 6,200
$ 8,600
$ 12,800

Additional Carts

May Cart Usage

Day
# Available

jkinser: # Available rentals:
Each cart can be rented up to 3 times per day. This is the maximum number of rentals for the day. Some days carts are out of service for repair.

jkinser: Total Lost Demand:
A combination of both revenue lost to demand not met and demand not used.

Demand

jkinser: Demand:
The number of carts that were requested for rental that day. If all carts were out, then the demand is higher than the # of available rentals.

$ Demand Not Met

$ Demand Not Used

$ Demand Not Met

$ Demand Not Used

$ Demand Not Met

$ Demand Not Used

$ Demand Not Met

$ Demand Not Used

$ Demand Not Met

$ Demand Not Used

$ Demand Not Met

$ Demand Not Used

5/1/18
30
60
$ 750.00
$ – 0

$ 675.00
$ – 0
$ 525.00
$ – 0
$ 375.00
$ – 0
$ 225.00
$ – 0
$ 75.00
$ – 0

5/2/18
30
43
$ 325.00
$ – 0

$ 250.00
$ – 0
$ 100.00
$ – 0
$ – 0
$ 50.00
$ – 0
$ 200.00
$ – 0
$ 350.00

5/3/18
27
41
$ 350.00
$ – 0

$ 275.00
$ – 0
$ 125.00
$ – 0
$ – 0
$ 25.00
$ – 0
$ 175.00
$ – 0
$ 325.00

5/4/18
30
32
$ 50.00
$ – 0

$ – 0
$ 25.00
$ – 0
$ 175.00
$ – 0
$ 325.00
$ – 0
$ 475.00
$ – 0
$ 625.00

5/5/18
24
48
$ 600.00
$ – 0

$ 525.00
$ – 0
$ 375.00
$ – 0
$ 225.00
$ – 0
$ 75.00
$ – 0
$ – 0
$ 75.00

5/6/18
30
40
$ 250.00
$ – 0

$ 175.00
$ – 0
$ 25.00
$ – 0
$ – 0
$ 125.00
$ – 0
$ 275.00
$ – 0
$ 425.00

5/7/18
30
31
$ 25.00
$ – 0

$ – 0
$ 50.00
$ – 0
$ 200.00
$ – 0
$ 350.00
$ – 0
$ 500.00
$ – 0
$ 650.00

5/8/18
30
44
$ 350.00
$ – 0

$ 275.00
$ – 0
$ 125.00
$ – 0
$ – 0
$ 25.00
$ – 0
$ 175.00
$ – 0
$ 325.00

5/9/18
30
26
$ – 0
$ 100.00

$ – 0
$ 175.00
$ – 0
$ 325.00
$ – 0
$ 475.00
$ – 0
$ 625.00
$ – 0
$ 775.00

5/10/18
21
42
$ 525.00
$ – 0

$ 450.00
$ – 0
$ 300.00
$ – 0
$ 150.00
$ – 0
$ – 0
$ – 0
$ – 0
$ 150.00

5/11/18
30
40
$ 250.00
$ – 0

$ 175.00
$ – 0
$ 25.00
$ – 0
$ – 0
$ 125.00
$ – 0
$ 275.00
$ – 0
$ 425.00

5/12/18
30
48
$ 450.00
$ – 0

$ 375.00
$ – 0
$ 225.00
$ – 0
$ 75.00
$ – 0
$ – 0
$ 75.00
$ – 0
$ 225.00

5/13/18
30
39
$ 225.00
$ – 0

$ 150.00
$ – 0
$ – 0
$ – 0
$ – 0
$ 150.00
$ – 0
$ 300.00
$ – 0
$ 450.00

5/14/18
15
34
$ 475.00
$ – 0

$ 400.00
$ – 0
$ 250.00
$ – 0
$ 100.00
$ – 0
$ – 0
$ 50.00
$ – 0
$ 200.00

5/15/18
24
32
$ 200.00
$ – 0

$ 125.00
$ – 0
$ – 0
$ 25.00
$ – 0
$ 175.00
$ – 0
$ 325.00
$ – 0
$ 475.00

5/16/18
30
30
$ – 0
$ – 0

$ – 0
$ 75.00
$ – 0
$ 225.00
$ – 0
$ 375.00
$ – 0
$ 525.00
$ – 0
$ 675.00

5/17/18
30
50
$ 500.00
$ – 0

$ 425.00
$ – 0
$ 275.00
$ – 0
$ 125.00
$ – 0
$ – 0
$ 25.00
$ – 0
$ 175.00

5/18/18
30
50
$ 500.00
$ – 0

$ 425.00
$ – 0
$ 275.00
$ – 0
$ 125.00
$ – 0
$ – 0
$ 25.00
$ – 0
$ 175.00

5/19/18
30
49
$ 475.00
$ – 0

$ 400.00
$ – 0
$ 250.00
$ – 0
$ 100.00
$ – 0
$ – 0
$ 50.00
$ – 0
$ 200.00

5/20/18
27
37
$ 250.00
$ – 0

$ 175.00
$ – 0
$ 25.00
$ – 0
$ – 0
$ 125.00
$ – 0
$ 275.00
$ – 0
$ 425.00

5/21/18
27
33
$ 150.00
$ – 0

$ 75.00
$ – 0
$ – 0
$ 75.00
$ – 0
$ 225.00
$ – 0
$ 375.00
$ – 0
$ 525.00

5/22/18
27
43
$ 400.00
$ – 0

$ 325.00
$ – 0
$ 175.00
$ – 0
$ 25.00
$ – 0
$ – 0
$ 125.00
$ – 0
$ 275.00

5/23/18
30
24
$ – 0
$ 150.00

$ – 0
$ 225.00
$ – 0
$ 375.00
$ – 0
$ 525.00
$ – 0
$ 675.00
$ – 0
$ 825.00

5/24/18
30
20
$ – 0
$ 250.00

$ – 0
$ 325.00
$ – 0
$ 475.00
$ – 0
$ 625.00
$ – 0
$ 775.00
$ – 0
$ 925.00

5/25/18
30
40
$ 250.00
$ – 0

$ 175.00
$ – 0
$ 25.00
$ – 0
$ – 0
$ 125.00
$ – 0
$ 275.00
$ – 0
$ 425.00

5/26/18
30
42
$ 300.00
$ – 0

$ 225.00
$ – 0
$ 75.00
$ – 0
$ – 0
$ 75.00
$ – 0
$ 225.00
$ – 0
$ 375.00

5/27/18
30
38
$ 200.00
$ – 0

$ 125.00
$ – 0
$ – 0
$ 25.00
$ – 0
$ 175.00
$ – 0
$ 325.00
$ – 0
$ 475.00

5/28/18
30
29
$ – 0
$ 25.00

$ – 0
$ 100.00
$ – 0
$ 250.00
$ – 0
$ 400.00
$ – 0
$ 550.00
$ – 0
$ 700.00

5/29/18
24
35
$ 275.00
$ – 0

$ 200.00
$ – 0
$ 50.00
$ – 0
$ – 0
$ 100.00
$ – 0
$ 250.00
$ – 0
$ 400.00

5/30/18
30
37
$ 175.00
$ – 0

$ 100.00
$ – 0
$ – 0
$ 50.00
$ – 0
$ 200.00
$ – 0
$ 350.00
$ – 0
$ 500.00

5/31/18
30
50
$ 500.00
$ – 0

$ 425.00
$ – 0
$ 275.00
$ – 0
$ 125.00
$ – 0
$ – 0
$ 25.00
$ – 0
$ 175.00

MowerPurchase

Red Bluff Golf Course & Pro Shop

Fairway Mower
Purchase Analysis

Option 1

Option 2

Option 3

Option 4

Option 5

Golf Cart Brand:
Toro

Toro

Jacobsen

John Deere

Model:
6700-D

7000-D

LF-570

LF-4677

7500 E-Cut Hybrid

Retail Price:

Timothy O’Keefe: Retail Price
Commonly known as Manufacturer’s Suggested Retail Price, or MSRP

52,995.00
52,995.00
52,995.00

56,995.00
56,995.00
56,995.00

53,595.00
53,595.00
53,595.00

60,695.00
60,695.00
60,695.00

54,796.00
54,796.00
54,796.00

Purchase Price:

Timothy O’Keefe: Purchase Price
What you must actually pay to purchase the golf cart

47,995.00
47,995.00
47,995.00

51,995.00
51,995.00
51,995.00

48,995.00
48,995.00
48,995.00

54,995.00
54,995.00
54,995.00

49,995.00
49,995.00
49,995.00

Annual Interest Rate:

TPO: Annual Interest Rate
Annual rate of interest in decimal or percentage format, e.g. 5% is entered as 0.05 or 5%

5.00%
5.50%
6.00%

# of Payments:

TPO: # of Payments
Total number of payments over the term of the loan

24
36
48

Monthly Payment:
$ 2,105.61
$ 1,449.25
$ 1,127.16

$ 2,281.09
$ 1,570.04
$ 1,221.10

$ 2,149.48
$ 1,479.45
$ 1,150.65

$ 2,412.71
$ 1,660.62
$ 1,291.56

$ 2,193.35
$ 1,509.64
$ 1,174.13

Total Purchase Cost:
50,534.58
52,173.08
54,103.87

54,746.23
56,521.29
58,613.00

51,587.49
53,260.14
55,231.15

57,904.97
59,782.45
61,994.84

52,640.40
54,347.19
56,358.43

Total Interest Cost:
2,539.58
4,178.08
6,108.87

2,751.23
4,526.29
6,618.00

2,592.49
4,265.14
6,236.15

2,909.97
4,787.45
6,999.84

2,645.40
4,352.19
6,363.43

image1

Turn in your highest-quality paper
Get a qualified writer to help you with

“ Information Tools For Business ”

Get high-quality paper

Guarantee! All work is written by expert writers!

Still stressed from student homework?

Get quality assistance from academic writers!

Order now