Financial Analysis Worksheets, Slides of Financial Statement Analysis

Download Financial Analysis Worksheets and more Slides Financial Statement Analysis in PDF only on Docsity!

State of California

Department of Technology

Financial Analysis Worksheets

Preparation Instructions

Statewide Information Management Manual – Section 19F

October 2017

INTRODUCTION TO

THE FINANCIAL ANALYSIS WORKSHEETS

Overview

SIMM Section 19F, Financial Analysis Worksheets (FAWs), provides a basis for detailed financial analysis that contributes to good investment decisions, project transparency, and accountability. The purpose of the FAWs is to document and compare the costs and financial benefits of the current method of operation with those of the alternatives considered. The FAWs are used to identify the specific resources that will be needed to implement the proposed alternative and attempts to answer the questions: “How much will the project cost?,” “What will be the impact on future operations?” and “From where will the funding come?” Estimating alternative solution costs is a key component of the financial analysis used to make an information technology (IT) investment decision. Financial proposals and decisions are strengthened when there is a clear understanding of the complete resource implications. The FAWs instructions have been prepared to help State of California Agencies/state entities^1 meet the California Department of Technology (CDT) requirements for documentation of project proposals. Contact your CDT, Office of Statewide Project Delivery (OSPD), Project Approval Manager for additional direction and clarification.

Clarifications  

FAWs will be created in conjunction with the Stage 2 Alternatives Analysis. Agencies/state entities are required to submit the FAWs along with a Stage 2 Alternatives Analysis for all proposals deemed reportable by the CDT.  For the proposed alternative, each of the following worksheets must be completed: Alt 1- Project, Alt 1- Future Ops, Alt 1- Funding Plan, Alt 1- Planning BCP, and Alt 1- Project BCP.  For all other alternatives considered, only the following worksheets must be completed: Alt 2- Project and Alt 2- Future Ops (and) Alt 3- Project and Alt 3- Future Ops. 

California Department of Technology SIMM Section 19F Financial Analysis Worksheets

2

Agencies/state entities are required to update the FAWs at Stage 3 Solution Development and Stage 4 Project Readiness and Approval, as further analysis of a proposal may necessitate changes to the financial analysis.

Stage 2 Alternatives Analysis Reporting Requirements

The CDT requires specific information from Agencies/state entities to carry out its responsibilities in approving the FAWs as part of the Stage 2 Alternatives Analyses. The FAWs must be comprehensive and cannot rely on verbal or subsequent written responses (e.g., emails) to the CDT staff questions. Submissions that are incomplete and fail to provide relevant information in written form may be returned without consideration at the discretion of the CDT.

(^1) State entity: Includes every state office, officer, department, division, bureau, board, and commission, including Constitutional Officers. “State entity” does not include the University of California, California State University, the State Compensation Insurance Fund, the Legislature, or the Legislative Data Center in the Legislative Counsel Bureau.

California Department of Technology SIMM Section 19F Financial Analysis Worksheets

4

Table of Contents

Overview.................................................................................................................................... .............................................................. .......................................................................................................... ................................................................................................... ................................................................................................ ......................................................................................... ...................................................................................

Stage 2 Alternatives Analysis Reporting Requirements 2 Transmittal Requirements 5 Financial Analysis Worksheets 5 Steps to Complete the FAWs: 5 FAWs Template General Instructions 7 1.0 Executive Cost Summary Worksheet 7 2.0 Summary Worksheet............................................................................................................ ............................................................ ..................................................................................... ............................................................................................................. ................................................................................................................

3.0 Current Operations Costs Worksheet (Current Ops) 9 3.1 Personal Services (Existing Staff) 9 3.1.1 Existing Staff 10 3.1.2 Program/IT 10 3.1.3 Monthly Salary .......................................................................................................... ..................................................................................................... .............................................................................. ................................................................................. ........................................................................... .................................................................. .............................................................................................................. ..............................................................................................................

3.1.4 Tenure/Time Base 10 3.1.5 Current Annual Operations Costs 10 3.1.6 Total Current Operations Costs 11 3.1.7 Average Current Operations Costs 11 3.2 Operating Expenses and Equipment (OE&E) 11 3.3 Local Assistance 11 3.4 Hidden Columns 11 4.0 Project Costs Worksheets (Alt #- Project) ........................................................................... ............................................................................................................ .................................................................. .............................................................................................................. ............................................................................ .......................................................................... ............................................................. ............................................................................................................

4.1 Personal Services 12 4.2 Operating Expenses and Equipment (OE&E) 12 4.3 Local Assistance 12 4.4 Annual Savings/Revenue Adjustments 12 4.5 Column Headings/Vertical Organization 12 5.0 Future Operations Worksheets (Alt #- Future Ops) 13 5.1 Personal Services 13 5.2 Operating Expenses and Equipment (OE&E) .................................................................. .................................................................................... .......................................................................... .................................................................... .....................................................................................

5.3 Average Future Operations Costs 13 5.4 Column Headings/Vertical Organization 13 6.0 Funding Plan Worksheet (Alt 1- Funding Plan) 14 7.0 Planning, Project BCP Worksheets 16 8.0 Detail Instructions – Executive Cost Summary, Summary, Current Ops, Alt 1- Project, Alt 1- Future Ops, and Alt 1- Funding Plan Tabs ................................................................................ 17 Executive Cost Summary Tab ............................................................................................... ........................................................................................................................ ......................................................................................................... ..............................................................

Summary Tab 19 Current Operations Tab 21 Alt 1- Project (Alt 2- Project, and Alt 3- Project) Tab 34 Alt 1- Funding Plan Tab......................................................................................................... ............................................................................................................... .................................................................................................................

Alt 1- Planning BCP 68 Alt 1- Project BCP 72

California Department of Technology SIMM Section 19F Financial Analysis Worksheets

5

FINANCIAL ANALYSIS WORKSHEETS PREPARATION INSTRUCTIONS

Transmittal Requirements

The FAWs are to be submitted with the Stage 2 Alternatives Analysis as an attachment to Section 2.14. If projected costs and/or financial benefits change throughout the Project Approval Lifecycle (Stage 3 Solution Development and/or Stage 4 Project Readiness and Approval), updates must be submitted along with the associated deliverable. Contact your CDT Office of Statewide Project Delivery (OSPD) Project Approvals and Oversight Project Approval Manager for additional guidance.

Financial Analysis Worksheets

Steps to Complete the FAWs:

The steps below are designed to obtain the cost-benefits, risk assessments, and cost-recovery strategies required to support business owner actions and stakeholder decisions, and to assist in project proposal assessments. Agencies/state entities may complete the FAWs in the sequential order below or follow another order as preferred. For example, the project costs (one-time) can be added to the FAWs before adding the current operating costs. However, it is important to enter cost information into the applicable Current Ops and Alt #- Project, and Alt #- Future Ops, for each respective alternative.

Step 1 : In the Executive Cost Summary worksheet, enter the Project Name, Department Name, Project Number, and Date. This information automatically populates in each of the other worksheets. The project costs (one-time and future operations IT staff an OE&E) in the Executive Cost Summary will automatically populate once the Alt 1- Project and Alt 1- Future Ops worksheets are completed.

Step 2 : Identify the current operations costs using the Current Ops worksheet: Determine the existing costs for program operations and supporting information systems as they would be if the proposed project is not undertaken. This information includes Personal Services (PS) (Existing Staff), Operating Expenses and Equipment (OE&E) and Local Assistance, which together add up to ”Total Expenditures – Current Operations Costs.”

In each appropriate column, by fiscal year (FY), until the maintenance and operations (M&O) cycle of expenditures repeats, select the FY (in 0000- format) with the:

a. Classification, program or IT (i.e., Pgm or IT), monthly salary, tenure/Time Base (i.e., P, T, E, Board), and number of positions. Note: It is important to select either “Pgm or IT” AND “P, T, E, or Board” as certain calculations within the worksheets depend on these entries.

b. Operating expenses and equipment (i.e., a project may have a cycle of expenditures over more than one FY before repeating).

c. Enter the number of FYs with entries (e.g., “5”), for calculation purposes.

Note : The Current Ops worksheet automatically calculates the average operations costs (i.e., Average Current Operations Costs) for use in subsequent calculations.

California Department of Technology SIMM Section 19F Financial Analysis Worksheets

7

FAWs Template General Instructions

The FAWs provide a fiscal comparison of the current method of addressing a business problem to each alternative approach. A key concept of this review is to develop apples-to-apples comparisons of various component cost and revenue impacts. This is done through the review of the average annual cost of multi-year cycles, which allows for cycles of varying timeframes to be compared. For example, if the current system has a three-FY M&O cycle (i.e., the cost of operations is not the same from FY to FY but repeats every three FYs) but the alternative’s future operations has a five-FY cycle, comparing the average annual operations cycle cost allows for a fair comparison of the current to the alternative approach.

The FAWs are made up of several Microsoft (MS) Excel spreadsheets that populate, by formula, the Executive Cost Summary, Summary , Alt 1- Funding Plan, Alt 1- Planning BCP, and Alt 1- Project BCP spreadsheets. The other specific spreadsheets address the costs of:

 Current operations cost for the existing system (the costs that would continue without initiating the proposed project).  One-time project costs for each alternative considered (one sheet is required for each alternative identified, not just the selected alternative).  Future operations costs for each alternative considered (one sheet is required for each alternative identified, not just the selected alternative).  Funding Plan for the selected alternative only.  Alt 1- Planning BCP for the selected alternative only.  Alt 1- Project BCP for the selection alternative only.

Each of these pages include column headings reflecting the FY in which the costs will occur. The Summary, Alt 1- Funding Plan, Alt 1- Planning BCP, Alt 1- Project BCP automatically identify the FY based on the corresponding Alt 1-, Alt 2-, Alt 3- Project or Future Ops worksheets. Only enter the actual FY in 0000-00 format (e.g., 2020/2021) in fields that are displayed as “White.” The FY columns are intended to sync up with BCP fiscal sheets, both by FY and by category of expenditure.

Note : The FAWs must be completed per these instructions regardless of the system development lifecycle that is anticipated to be used to deliver the solution (waterfall, agile/iterative, etc.). Show entire project costs on these spreadsheets, hiding/unhiding FYs as needed, and adjusting ”average” formulas via the “Number of FYs with entries” cell.

1.0 Executive Cost Summary Worksheet

The Executive Cost Summary summarizes the total project (planning, project, and future) costs and also provides the overall planning and project costs plus the continuing future IT staff and OE&E costs.

Once Stage 4 Project Readiness and Approval is approved, the final FAWs Executive Cost Summary will identify the final approved budget amount for the project. This information may be transferred to your other project related documentation (e.g., Project Status Report) or other cost tracking sheets. When transferring cost amounts, either copy and paste special using “Values (V)” or retype the amounts into the destination worksheet/cost tracking document.

Enter the project name, department name, project number, and date. All other fields are automatically populated using data from your Alt 1- Project and Alt 1- Future Ops worksheets.

California Department of Technology SIMM Section 19F Financial Analysis Worksheets

8

2.0 Summary Worksheet

The Summary worksheet displays the summarized costs and financial benefits of each alternative. This worksheet has been designed to chronologically display key financial aspects for each alternative, identifying the average current operations costs (before project), project costs (during project), and average future operations costs (after project). All of the cells in the Summary worksheet are generated from data contained in the “ Current Ops ” worksheet, applicable “ Alt #- Project ” worksheets, and the “ Alt #- Future Ops ” for each alternative. Do not input data directly into the Summary worksheet.

The Summary worksheet compares each alternative solution and identifies the proposed alternative (i.e., Alt 1- Project). The Summary worksheet contains summary level information, which provides the basis to perform an evaluation of each alternative both in terms of costs to implement the alternative and the positive or negative impact on operations resulting from the implementation of the system. The “Net Cost (+) or Benefit (-)” row for each alternative in the Summary worksheet provides information on the relative benefit of implementing each alternative, based on the total project costs.

The Summary worksheet is organized into the following vertical sections (columns):

 Average Current Operations Costs (Before Project) : This column includes the annual cost to operate the program and information systems as they exist today before the new solution has been implemented. The costs shown here are derived from the information inputted into the Current Annual Operations worksheet (Current Ops), which represent annual costs of multi-year operations cycles. As represented in the Summary worksheet, those costs represent the average annual operations costs.  Planning Costs (Stage 2 – Stage 4) : These columns show the estimated year-by- year cost to plan the project; this reflects total one-time costs. The first five FYs are labeled as: “Planning Costs,” “FY 1,” “FY 2,” etc. See 3.0 Project Costs Worksheets (Alt #- Project) for additional information regarding planning costs.  Project Costs (During Project) : These columns show the estimated year-by-year cost to implement the project; this reflects total one-time costs. The FYs (in the 0000-0000 format) align with the BCP fiscal detail. This section includes each alternative’s project costs based on the project activities broken down by FY.  Total One-Time (Project) Costs: These columns combine the total project costs for all FYs, broken down by positions and dollars, OE&E expenditures, local assistance.  Average Future Operations Costs (After Project) : These columns show the projected cost to operate the program and information systems after the alternative has been implemented and changes have been made to program and IT operations.  Change in Operations Costs: These columns show the differences between what it costs to operate the current program and supporting systems (today) and what it is estimated it will cost to operate the program and supporting systems in the future after the project has been completed and the new system has been implemented.  Total Project Costs (Planning + One-Time + Total Future Annual Costs): These columns summarize the costs for planning, one-time project, and total future operations for IT staff with OE&E costs.

Using the following fields, identify the existing staff that currently support the business process and IT systems impacted by this proposal.

3.1.1 Existing Staff Identify the classifications of the existing staff that currently support the business process and IT systems impacted by this proposal.

3.1.2 Program/IT Differentiate the program staff that are currently working in the business area supported by the system versus the IT staff currently assigned to support the existing information systems and infrastructure (if one exists).

3.1.3 Monthly Salary Identify the total monthly salary of each classification. The salary for existing staff is the current salary. Do not include any benefits costs.

3.1.4 Tenure/Time Base

California Department of Technology SIMM Section 19F Financial Analysis Worksheets

10

For each position, enter whether the position is Permanent (P), Temporary (T), Exempt (E), or a Board position. As needed, work with your Budget Office to determine the appropriate tenure/Time Base for your positions. For each appropriate FY column, include the time base (to the tenth of a full position) for all applicable staff resources. The definitions for P, T, E, and Board positions are as follows:

 Permanent: Positions authorized/reflected in the annual Supplemental Schedule of Salaries and Wages (Schedule 7A), authorized in the California Budget.  Temporary: Positions not authorized/reflected in the annual Supplemental Schedule of Salaries and Wages (Schedule 7A). Typically, temporary positions are paid out of “blanket” funds unassociated with a specific authorized position. Also refer to Government Code (GC), Title 2, Division 3, Part 3, Chapter 3, Section 13309 for additional information.  Exempt: Appointees of the Governor or elected officials, including members of the Legislature. The Government Code, Title 2, Division 3, Part 1, Chapter 6 sets the positions and salaries for most top-level State officers appointed by the Governor (e.g., Director of Finance, Secretary of Transportation, Secretary of the Natural Resources Agency). For specific positions and salaries of exempt positions, refer to the California Department of Human Resources (CalHR) Exempt Employee Salaries.  Board: Typically, appointees of the Governor or other appointing authorities. Members of boards and commissions are exempt from civil service. For specific positions and salaries of board positions, refer to the CalHR Exempt Employee Salaries.

3.1.5 Current Annual Operations Costs For each FY column, identify the number of positions. The associated costs (Dollars) for current operations will automatically be based on the monthly salary and position information entered. Positions (and costs) are to be shown for an entire multi-year operations cycle. These reflect the cost of current operations if the project is not undertaken and operations continue in their current state.  Positions : Enter the number of existing staff for each classification. Identify partial positions to one/tenth (0.1) of a position.

 Dollars : The staff cost will be calculated automatically based on the monthly salary/benefits and number of positions. This figure reflects the annual salary-only cost for the positions.

3.1.6 Total Current Operations Costs The costs will be calculated automatically based on the total sum of all FYs for the multi-year operations cycle.

3.1.7 Average Current Operations Costs The costs will be calculated automatically based on the total average of all FYs. The figures in this column will be automatically rolled up into the Summary worksheet. Remember to change the “Number of FYs with entries” based on the multi- year operations cycle entered.

3.2 Operating Expenses and Equipment (OE&E)

The costs for OE&E are broken out by the items of expense used in the BCP fiscal sheets. Departments shall not revise these items of expense or the totals derived from these items. The sample form includes examples of various sub items. For example, in the Consulting and Professional Services: Interdepartmental, in the parenthesis “5340330-Consulting and Professional Services – Interdepartmental” is noted. If this item applies, you may enter “5340330” and then the department providing professional services. Be sure to follow the format recommended by your department’s budget/accounting section. Comply with the Department of Finance (DOF) and Financial Information System for California (FI$Cal) definitions regarding the correct item/sub item of expense to post OE&E expenses.

3.3 Local Assistance

Identify any Local Assistance expenditures. These are state funds provided to counties, cities and special districts. As the use of Local Assistance as an expenditure is rare, the Local Assistance fields are hidden and may be unhidden if needed.

California Department of Technology SIMM Section 19F Financial Analysis Worksheets

11

3.4 Hidden Columns

The Current Ops and all “Alt #- Project,” and “Alt #- Future Ops” worksheets are formatted similarly. All of these spreadsheets include columns that have been hidden from view to simplify the look of the spreadsheets since they include only formulas that are not intended to be changed, as they perform calculations that transfer position-type information to the BCP fiscal data sheet. Use the “Hide” or “Unhide” feature in MS Excel to add or hide columns and rows, as appropriate.

4.0 Project Costs Worksheets (Alt #- Project)

NOTE: The Current Ops worksheet, as well as the Alt #- Project and Alt #- Future Ops worksheets for all alternatives are formatted similarly. Please refer to Section 3.0 Current Operations Costs Worksheet ( Current Ops ), above, for specific instructions not repeated below. Since the various Alt #- Project and Alt #- Future Ops worksheets for all alternatives are formatted similarly, be sure to label them per the alternative for which the information is being displayed (e.g., Alt 1 vs. Alt 2).

California Department of Technology SIMM Section 19F Financial Analysis Worksheets

13

5.0 Future Operations Worksheets (Alt #- Future Ops)

NOTE: The Current Ops worksheet, as well as the Alt #- Project and Alt #- Future Ops worksheets for all alternatives are formatted similarly. Please refer to Section 3.0 Current Operations Costs Worksheet ( Current Ops ), above, for specific instructions not repeated below. Since the various Alt #- Project and Alt #- Future Ops worksheets for all alternatives are formatted similarly, be sure to label them per the alternative for which the information is being displayed (e.g., Alt 1 vs. Alt 2).

The third step in completing the FAWs is to identify and document the future annual cost to operate the alternative (after project). It is important to estimate and document the future costs for program operations and supporting information systems for each alternative solution. When compared to the baseline operations costs, this presents a view of the total cost of ownership for the proposed alternative. In the Summary worksheet, these future operations costs are compared to the current annual operations cost and presented in the Change in Operating Costs columns. Note: Do not adjust costs for inflation.

5.1 Personal Services

Refer to Section 3.0 Current Operations Costs Worksheet ( Current Ops ).

Identify the classifications of the staff who support the business process and IT systems impacted by the information system. Differentiate the program staff that are currently working in the business area supported by the system versus the IT AND program staff currently assigned to support the information systems and infrastructure.

For example, if an Associate Governmental Program Analyst will provide ongoing support for the system (not as a user only), then this staff person should be designated as “IT” for the portion of duties when they will provide information system and infrastructure support.

On a case by case basis, you may only need to show those staff who will provide support to the information system (identified as “IT”). Discuss your particular situation with your CDT OSPD Project Approvals and Oversight Manager for further clarification.

5.2 Operating Expenses and Equipment (OE&E)

The OE&E information for all Alt #- Project and Alt #- Future Ops worksheets are formatted similarly to the Current Ops worksheet. Refer to Section 3.0 Current Operations Costs Worksheet ( Current Ops ).

5.3 Average Future Operations Costs

The costs will be calculated automatically based on the total average of all FYs. The figures in this column will be automatically rolled up into the Summary worksheet. Remember to change the number of FYs of costs if the operations cycle being used is different from that shown in the sample form.

5.4 Column Headings/Vertical Organization

Column headings for the Alt -# Future Ops worksheets are similar to the Current Ops worksheet; refer to Section 3.0 Current Operations Costs Worksheet (Current Ops) with the following exceptions:

California Department of Technology SIMM Section 19F Financial Analysis Worksheets

14

 IT Only Total (Future FYs): The total IT personal services costs automatically calculates each row in Personal Services (Existing/Redirected) and (New) based on the positions identified as “IT.” Any entries in the Overtime, Holiday, Other row will be included added to the IT Personal Services total. Additionally, the percentage entry in the Dept. Benefits Rate (% of Salaries/Wages) (first FY column) will be used to calculate the IT personal services benefits total.

The IT Personal Services (Existing/Directed)/(New) totals plus the OE&E costs for all FYs will be automatically summed and used to populate the Summary worksheet’s Total Project Costs “Total Fut. Ops. IT Staff & OE&E Costs” cell.

 (Optional) Enter Total IT OE&E Costs Only: If the OE&E costs shown indicate program- related costs, you may manually enter only those costs related to IT in the columns identified as “(Optional) Enter Total IT OE&E Costs Only.” For example, if you have $50,000 Travel – In State costs and only $5,000 is related to IT, then use the corresponding row and “(Optional) Enter Total IT OE&E Costs Only” column to enter $5,000. Once an entry is made to this column, you must manually enter all other costs related to your IT OE&E needs, for each respective row. You will also need to enter the appropriate costs in the “(Optional) Enter IT OE&E for the FINAL M&O FY ONLY” column. Additionally, if no OE&E costs shown relate to the IT OE&E needs, you may enter $ .01 in one row under the “(Optional) Enter Total OE&E Costs Only” and “Optional) Enter IT OE&E for the FINAL M&O FY ONLY.” By doing this, you will not have IT OE&E costs for “Future Ops. IT Staff & OE&E Costs (Continuing)” or “Annual Future Operations IT Costs (M&O)” on the Summary and Executive Cost Summary worksheets.

Be sure to align the M&O IT OE&E with the specific year identified.

 Enter Final M&O FY: Using the dropdown, enter the FY that provides the final M&O FY where the expenditures repeat in future FYs. The total IT Personal Services (Existing/Redirected)/(New) plus “Overtime, Holiday, Other and Dept. Benefits Rate (% of Salaries/Wages)” plus OE&E costs for the FY entered will calculate automatically for this column. The Summary worksheet’s Total Project Costs “Annual Fut. Ops. Costs (M&O)” will populate this sum for the annual future operations costs. This calculation is similar to the IT Costs in the previous Economic Analysis Worksheets to identify “above the line” continuing IT costs.

 (Optional) Enter IT OE&E for Final M&O FY Only: If the OE&E costs shown indicate program-related costs, you may manually enter only those costs related to IT in this column for each respective OE&E category. Once any is made to this column, you must manually enter on other OE&E costs related to the final M&O FY. Refer to additional information in “(Optional) Enter Total IT OE&E Costs Only” above.

6.0 Funding Plan Worksheet (Alt 1- Funding Plan)

The Alt 1- Funding Plan worksheet is used to show where the funding will come from for the proposed alternative as well as savings and/or revenues that may be achieved. The funding plan shall only be developed for the proposed alternative.

California Department of Technology SIMM Section 19F Financial Analysis Worksheets

16

 Funding Source Total : The Funding Source Total should equal the total planning, project, and future costs (as applicable) (Row 7) for each FY. If the Funding Source Total does not equal 100%, review the entries in rows 26 – 38 to verify all applicable fund sources have been identified.

7.0 Planning, Project BCP Worksheets

BCP worksheets are only prepared for the selected alternative (Alt#1). There are two BCP worksheets: (1) Alt 1- Planning BCP and (2) Alt 1- Project BCP. The Alt 1- Planning BCP worksheet takes information from the Planning Cost columns of the Alt 1 - Project worksheet, while the Alt 1- Project BCP worksheet takes information from the FY columns of the Alt 1 - Project and Alt 1- Future Ops worksheets, as applicable.

The two BCP worksheets are structured similarly to the BCP worksheet provided by the DOF. The purpose of this worksheet is to automatically generate most of the information needed for the BCP, thereby, simplifying the alignment of information between the FAWs and the BCP. Most of the cells in the BCP worksheets reference cells in other FAWs so that the data in the BCP Transfer worksheet is predominantly automatically populated as other FAWs are completed, with the following exceptions that the Agency/state entity shall complete, as appropriate:

a. Other Funding to be used, if any (Existing): If other existing fund sources are to be used for specific expenditures (e.g., Positions, General Expense), enter the total amount in the corresponding column. Entries into the “(-) Other Funding to be used, if any (Existing)” column will reduce the Planning or Project BCP (as applicable) funds needed. b. Total Capital Outlay Expenditures: The FAWs are constructed to calculate State Operations and Local Assistance expenditures, both existing and new, but not Capital Outlay expenditures. Therefore, should the Agency/state entity’s project require Capital Outlay expenditures, Agencies/state entities are required to populate these fields in the BCP worksheets manually. c. Other Items (Unclassified, Reappropriation, and Reversion): These amounts are not tracked in the FAWs and, if appropriate to be included in the BCP fiscal sheet, must be added by the requesting Agency/state entity. d. Revenue (General Fund, Other/Special Funds): These amounts are not tracked in the FAWs and, if appropriate to be included in the BCP fiscal sheet, must be added by the requesting Agency/state entity. e. Loans/Revenue Transfers (General Fund, Other/Special Funds): These amounts are not tracked in the FAWs and, if appropriate to be included in the BCP fiscal sheet, must be added by the requesting Agency/state entity.

California Department of Technology SIMM Section 19F Financial Analysis Worksheets

17

8.0 Detail Instructions – Executive Cost Summary, Summary, Current Ops, Alt 1- Project, Alt 1- Future Ops, and Alt 1- Funding Plan Tabs Detailed instructions for the Executive Cost Summary, Summary, Current Ops, Alt 1- (Alt 2-, Alt 3-) Project, Alt 1 (Alt 2-, Alt 3-) Future Ops, Alt 1- Funding Plan tabs, are as follows:

Executive Cost Summary Tab Location, Column/Row Label Name^ Information Row 2 Project Name: Enter the project name. Once entered, this information will automatically populate on all other worksheets. Row 3 Department Name: Enter your department name. Once entered, this information will automatically populate on all other worksheets. Row 4, Column C Project Number: Enter the project number. Once entered, this information will automatically populate on all other worksheets. Row 4, Column D/E

Date: Enter the date. Typically, the date will represent when final project approval is received. Once entered, this information will automatically populate on all other worksheets. Column E (Rows 6 – 11)

Project Costs; Planning Costs (One-Time); Project Costs (One-Time); Future Operations IT Staff & OE&E Costs (Continuing); and, Annual Future Operations IT Costs (M&O)

This provides the total planning, project, and future IT operations costs for the project. Additionally, this information is used in your Stage 4 Approval letter from CDT and will be used to determine your total project budget.

Column E (Rows 7 – 61)

Various Total project costs (planning, project, and future) are automatically populated based on the entries to your Alt 1- Project and Alt 1- Future Ops worksheets.

The Staff OE&E Rollup and Misc. OE&E Rollup totals contain multiple categories of OE&E. Specifically,

Staff OE&E Rollup contains: General Expense; Printing; Communications; Postage; Insurance; Travel – In-State; Travel – Out-of-State; Training; Facilities Operations; and Utilities. Refer to the Alt 1- Project or Alt 1- Future Ops sheets for itemized details.

Miscellaneous OE&E Rollup contains: Departmental Services; Central Administrative Services; Office Equipment; Other; Unclassified/Special Adjustment; Local Assistance. Column B – E (Rows 17 – 27

Consulting and Professional Services: Interdepartmental

These rows display the total cost along with the specific detail to support the total cost of consulting and professional services (interdepartmental). Column B – E (Rows 28 – 38)

Consulting and Professional Services: External

These rows display the total cost along with the specific detail to support the total cost of consulting and professional services (external). Column B – E (Rows 39 – 46)

Consolidated Data Centers These rows display the total cost along with the specific detail to support the total cost of consolidated data center services.

California Department of Technology SIMM Section 19F Financial Analysis Worksheets

19

Summary Tab Location, Column/Row Label Name^ Information Row 1 Department name, Stage X/Version X, Date

No entry required for Department name and Date if the Executive Cost Summary Tab was completed.

Enter the Stage and Version, as needed. Row 2 Project Number and Project Name

No entry required for Project Number and Project Name if the Executive Cost Summary Tab was completed.

Footer SIMM 19F.2, Pages X of X, Automatic Date

Automatic – No entry required.

Location, Column/Row Label Name^ Information Row 4 FY 1, FY 2, FY 3, FY 4, FY5, FY (Various).

Fiscal Year (FY) (July 1 – June 30). Automatic – No entry required (once applicable Alt 1- Project, Alt 2- Project, or Alt 3- Project information entered).

Note: Hidden columns H/I, J/K, LM (for Planning Costs) and AB/AC, AD/AE, AF/AG (For Project Costs may displayed if more than two planning FYs or seven project FYs information is documented. Unhide columns as needed. Row 5 Positions, Dollars Positions and dollars needed for each applicable FY or in total for Average Current Operations Costs, Average Future Operations Costs, and Change in Operations Costs. Rows 6 , 23, 40 Total Project Costs Label.  Rows 7 , 2 4 , 41  Total Personal Services Expenditures

Includes New and Redirected Personal Services. Automatic – No entry required.  Rows 8 , 25 , 42  Total OE&E Expenditures Includes New and Redirected OE&E Expenditures. Automatic – No entry required.  Rows 9 , 26 , 43  Total Local Assistance Funding for local assistance (e.g., grants, local training, call center).  Rows 10 , 2 7 , 4 4 Total Costs Total costs (Personal Services, OE&E, and Local Assistance) for specific FY. Rows 11 , 2 8 , 4 5 Annual Savings/Revenue Adjustments

Label for the summarized savings and/or revenue adjustments.

California Department of Technology SIMM Section 19F Financial Analysis Worksheets

20

Location, Column/Row Label Name^ Information  Rows 12, 29, 46 (^)  Cost Savings Total Project Positions and Dollars – Current Operations Positions and Dollars.  Rows 13, 30, 47  Cost Avoidances/Increased Program Revenues

Total Cost Avoidances and Total Increased Project Revenues based on the project.  Rows 14, 31, 48 (^)  Net Cost (+) or Benefit (-) Sum of Cost Savings, Cost Avoidances, and Increased Program Revenues for the entire project.  Rows 15, 32, 49  Cum. Net Cost (+) or Benefit (-)

Cumulative totals for the Net Cost or Benefit.

 Rows 17, 34, 51 Annual Future Operations Costs (M&O)

Total final future FY IT Costs, which includes IT personal services (redirected/existing/new), overtime, holiday, other, benefits, and OE&E costs for the final future FY once a system is designated as M&O.  Rows 18, 35, 52 Simple Return on Investment

• (Future Costs Compared to Current Costs)

Future Operations divided by the Current Operations. The percentage calculates the overall estimated return once the project is implemented.

Location, Column/Row

Label Name Information Columns B, C Average Current Operations Costs (Before Project) (A)

Current positions and dollars before the project (or if the project is not undertaken).

Columns D – M Planning Costs Planning Costs occur during the development of Stages 2, 3, and 4.

Note: Hidden columns H/I (Year 3), J/K (Year 4), L/M (Year 5 ) applicable only if more than two ( 2 ) FYs of planning information is documented. Unhide columns as needed. Columns N – AG Project Costs (During Project)

Costs after contract award during project implementation.

Note: Hidden columns AB/AC (Year 8), AD/AE (Year 9), AF/AG (Year 10) applicable only if more than seven (7) FYs of project information is documented. Unhide columns as needed. Columns AH, AI Total One-Time (Project) Costs

Total project costs including new and redirected Personal Services, OE&E, and Local Assistance.