Strategic Initiatives Spreadsheet Instructions

Hi, my name is Cesar Rogers with DeepTree Incorporated. We are assisting the Matanuska-Susitna Borough with developing a strategic investment portfolio. As part of that effort, we have developed an Excel spreadsheet for analyzing the various capital investment efforts that lay before the borough.

To access the Excel sheet, you will need Microsoft Excel 2007 or higher.

And you can have the Excel sheet either on a thumb drive or on the web.

If it's on the thumb drive, you double-click on the Excel sheet, and you will notice that there's 4 worksheets. There's the Investments, the Tabulated, the Pie Chart, and the Bar Graph. We're going to go through each of the worksheets to help you better understand them. Now the first worksheet is Investments, and in column A lists all the projects and tasks that have been compiled by the borough. If you, if you go over the project itself, you'll see there's a summary of each of the projects and tasks and what they're trying to accomplish.

Column B, this lists the investment categories that have been associated with this investment project or task. So each investment or project tasks can fall under either Economy, Quality of Life, Health and Safety, Education, Borough Communications, Transportation, Borough Operations, and the Port MacKenzie Infrastructure. That way we can group all the investments and projects into certain categories. Now some projects and tasks, they span multiple categories, and so this column C allows you to associate this specific project here, the Deep Draft biosafety protection under the Port MacKenzie infrastructure category as well as the economy category.

Column D helps describe what type of project it is. So there's 3 types of projects we have. We have an Appraise/Define, and that project really just decides, identifies, and assesses the business need. It doesn't complete the project, it just does an appraise of what is going to be accomplished. And the other project type is full project cost.

That's how much it costs from beginning to end to run this project or task. And the last one is Envision. These are for large projects which tend to be unfunded and it's more of an exploratory phase as to this is what we think we'll be needing to accomplish this task. In column E, this column identifies whether this investment is a project or a task. A task is something that happens within normal operations of the assembly— sorry, within the borough staff and requires little guidance from the assembly.

And as such, as an example, Title 23 is something that requires to be accomplished by the borough and not needed assistance from or guidance from the assembly. Column F is the column that gives the cost of the project. And the second class required— this column, this identifies if it's required as per an ordinance or responsibility of the borough. So if it's marked as yes, then this bridge repair and replacement is required for, as per being a second class borough. Now the Matsubara in 2010 developed an economic development plan.

And in that plan they had different goals that would be accomplished. Here are the goals here. It's a list of about 20 goals. Now each of these investments has been a crosswalk between— from the investment to a specific goal. So in an Enterprise Approach to System Data, that project has an associated goal within the strategic plan of 3E.

Now if there's a blank, there's been interpretation that there is no— that project has no reference or goal in the Matsue 2010 Economic Plan. Now if the project is going to be revenue enhancing, well this column indicates yes and how much so, if known. And if the project improves the efficiency of the borough, if it reduces the expenditure how much so, and this is what this column captures. And this column here, column K, indicates how much impact the project has on citizens. And this column L helps you once you've filtered down the list, you can prioritize the projects in, in order.

Now, time frame This column N, this helps to show the, um, how long the project is going to take. So if it's either a short-term project, we've determined as in ending by 2016, or if it's medium, to be completed by 2018, or if long-term, yet to be determined. And the last column, column N, is for funding source, whether that it's internally funded by man-hours within the borough, or it's being funded by a state or federal grant, or if it's been funded by a future mill rate or bond.

So now we see that there's 61 projects and tasks.

That's 61, the count here.

And we want to be able to slice and dice the data so that we can look at the data in a different view to help us prioritize and work out what should be funded and what should not be. Now the first column I'm going to filter on is whether it is a project. Now I don't want to see tasks that are going to be accomplished by the borough. I only want to see projects. So I unclick "No" and that reduces the list down.

And I only want to see projects that are required as for a second-class borough. So I'm going to unclick "No." And lastly, I want to see only projects that have an associated reference, a goal that's been referenced in the Matsu Economic Plan of 2010. So I'm going to uncheck "Blank." So now we're down to 12 projects that have met that criteria and filter. And you can adjust these filters in any way. So there's two things to remember here.

One is that we can add columns, we can add more columns to help filter the data, and we can remove columns, and we can keep parsing the data. So you notice some of these columns are lacking in data. For example, we haven't got a dollar value for this particular project, and so we'll be constantly just cleaning and refreshing the data so that we get more and more accurate data to allow you to make better, more accurate decision-making. Now the second worksheet is the tabulated worksheet. Now this shows the same information of the investment but in a vertical format.

Microsoft Excel has pivoted the data so that all 61 projects are now in a vertical. All it does is allow you to see this data in a different viewpoint.

Now we're going to use the same criteria, we're going to filter, we're going to say we only want to see projects that are— I want to see projects, so unclick No. I'm only going to want to see projects that are required by the Borough, and I'm going to only want to see projects that are required by the Strategic Plan. And so we're down to the same 12 projects here, and just like the previous worksheet.

Again, you can adjust these filters accordingly. Now this next worksheet is the pie chart. Same data, just viewed in a different format. It views it by investment categories and you'll see currently if you list all the 61 projects and tasks, this is where the majority of the project dollar values come from, are being allocated to. And we're going to do is we're going to just list only projects.

And if you do the same, go right down, you'll be— and you go down to Project Type and do the same filtering, you'll be down to the 12 projects and what, how the categorization will be, is divided amongst different categories, investment categories. Now the last worksheet is the Bar Graph, and this does the same 61 projects in grouping by investment category and the cost. And so if you can hover over that specific project, you can see that this project, the value of the project, what the category is, and the type of the— and the name of the project it is. And again, we can reduce the 61 by projects and tasks, and we can reduce it down taking only projects and etc., working our way through. Just filtering the data in different ways.

We hope that this brief video has given you the introduction necessary for you to navigate and interpret the data in this spreadsheet in a satisfactory way. And, um, thank you for your time.