How to configure this steps?
Part B: Complete the following:
This worksheet captures information regarding some of Gatchell Bikes Ltds best customers. You will clean and format the data in the Members table. Complete the following:
1.Select the members worksheet.
- Insert a table column to the left of column B (the First Name column).
- Rename this new table column from Column1 to Proper Last.
- In the Proper Last column, use the PROPER command to convert the last names from the Last Name column. (leave both columns displaying the last names do not delete any column)
- Insert a table column to the left of column E (CustomerID).
- Using the full range of City Province names (should be D2:D45), use the text to columns feature to split the City and Province names. You will use the comma (,) as the delimiter.
- Rename the newly inserted Column1 (should be column E), as Province
- Rename the City Province column as City.
- Insert a table column to the left of column G (Phone).
- Rename this new column to be CustID.
- The Customer ID column has a code indicating a Gatchell Bikes Ltd customer by using the 3 letters GBL followed by 3 numbers as the main code. Some Customers have a sub-code comprised of a and 2 digits following the main customer code. We want to capture only the main customer code in the column CustID. Use the LEFT function to capture only the main customer code. (do not delete the Customer ID column).
- Use auto-width to widen the column containing phone numbers so that you can see them properly and apply the Phone Number format to these phone numbers.
- We are going to create a new field for the table. Select the cell to the right of the column with the name Sales Date. (this should be cell J1) and call it Month.
- Use a formula and custom formatting so that only the short-form month name is displayed in the Month column (eg. Jan, Mar, Aug etc.).
- Select the cell immediately to the right of the Month column. (this should be cell K1) and enter the text Full Name. This will create a new field for the records.
- In the Full Name column, use the CONCATENATE (or CONCAT) function to display the first and then the proper last name (separated by a single space) for all the records.
- Widen the Full Name column using auto-fit.
- Save and close your file.
1 Gatchell Bikes Ltd 2 BATTERY POWERED BIKE ANALYSIS 3 4 Break-Even Analysis Revenue Expenses Units Sold Net Income 800 $3,000 24,00,000 $ 800 $1,287 10,29,600 $476 3,80,800 14,10,400 $ 5 Revenue 6 Units Produced and Sold 7 Average Price per Unit 8 Total Revenue 9 10 Variable Expenses 11 Units Produced 12 Average Material Cost per Unit 13 Total Material Cost 14 Average Manufacturing Cost per Unit 15 Total Manufacturing Cost 16 Total Variable Expenses 17 18 Fixed Expenses 19 Salaries and other benefits 20 Property tax 21 Insurance 22 Miscellaneous 23 Total Fixed Expenses 24 25 Summary 26 Total Revenue 27 Total Expenses 28 Net Income 29 30 31 32 33 $ 7,45,000 87,000 82,000 39,000 9,53,000 $ 24,00,000 23,63,400 36,600 $ J25 X fi Asha B D E F G 1 Last N First Nam City Provin Customer Phone Sale Da 2 ROWE Brice Windsor, ON GBL 139 8006139520 05-01-2017 3 KITCHENS Nguyet Alcida, NB GBL838 8007838810 08-01-2017 4 MAIR Janae Belleville, ON GBL639 8004863933 15-01-2017 5 MAKLEY Darrel Vancouver, BC GBL006-09 6132675615 19-01-2017 6 MILIONIS Genevieve Llle-Perrot, QC GBL732 8007323349 26-02-2017 7 BACHO Giovanna Swift Current, SK GBL364 8003646833 28-02-2017 8 SPANNUTH Shelton Baker Brook, NB GBL395 8006139520 08-03-2017 9 MAHLUM Alison Delhi, ON GBL101-44 8003646832 11-03-2017 10 ZUFALL Maynard Bradford, ON GBL357 8001835703 19-03-2017 11 BUSSERT Timothy Aurora, ON GBL346 7054834659 10-04-2017 12 EDDENS Viola New Waterford, NS GBL241 8005241612 23-04-2017 13 MICHON Luann North York, ON GBL939 7055493923 26-05-2017 14 GOERKE Mable Red Deer, AB GBL076 6137607603 26-05-2017 15 KAEA Sabine Edmonton, AB GBL101-11 8008848098 12-06-2017 16 MOLTZ Dolores Fredericton, NB GBL233 8007323348 13-09-2017 17 SUERO Hannah Etobicoke, ON GBL434 8003643409 23-10-2017 18 BLAN Sonia Barrie, ON GBL725 6138672579 04-03-2018 19 STAPELS Cletus Calgary, AB GBL037-15 8002946043 22-03-2018 20 ZOLDAK Pangman, SK GBL438 6139943832 30-04-2018 21 BILLOW Verlene Rouyn-Noranda, QCGBL622 8007622349 03-05-2018 22 GIGLIO Yesenia Leduc, AB GBL800 8008880069 02-06-2018 23 SINE Tayna North Vancouver, B GBL223 8007622350 18-06-2018 24 JOBST Verona London, ON GBL437 8002943793 26-06-2018 25 EGGERS Denae Hamilton, ON GBL572 6137257253 09-07-2018 26 MONTALGO Rikki Pierrefonds, QC GBL037-10 8003531422 12-07-2018 27 WEEKLY Nan Windsor, ON GBL449 7052449380 03-08-2018 28 DESPAIN Sherell Laval, QC GBL644 6139964455 08-09-2018 29 BRENNICK Coral Yellowhead County GBL745 8003974586 14-10-2018 30 HUELSE Phyliss Strathroy, ON GBL493 7052449380 31-10-2018 31 WIDA North River, NS GBL073 8008007315 03-12-2018 32 HELANDER Latonia Ottawa, ON GBL388 8007838806 24-12-2018 33 CLOZZA Aimee Becancour, QC GBL101-12 8007830349 27-12-2018 34 STAIE Marcelle Sault Ste. Marie, O GBL613 8006138289 07-05-2019 35 SCHURING Ilda Salmon Arm, BCGBL416 6135641644 14-08-2019 36 KLUKKEN Aileen Richmond, BC GBL137 8003513747 14-08-2019 37 ZENG Davida Calgary, AB GBL351 6138435164 31-08-2019 38 RUBARTS Beata Vernon, BC GBL825 8006138258 25-09-2019 39 WIEAND Angelyn Saint-Hyacinthe, QIGBL037-25 8002816538 27-09-2019 40 HUMMELL Claudio Peterborough, ON GBL361 6136136134 27-10-2019 41 TWYMAN Krystal Quesnel, BC GBL310 8004931055 29-10-2019 42 ECHEGOYE Adela Etobicoke, ON GBL382 8006138289 18-11-2019 43 PHIBBS Ryan Regina, SK GBL376 8006137684 29-11-2019 44 BUETTNER Edmond Matane, QC GBL342 8007834231 03-12-2019 45 EMMERT Catina Abbotsford, BC GBL006-12 6136698551 03-10-2020 Instructions | Documentation Income Statement Members Jesse 1 Gatchell Bikes Ltd 2 BATTERY POWERED BIKE ANALYSIS 3 4 Break-Even Analysis Revenue Expenses Units Sold Net Income 800 $3,000 24,00,000 $ 800 $1,287 10,29,600 $476 3,80,800 14,10,400 $ 5 Revenue 6 Units Produced and Sold 7 Average Price per Unit 8 Total Revenue 9 10 Variable Expenses 11 Units Produced 12 Average Material Cost per Unit 13 Total Material Cost 14 Average Manufacturing Cost per Unit 15 Total Manufacturing Cost 16 Total Variable Expenses 17 18 Fixed Expenses 19 Salaries and other benefits 20 Property tax 21 Insurance 22 Miscellaneous 23 Total Fixed Expenses 24 25 Summary 26 Total Revenue 27 Total Expenses 28 Net Income 29 30 31 32 33 $ 7,45,000 87,000 82,000 39,000 9,53,000 $ 24,00,000 23,63,400 36,600 $ J25 X fi Asha B D E F G 1 Last N First Nam City Provin Customer Phone Sale Da 2 ROWE Brice Windsor, ON GBL 139 8006139520 05-01-2017 3 KITCHENS Nguyet Alcida, NB GBL838 8007838810 08-01-2017 4 MAIR Janae Belleville, ON GBL639 8004863933 15-01-2017 5 MAKLEY Darrel Vancouver, BC GBL006-09 6132675615 19-01-2017 6 MILIONIS Genevieve Llle-Perrot, QC GBL732 8007323349 26-02-2017 7 BACHO Giovanna Swift Current, SK GBL364 8003646833 28-02-2017 8 SPANNUTH Shelton Baker Brook, NB GBL395 8006139520 08-03-2017 9 MAHLUM Alison Delhi, ON GBL101-44 8003646832 11-03-2017 10 ZUFALL Maynard Bradford, ON GBL357 8001835703 19-03-2017 11 BUSSERT Timothy Aurora, ON GBL346 7054834659 10-04-2017 12 EDDENS Viola New Waterford, NS GBL241 8005241612 23-04-2017 13 MICHON Luann North York, ON GBL939 7055493923 26-05-2017 14 GOERKE Mable Red Deer, AB GBL076 6137607603 26-05-2017 15 KAEA Sabine Edmonton, AB GBL101-11 8008848098 12-06-2017 16 MOLTZ Dolores Fredericton, NB GBL233 8007323348 13-09-2017 17 SUERO Hannah Etobicoke, ON GBL434 8003643409 23-10-2017 18 BLAN Sonia Barrie, ON GBL725 6138672579 04-03-2018 19 STAPELS Cletus Calgary, AB GBL037-15 8002946043 22-03-2018 20 ZOLDAK Pangman, SK GBL438 6139943832 30-04-2018 21 BILLOW Verlene Rouyn-Noranda, QCGBL622 8007622349 03-05-2018 22 GIGLIO Yesenia Leduc, AB GBL800 8008880069 02-06-2018 23 SINE Tayna North Vancouver, B GBL223 8007622350 18-06-2018 24 JOBST Verona London, ON GBL437 8002943793 26-06-2018 25 EGGERS Denae Hamilton, ON GBL572 6137257253 09-07-2018 26 MONTALGO Rikki Pierrefonds, QC GBL037-10 8003531422 12-07-2018 27 WEEKLY Nan Windsor, ON GBL449 7052449380 03-08-2018 28 DESPAIN Sherell Laval, QC GBL644 6139964455 08-09-2018 29 BRENNICK Coral Yellowhead County GBL745 8003974586 14-10-2018 30 HUELSE Phyliss Strathroy, ON GBL493 7052449380 31-10-2018 31 WIDA North River, NS GBL073 8008007315 03-12-2018 32 HELANDER Latonia Ottawa, ON GBL388 8007838806 24-12-2018 33 CLOZZA Aimee Becancour, QC GBL101-12 8007830349 27-12-2018 34 STAIE Marcelle Sault Ste. Marie, O GBL613 8006138289 07-05-2019 35 SCHURING Ilda Salmon Arm, BCGBL416 6135641644 14-08-2019 36 KLUKKEN Aileen Richmond, BC GBL137 8003513747 14-08-2019 37 ZENG Davida Calgary, AB GBL351 6138435164 31-08-2019 38 RUBARTS Beata Vernon, BC GBL825 8006138258 25-09-2019 39 WIEAND Angelyn Saint-Hyacinthe, QIGBL037-25 8002816538 27-09-2019 40 HUMMELL Claudio Peterborough, ON GBL361 6136136134 27-10-2019 41 TWYMAN Krystal Quesnel, BC GBL310 8004931055 29-10-2019 42 ECHEGOYE Adela Etobicoke, ON GBL382 8006138289 18-11-2019 43 PHIBBS Ryan Regina, SK GBL376 8006137684 29-11-2019 44 BUETTNER Edmond Matane, QC GBL342 8007834231 03-12-2019 45 EMMERT Catina Abbotsford, BC GBL006-12 6136698551 03-10-2020 Instructions | Documentation Income Statement Members Jesse