The Most Competitive Highly Urbanized Philippine Cities outside Metro Manila

 2020 RANKINGS of the Most Competitive Highly Urbanized Philippine Cities outside Metro Manila.

Cities and Municipalities Competitiveness Index (CMCI) is an annual ranking of Philippine cities and municipalities developed by the National Competitiveness Council through the Regional Competitiveness Committees (RCCs) with the assistance of the United States Agency for International Development.

Rankings of Cities and Municipalities are based on the sum of their scores on 4 Pillars: Economic Dynamism, Government Efficiency, Infrastructure, and Resiliency.

Lucena City was ranked 31st out of 32.

EXCEL FORMULAS FOR DATA ANALYSIS

 

TEACHING EXCEL FOR DATA ANALYSIS: HANDS ON Create a trendline

 

Create a trendline

You would like to add a moving average trendline to a chart that illustrates sales data over the period of several weeks.

1. Click on the link below to open the Microsoft Excel exercise document you can use to complete this exercise.

https://github.com/alcadelina/Excel-

2. Click to select the large chart that appears on Sheet1 of the current workbook:

3. Click Chart Design → Add Chart Element → Trendline → More Trendline Options:

4. The Format Trendline task pane now appears on the right side of the Excel window. Click the Moving Average radio button:

5. Ensure that the Period setting is set to “2” and that the Trendline Name radio button is set to Automatic:

6. Close the Format Trendline task pane by clicking the Close button (x) in its upper right-hand corner:

7. Examine the graph and you will see that the trendline that has been added better illustrates the fluctuation in this data over time:

8. Save the current workbook as Activity 4-3 Complete and then close Microsoft 365 Excel to complete this exercise.

9. Now, you can check out an example of a completed document in the link below:

https://github.com/alcadelina/Excel-

TEACHING EXCEL FOR DATA ANALYSIS: HANDS ON 35 Practice Analyze and present data with Excel

 This assignment asks you to create a polished Sales Report from raw data. While you work through the steps to create the Sales Report, you can use the linked image files as an example of what each sheet in the completed Workbook should look like.

Setup instructions:

Before you begin, you will need to download the sample file called Sample File – Original.xlsx. Open this file and use the File, Save As option to save it under the name Sample File – Completed. You will need to download and have available two other files which you will be asked to use in completing the Workbook. These are Background Image.png and New Staff.csv. You should not open these files at this time. You will need to download and have available the image files that you will be using as a visual guide for each sheet in the workbook.

You can download access these files : https://github.com/alcadelina/Excel-

How to submit:

You are required to submit your assignment using a single file in .xlsx format. It's recommended that peer review assignment file uploads be 10MB or lower. Click the My submission tab to submit your assignment.

How to create your assignment:

All Sales sheet The workbook currently has a single worksheet. Rename this sheet to All Sales. Sort the data to organize it initially by Month and then by Sales Area. Use commands in the Font, Number and Cell Styles to make all content and headings to match the sample. Widen columns were necessary. Add in the Targets and Commission columns. In Targets, enter one value of 15000. Use Fill Down to repeat for all cells under the Targets heading. Enter a value of 10% in a cell and assign the name commission to that cell. In the first cell under the heading Commission, create an IF formula that checks if the sales amount on that row is greater than or equal to the target. If it is, the IF formula should calculate the commission due using the named cell. If the target is not met, the IF formula should return the value zero.

North, South, East, and West sheets Add in four new sheets. Rename them North, South, East, and West. In the All Sales sheet, use Filters to identify entries for each region. Use copy and paste to add the data into the new sheet of the same name. In each of the area sheets, create blank rows and add the headings. Format each heading as per the sample. Add a Conditional Format rule to the Sales Amount column in each area sheet which highlights the top 5 values in color. In each sheet, add the salespeople’s names as headings at the top right. For the first salesperson in each sheet, use SUMIF to calculate their sales total. Use dollar signs in the formula so that you can copy the formula to the right.

Copy of All Sales sheet Copy the All Sales sheet. Rename it Copy of All Sales. Clear all formatting from the titles. Make the data into a table with the Total Row feature turned on. Add totals in the Sales Amount and Commission columns. Name the table Sales_Data and format it with the style feature. Add the Over/Under column. Create a formula using table references to calculate this. Change the formatting so that the results are displayed as currency, with negative values in red.

Chart sheet Add a sheet called Chart. Create a summary block of data which contains the area totals for sales and commission. Use the Total Row of the Table to generate the totals but ensure that the values in the Chart sheet are constant and will not change. Create a Column Chart from the block of data. Once the chart has been created. Change the Title text and the appearance of the Chart as per the sample. Change only the Commission data series to a green color. Add a border and beveled effect around the edges. Add an Alt+Text description to the chart.

Sales Analysis sheet Position the cursor in the Sales_Data Table and insert a Pivot Table in a new worksheet. Rename that worksheet Sales Analysis. Design a PivotTable that show the sales totals broken down by month and formatted as currency. Have a second set of totals visible as a percentage of the grand total. Set up the PivotTable so that the whole table can be filtered by payment type. (Tip: if you add the heading month to the appropriate box in the PivotTable pane, it will probably be visible as two entries. You can remove one to leave just a list of unique month names.) Change the style of the PivotTable and reword the headings to match the sample. Add the Sales Area and Employee slicers to the sheet. Format them so that they stand out.

New Staff sheet Use options in the Data Ribbon to import data from the New Staff CSV file. Load the data. Break the link to the original file. Delete any connections and close any query windows. Add in blank columns and by using text function formulas or Flash Fill (as appropriate) fill them with the staff names transformed to match the sample. In one column use a text function to extract the area initials from the column called Payroll ID. Hide columns except for First Name, Last Name and Area Code.

Cover Page sheet Create a new worksheet called Cover sheet. Organize the sheets in the workbook so that they match the order in the sample. (Left to right they should be: Cover Sheet, All Sales, North, South, East, West, Copy of All Sales, Chart, Sales Analysis, and New Staff.) On the Cover Page sheet, add the heading and the sheet name headings. Add a background using the Background.png file. Add hyperlinks to the sheet headings to facilitate clicking on the heading to navigate to that sheet. Add Alt+Text to the hyperlinks to show the sheet name and not the full path. Add a color to the Cover Page, All Sales, and Sales Analysis tabs to make them stand out. Save and close the file.

Examples of Good Feedback

As a reviewer, you will be required to provide feedback on an assignment submitted by two of your peers. The focus of your feedback should be on the appearance of the worksheets and the consistent use of Excel features.

While you work through the steps to create the Sales Report, you can use the linked image files as an example of what each sheet in the completed Workbook should look like.

To measure if the learner has created a professional Sales Report, consider the following points in relation to each sheet: Sheet: Cover Sheet.

LO: Use formatting to set the scene in a report and provide a quick way to navigate the workbook. Q: Are you immediately aware of the purpose and the status of the information in this report?

• Is there a background image?

• Do the headings stand out in the same way as the sample?

• Are the headings hyperlinks which you can use to jump directly to a specific sheet? (They should appear in a blue color with an underline.)

• Are the sheets organized in the same order as the sample? Left to right the order should be: Cover Sheet, All Sales, North, South, East, West, Copy of All Sales, Chart, Sales Analysis, and New Staff.

• Do the Cover Sheet, All Sales, and Sales Analysis sheet names have a different background color?

Sheet: All Sales LO: To use formatting, sorting, and naming tools to provide useful insights on a block of data. Q: Is the block of data organized and laid out in a way that helps the viewer to understand the data and the results presented?

• Are the headings formatted to stand out?

• Are there columns called Targets and Commission? Do these columns contain similar numbers to the completed samples?

• Is there a 10% value-added to the sheet?

Sheets: North, South, East, and West LO: Use the filter and copy and paste tools to extract focused subsets of data from a larger block. Q: Does each sheet focus on one area and provide targeted insight on the performance of the team in that region?

• Does each of these sheets contain only the data that is relevant for the area named?

• Does each sheet have an overall heading that is formatted to stand out and centered over the data?

• Are the salespeople’s names visible on the top right of the sheet with totals underneath? Do these totals match the samples?

• Do all figures have the appropriate currency symbol next to them?

• Are five of the figures under the heading Sales Amount standing out in a different color?

Sheet: Copy All Sales

LO: Use and understand storing data in a dynamic table and use table features to generate results. Q: Does the assignment display the sales data in an organized way and contain results that give a clear picture of performance?

• Is the data formatted as a Table? (The headings should have a different background color. There should be drop-down arrows beside the heading and the rows should have alternating background colors.)

• Is there a column on the right of the data called Over/Under? Do the figures under this heading match the sample? Are negative figures appearing in a different color?

• Is there a row at the bottom of the data called Total? (This row will stand out in some way. For example, in the sample it there a double line above and a thick black line below.)

• Are there number totals in the Sales Amount and Commissioncolumns?

Sheet: Chart LO: Use the Chart feature to create an effective visual representation of the Data. Q: Does the assignment provide a clear and easy-to-understand picture of the data in the Chart?

• Is there a block of results in the sheet showing the Sales and Commission totals for each region? Do the figures in this block of data match the sample?

• Is there a chart on this sheet with vertical columns?

• Is the one called Commission colored green?

• Does the Chart have the title Sales 2021?

• Does the Chart have a border that stands out?

Sheet: Sales Analysis LO: Use pivot tables to summarize and analyze data Q: Does the assignment provide a breakdown and analysis of the sales data by month?

• Is there a table of results on this sheet with Sales Totals shown by month?

• Is there a second column of results shown as percentages?

• Do these columns have the headings Month, Sales Total and % of Grand Total?

• Above the table is there a heading Payment Type with a drop-down arrow? (This drop-down arrow will be to the right of an entry in brackets. That entry could read All, Cash, Credit Card or On Account.)

• Are there two graphics on the sheet with colored buttons? One should be called Sales Area and have entries North, South, East, and West. The other should contain peoples’ names. They should have different color schemes.

Sheet: New Staff LO: Use the import tools to bring information from another file and then shape and refine it. Q: Has the assignment transformed the unrefined data into a form that is clear and easy to read and work with?

• Are there only three columns with data called First Name, Last Name and Area? (These should be the only visible content. There should be no side windows open in Excel to the right of the screen.)

• Do the names all begin with a capital letter?

• Under the heading Area, are there entries comprised of just two initials?

• Are the First Names in column C, the Last Names in Column D, and the Area entries in column E? Is the next column letter H?

TEACHING EXCEL FOR DATA ANALYSIS: HANDS ON 34 Using data sources

 

Using data sources

You have been asked to create a price list, in Canadian dollars, for some of your company’s products. You will need to update it regularly using the current exchange rate. You first need to import your price list, then create a web query to load and maintain the current exchange rate.

Note: The will need the following activities to complete this exercise:

• https://github.com/alcadelina/Excel-
  
  

1. Click on the link below to open the Microsoft Excel exercise document you can use to complete this exercise.

https://github.com/alcadelina/Excel-
2. Click Data → From Text/CSV:

3. The Import Data dialog box will open. Navigate to your activity files and select Exercise: Using Data Sources_text, then click the Import button:

4. The Get & Transform Data dialog box will open, displaying the data in the source file. If the selection in the Delimiter drop-down list reads --Custom--, and there is an extra header, click to select the Tab option in the Delimiter drop-down list:

5. With your data properly aligned in the list box, click the Load button:

6. A new worksheet is created and the data from your text file is loaded as a table. The Queries & Connections task pane opens on the right of the screen:

7. Click on the Sheet1 tab to make it the active worksheet. Click to select cell A1, then press Ctrl + C to copy the URL of the exchange rate site:

8. Now click Data → From Web:

9. The From Web dialog box will open. Click to select the URL field, then press Ctrl + V, to paste the URL. Click OK:

10. The Navigator dialog box will open. Click to select Table 0 in the list box on the left, confirm the data is present in the Table View window on the right, then click the Load button:

11. A new worksheet is created and the data from the website is loaded as a table. The query appears under the Exercise: Using Data Sources

12. Hover your cursor over the web query in the Queries & Connections task pane, then click Edit in the table dialog box that appears:

13. The Power Query Editor will launch. Click the filter button to the right of the header of the Country-Currency column, then click to select the Text Filters field and begin typing “Canada”. When Canada-Dollar is the only selected item, click OK:

14. Click the Close and Load command in the Close group of the Home tab:

15. You will see that the Web Query table now only displays the Canada-Dollar row of data from the data set:

16. Click the Sheet2 tab to make it the active sheet, then click to select cell C2. Type the formula “=[@[US Dollars]]*VLOOKUP("Canada-Dollar",Sheet3!A:B,2,FALSE)”, then press the Enter key:

17. The table containing the price list will auto complete the formula for every row in the column:

18. To refresh the data from the website, right-click on the query in the Queries and Connections task pane, then select Refresh:

19. You can now save your changes as Activity 6-2 Complete and close Microsoft Excel 365 to complete the activity.

20. Now, you can check out an example of a completed document in the link below:

https://github.com/alcadelina/Excel-