Fluent Essays

Excel Chapter 1 Prepare Case - Golf Cart Grader - Instructions Excel 2019 Project YO19_Excel_Ch01_Prepare_Golf_Cart_Analysis 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 due to wear and tear. 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. Steps to Perform: Step Instructions Points Possible 1 Start Excel. Download and open the file named Excel_Ch01_Prepare_GolfCartAnaylsis.xlsx. Grader has automatically added your last name to the beginning of the filename. 0 2 The GolfCartAnalysis workbook provides data to analyze the purchase of golf carts by model, price, and financing.The information on the GolfCartPurchases needs to be updated. Click the GolfCartPurchases worksheet tab to make it the active worksheet. In cell E7, type 6742 In cell D5, type Easy GO Bold cell D13. In cell A13, type Total Interest Cost: 0.4 3 Workbooks are often shared with others. Therefore, it is important to document changes you may make to a workbook. In cell A9, document your work by adding a note. In the Note box, delete the user name and the colon that is automatically inserted into the comment. Type Annual Interest Rate: Annual rate of interest in decimal or percentage format 0.2 4 Continue documenting your workbook on the Documentation worksheet tab. In cell A8, type the date 6/1/2022 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 0.2 5 Update the GolfCartPurchases worksheet, by entering the following: In cell A2, type Red Bluff Golf Course & Pro Shop In cell A3, type Golf Cart Purchase Analysis In cell A4, type 06/06/2022 In cell G4, type Option 5 In cell D9, type .06 0.4 6 Wrapping text in a cell can improve the look of your worksheet. On the GolfCartPurchases worksheet, click cell A3, and then create a hard return - two or more lines of text in one cell - after the word Cart. Be sure to delete any spaces between Cart and Purchase. On the Documentation worksheet, using the Wrap Text feature, wrap the text in cell cell C8. 0.4 7 Once you have a worksheet created, you may want to make changes to the look of the worksheet by moving text and data to a different location on a worksheet. On the GolfCartPurchases worksheet, cut the contents of cell D5 and then paste the contents in cell C5. Copy the contents of cell D6, and then paste the contents in cell C6. 0.4 8 On the GolfCartPurchases worksheet, copy the contents of cell range C5:C6 to cell range D5:G5. 0.4 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. 0.4 10 In cell D6, type Freedom RXV In cell E6, edit the text to Freedom TRZ In cell F6, type Freedom RXV In cell G5, type Yamaha In cell G6, type The Drive In cell G7, type 9164 In cell G8, type 7748 0.4 11 To improve the look of the worksheet headings, wrap the text of cell range C6:G6. 0.2 12 Move the contents of cell range C4:G13 to the left one column by deleting B4:B13. 0.4 13 There are several ways to center worksheet titles. One way is to merge cells into one cell and then center the title within that cell. Merge and center cell range A1:F1 to center the title over the worksheet columns. 0.4 14 Another way to center worksheet titles, is to center the text across a selection. Center cell range A2:F3 across selection. 0.4 15 Sometimes adding white space to a worksheet may improve its readability. To improve the readability of the GolfCartPurchases worksheet, make the following changes: Insert a new row 4 Insert a new row 8 Insert new rows 11:12 Insert new rows 15:16 **Please note, if you receive an error, no points are being deducted for the item. There is an issue in the software. 0.32 16 By adding extra columns or rows, you can also add too much white space on a worksheet. After adding rows to the GolfPartPurchases worksheet there is now too much white space. To remove some of the extra white space, delete rows 11 and 15. 0.28 17 To separate the data for the mower types, blank columns will be added to the worksheet. Insert a column to the left of column C, and then insert a column to the left of columns G, F, and E. 0.2 18 Column widths can be adjusted to improve the spacing of a worksheet. Adjust the column width of columns C, E, G, and I to 2 0.4 19 Row heights can also be adjusted to improve the spacing of a worksheet. Adjust the row height for rows 4, 8, 11 and 14 to 10 0.4 20 Adjust the width of columns B, D, F, H, and J to 9 Adjust the width of column A to 26 0.4 21 Delete the MowerPurchase worksheet. 0.4 22 Workbooks can contain multiple worksheets. While working with a workbook, you may decide you have to add additional worksheets. To expand the workbook to include June's golf cart usage, you will add an additional worksheet to the workbook. Create a new worksheet to the right of the MayUsage worksheet. Name the new worksheet JuneUsage Change the JuneUsage worksheet tab color to Blue, Accent 1. Change the MayUsage worksheet tab color to Orange, Accent 2. 0.6 23 To save time entering data on the JuneUsage worksheet, copy the entire contents of the MayUsage worksheet, and paste the contents to the JuneUsage worksheet. On the JuneUsage worksheet, clear the contents of cell range A8:A38. Delete row 10. In cell A6, replace the word May with June In cell A8, type 06/01/2022 and then fill the contents of cell A8 through cell range A8:A37 without formatting. 0.2 24 Move the JuneUsage worksheet tab to the left of the MayUsage worksheet tab. Copy the MayUsage worksheet tab to a new worksheet named JulyUsage Change the tab color of the JulyUsage worksheet to Gold, Acccent 4. Move the JulyUsage worksheet to the left of the JuneUsage worksheet. Change the contents of A6 from May to July In cell A8, type 07/01/2022 Fill the contents of cell A8 through cell range A8:A38 without formatting. 0.6 25 June and July demand have not yet been determined so you want to clear the May golf cart usage data to prepare the worksheet for future data entry. On the JuneUsage worksheet, delete cell range C8:C37. On the JulyUsage worksheet, delete cell range C8:C38. 0.2 26 Update the Documentation worksheet by completing the following: In cell A22, type 6/1/2022 In cell B22, type JuneUsage In cell C22, type your name in the Firstname Lastname format. In cell A23, enter 6/1/2022 In cell B23, type JulyUsage In cell C23, type your name in the Firstname Lastname format. In cell D23, type Golf cart usage analysis Move the Documentation worksheet to the right of the MayUsage worksheet. 0.4 27 On the GolfCartPurchases worksheet, add the File Name code in the center footer. 0.2 28 Scale the MayUsage, JuneUsage, and JulyUsage worksheets to fit all rows on one page, and then to have wide margins. 0.6 29 Change the Documentation worksheet to Landscape Orientation and change the Print Settings to print the entire workbook. 0.2 30 Ensure that the worksheets are correctly named and placed in the following order in the workbook: GolfCartPurchases, JulyUsage, JuneUsage, MayUsage, and Documentation. Save and close Excel_Ch01_Prepare_GolfCartAnalysis.xlsx. Exit Excel. Submit the file as directed. 0 Total Points 10 Created On: 01/27/2020 1 YO19_Excel_Ch01_Prepare - Golf Cart Analysis 1.2 Documentation Create Date By Whom Description Workbook Name 5/15/22 Barry Cheney Analyze the replacement of the Red Bluff Golf Course fleet of golf carts e01ch01_PermLog_kaj-1_DE_to_AU_042218.xlsx 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/22 GolfCartPurchases Barry Cheney Compare price, financing terms, payments, and total cost of golf carts under consideration 5/15/22 MayUsage Barry Cheney Golf cart usage analysis 5/15/22 MowerPurchase Barry Cheney Fairway mower purchase analysis GolfCartPurchases Option 1 Option 2 Option 3 Option 4 Golf Cart Brand: Model: TRZ Retail Price: 8,137 8,195 7,135 Purchase Price: 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 MayUsage Analysis of Cart Usage $ 25.00 Cost of Cart Rental 3 Maximum # of times per day a cart may be used 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. 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 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. $ 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. 5/1/22 30 60 $ 750.00 $ - 0 5/2/22 30 43 $ 325.00 $ - 0 5/3/22 27 41 $ 350.00 $ - 0 5/4/22 30 32 $ 50.00 $ - 0 5/5/22 24 48 $ 600.00 $ - 0 5/6/22 30 40 $ 250.00 $ - 0 5/7/22 30 31 $ 25.00 $ - 0 5/8/22 30 44 $ 350.00 $ - 0 5/9/22 30 26 $ - 0 $ 100.00 5/10/22 21 42 $ 525.00 $ - 0 5/11/22 30 40 $ 250.00 $ - 0 5/12/22 30 48 $ 450.00 $ - 0 5/13/22 30 39 $ 225.00 $ - 0 5/14/22 15 34 $ 475.00 $ - 0 5/15/22 24 32 $ 200.00 $ - 0 5/16/22 30 30 $ - 0 $ - 0 5/17/22 30 50 $ 500.00 $ - 0 5/18/22 30 50 $ 500.00 $ - 0 5/19/22 30 49 $ 475.00 $ - 0 5/20/22 27 37 $ 250.00 $ - 0 5/21/22 27 33 $ 150.00 $ - 0 5/22/22 27 43 $ 400.00 $ - 0 5/23/22 30 24 $ - 0 $ 150.00 5/24/22 30 20 $ - 0 $ 250.00 5/25/22 30 40 $ 250.00 $ - 0 5/26/22 30 42 $ 300.00 $ - 0 5/27/22 30 38 $ 200.00 $ - 0 5/28/22 30 29 $ - 0 $ 25.00 5/29/22 24 35 $ 275.00 $ - 0 5/30/22 30 37 $ 175.00 $ - 0 5/31/22 30 50 $ 500.00 $ - 0 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 Jacobsen John Deere Model: 6700-D 7000-D LF-570 LF-4677 7500 E-Cut Hybrid Retail Price: 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: 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: 5.00% 5.50% 6.00% 5.00% 5.50% 6.00% 5.00% 5.50% 6.00% 5.00% 5.50% 6.00% 5.00% 5.50% 6.00% # of Payments: 24 36 48 24 36 48 24 36 48 24 36 48 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