Process - Using Office Templates

From Calidus HUB

This page ins intended to help with the process of using Office Document Templates, with specific instructions on BRD, EST and COST sheet templates.

Files

  • Templates - document templates
    • Standard Aptean Templates - you should check for updates on Sharepoint - these may be old. Check
    • BRD SFNumber Client v0.02.xltx - capturing business requirements from notes fragments, cross-referencing to create SCRs for a full project,
    • Calidus Network Diagram Template.vstx - Technical Architecture Diagram Visio Template
    • Client_BugList Analysis.xltx - referenced in "Process - Reporting Rework Faults" - paste in DevOps extracted bugs here, get reported stats.
    • COST-SFNumber Client Cost Sheet v1.07.xltx - cost sheet for full system implementations/new projects.
    • DEVLOG-SFNumber-ClientCode-Development & Implementation Issues List.xltx - a template for a spreadsheet to capture UAT issues and feed back from DevOps.
    • EST-CaseNo-Desc v0.01.dotx - estimate document template
    • EST-SFNumber Client Description v1.02.xltx - estimate spreadsheet in hours and days, calculating based on parameters.
    • FS-CaseNo-Desc v0.01.dotx - FS template. Also in Assist
    • POT-SFNumber Description v1.1.dotx - Proof of Testing Template
    • Release Note Template Aptean POD - Calidus Edition v4.x.xx.xx.dotx - Release Notes - use Assist template instead.
    • SCR-CaseNo-Desc v0.1.dotx - Small Change Request template - also in Assist.
    • Screen Templates.xls - a spreadsheet to help design oracle forms/new .NET screens
    • SDD-CaseNo-Desc v0.01.dotx - Solution Design/Requirements template. Also in Assist.
    • SOW-SFOppNo-Desc v0.02.dotx - Statement of Work - see notes above for validity.
    • SUP-Cust-Desc v0.01.dotx - handover to support document template.
    • TP-CaseNo-Desc v0.01.dotx - Test Plan template
    • Unlikely to ever be of use to anyone:
      • BPA-CaseNo-Desc Agenda v0.01.dotx - Elucid agenda template.

Notes on Templates

Using Office Templates

Note that templates do not open natively in Teams - you have to download them to use them.

They are double-licked on, then the appropriate office app will create a copy and open up that copy.

You can then save that as the new document you want to create WITHOUT affecting the template on which it is based.

In summary:

  • Download the template
  • Double-click on it

If you want to edit the template, first open your office app (e.g. Word or Excel), then open the template. Any change you make and save will be saved to the template, and then any NEW documents created from that template will have your changes. This will not affect any documents already created.

Be kind - upload your modification back to Teams as a new version - maintain the system for everyone.

Using the EST template Spreadsheet

For a one-off estimate and SOW. See later for use to hold technical notes for a bunch of related estimates in a BRD or Cost Sheet.

  • Calc tab
    • Enter the Customer and Rates year e.g. NHSBT, 2025. The rates for the sheet will populate below, if they have been set up - see Rates tab.
    • Fill in the yellow fields. Everything else will calculate based on settings. See Rates tab..
    • Fields in yellow highlight are designed for you to enter data.
      • REQ is time you have taken so far on the scoping.
      • EST is the time taken estimating, talking to R&D, etc.
      • REL costs should be 0.5d or 4 hours per system affected.
      • IMP costs should be the amount of time to run additional scripts, set up the data for the customer, any agreed post-implementation consultancy time required.
    • You can then copy the Cost Detail section for pasting into an Estimate, or the lower table for pasting into an SOW.
  • Notes tab
    • Keep a note of all your discussions, and also your prosed technical solution notes here.
    • Ensure that System, Lang and Est columns are populated where required. Area is for your own notes.
    • The table will total your estimates at the bottom.
    • The sheet will calculate percentages by language and system - you may need to refresh the data source or tables to make this happed. Easiest is to select the Data tab menu at the top, and click Refresh All.
  • Rates tab
    • You should enter the customer, year, activity, day rate and currency here if not present. Contact your manager/PM for the relevant rates. You should amend the template with the new rates, so you don't have to do this every time. Remember rates change every year. Hourly rates will automatically calculate.
    • Settings show how the Calc tab calculates the rest of the settings.
      • FS/ST/CON/PM - percentage of DEV to apply to calculate these figures in the sheet.
      • Rounding - how closely to round. - 0.25 or 0.1 Leave as s.
      • Hours per day - uses this to calculate the dev activity costs, but also the rates. Leave as is.
      • Contingency - be led by your PM. Assume 10 or 20% uplift as required.

Notes:

  • If complicated, I always include a contingency line in the notes, adding a little more to the estimate on top of any global contingency added to the estimate by the calculations. This is to account for something difficult taking longer than expected. Be led by R&D advice for this.

Using the BRD template

The purpose is to help the process of taking notes and translating them to changes when doing a larger analysis.

It's also a working document to refine and capture multiple meetings and notes, all the way through to fully-qualified and quantified SCRs.

  • Notes tab.
    • You will have taken notes - give them a reference (e.g. TW/20251211-1) and enter this and a brief description in the Refs tab. Refer to any supporting docs in this note like the ER(s) you gene rated the notes from.
    • Enter your notes here in the Description column. Break them down to small pieces/statements, in general the smaller the better.
    • Then for each note, categorise by Area. Note if more complicated than this, add a column for sub-area.
    • Add a section title - Number those sections by logical area.
      • Use two decimal places for IDs. For WMS that might be
      • 1.00 - General Information
      • 2.00 - Config
      • 3.00 - Receipt
      • 4.00 - Putaway
      • 5.00 - Movements
      • 6.00 - Stock Take
      • 7.00 - Orders
      • 8.00 - Picking
      • etc.
    • Make sure every note is then IDd under the section e.g. 1.01, 1.02, etc. This will be useful later.
    • Mark every note you have entered has Source column marked with the Ref for that meeting note.
    • Mark every note as to which System this applies to (WMS, PORTAL, WCS, etc) in the System column.
    • If a note refers to or expands on a previous note line, use XREF to show the note to which this cross-references. Don't just type in the ID, type in "=" then selet the ID field of the note you are cross-referencing.
    • Do this for all your meetings and notes, from as many people who provide them to you.
    • Now use SCR column t decide whether you think this may be a change. Y, N, ? (unsure). The column will colour code, so anything red/yellow is something you have to deal with.
    • Enter any clarifications you want with the customer in the Notes column.
    • Run through and MoSCoW each change. By convention:
      • M(ust) - critical to do, phase 1.
      • S(hould) - will be included in the estimates phase 1
      • C(ould) - will not be included phase 1, could do it later. Will highlight yellow
      • W(ould) - will not be done. Will highlight yellow
      • ? - Unsure. Will highlight yellow
  • SCRs tab.
    • Now go through each note you have marked as SCR = "Y" or "?". Basically, if you need an SCR, add it to the list in this SCRs tab.
    • Enter the SCR number.
      • By convention, anything chargeable to the customer starts at 01, anything that might be implementation or product dev starts at 99 and counts down.
      • By convention, if you want to split an SCR into smaller components (perhaps for multiple systems development, add an A, B, C etc to the end).
    • Cross-ref the IDs in the BR (optional)
    • Enter System, Area, Sub-area and a short description.
    • Categorise however you like, but expect that PROD would be product development, you might use this for phases, or leave blank and work with it later. Basically it's usually a good column to sort and format stuff later. Consider adding conditional formatting on NA to make it red and strikethrough, adding why this change is no longer applicable in the Notes column.
    • Enter a basic Hours or Days (whatever your favoured convention is here). Don't get too bogged down in details just yet - this is tshirt sizing at this time (2/5/10/25, etc). The total column will calculate for you based on some basic uplift settings in the Summary sheet.
    • Hide the following columns initially - they are only used when we start to make further refinements later with the customer
      • Initially provisioned
      • Expected
      • Delta
      • Delta Notes
    • Use the cross-referenced BR ID to get more information.
    • Paste any tech or clarification notes into the Notes column.
    • When the SCR is created, enter that in the SCR column in the Notes sheet. Again, use "=" and then select the ID column on the SCR tab to get it as an updating cross-reference - if you change SCR numbers later, the references will update on the prior sheet and vice versa. Enter that ID against every BR note that the SCR could reference. Extremely useful for discussions later.
  • Summary sheet
    • Maintain the uplifts here, similar to the EST sheet.
    • You also have summary stats of changes/days per category, very useful for phasing, e.g.

Now you have a list of SCRs and t-shirt sizes, ballpark costings and criticality to start discussing with the customer.

As discussions go on, more notes can be generated - follow the same process to put them in the BR sheet. Amend any SCRs.

When SCRs have been initially discussed and categorised, the customer will likely want you to firm up the estimates.

Use the SCR numbers and fully cross referenced notes you have to put together discussion notes for use with R&D.

The best way to go about this is to use a variant of the EST sheet above.

You may also move forward from this point by creating a change document, SOW or SDD. In any case, you can use this sheet to keep track of where you are up to in documenting and ensurign all requirements are met.

  • Notes tab:
    • Resolved % - has this been resolved 100% by the SCRs?
    • Section - what section is this populating the final doc (number, text or just a Y to say you've included it/copied the descriptive text into the final doc.)
  • SCRs tab:
    • Doc - have you added this into the final doc or created a doc for it - y.

Using the EST template for multiple SCRs.

We will use the sheet as before, but now we will make a copy of Cost tab and call it SCR-01.

We will then use this to collate discussion notes from he BRD into this tab.

We will do this for each SCR.

We will either estimate ourselves and make technical notes, or we will discuss with R&D and make technical notes in here, deciding on the cost for each line, if any.

Then we can use the total to revise/refine the days back in the BRD.

We should also fill in the EST sheet in exactly the same way as before, including release and imp totals - although these are not used right now, these may be useful later if the customer chooses to break these SCRs down into other phases, or do SCRs singly.

Using the Cost Sheet Template.

This can be used right from the start as a full project cost sheet, or can be used after the stages above to start to pull together full project costs.

note that, due to rounding, a fully qualified BRD list of SCRs will probably not exactly match the costs in a cost sheet - agree with the PM which is the cost mechanism and use accordingly. It's up the PM to manage this with the customer.

Note also that this template includes some of the tabs from the BRD template (Notes, References) - if not going from BRD initially, you can use the same process here on these tabs to do a similar process of creating notes and cross-referencing SCRs, but this time the cross-referenced SCRs go in the Configuration and Development Tab.

  • Profile tab
    • Enter the olive columns. Specifically:
    • Enter your rates.
    • "Fixed Cost" will apply an uplift like the Estimate sheet.
    • "Inc Optional" will include phase 1 and optional (phase X) changes in the total costs produced. Typically "No".
    • "Full Project" - applies system testing rates, typically the same as unit testing for all the changes, in the C&D tab. Typically "Yes".
    • "Hourly" - hourly rather than daily estimates - typically "Yes"
    • Enter the total sites and users for each Calidus product - this is typically used by the team producing SOWs to determine licence costs.
    • The rest of the sections should be populated if we don't have format RFQ information (which we should reference in the RFQ tab) or SDD/BRD notes (which should be referenced here or in other documents.)
  • Assumptions & Comments - typically this is a restatement of general SOW comments. But you can add specifics here that the SOW team will use to help qualify the SOW.
  • Configuration and Development tab
    • You will determine before or during this process a list of SCRs that you want to develop as part of this project, either now, pre-known requests from the customer, or from a BRD or BRD-style process. Enter them in the Configuration and Developments tab in a similar way to the BRD process above.
      • Phases can be shown in the Phase 1 and Optional changes sections. Split them accordingly.
      • Req column references the Notes, as in BRD.
      • Enter the System, Area, Sub-area and Description as before.
      • Enter the olive columns - specifically:
        • Dev - the days/hours you estimate (either tshirt or from an estimate or combined estimate sheet, as before.)
        • Config/Spec - any CON, REQ, Pre or Post imp cost SPECIFICALLY FOR THIS SCR - we will count some global values for these later, so just specific to this change.
      • Enter solution notes and comments as per the BRD process.
  • Cost Details tab
    • You can enter the Olive columns. Some calculate already, but over-type if you're confident.
    • Use the notes column to qualify and quantify the value here. For example, you may add 30 hours for Release under the implementations section. You may then want to quantify and qualify that e.g. "Assuming 2 major releases over 2 systems"
    • Technical assistance (Other Sites) - If you're trying to cost a major implementation project across multiple sites, you may want to enter a value here. Simply enter the number of hours or days you want in the Notes column - the sheet will then calculate the total amount for other sites (total sites from the Profile sheet, minus 1)
  • Calculations tab
    • Some cross-references and settings, much like EST and BRD. Importantly, also:
      • Rounding - use 0.1 for daily and 0.25 for hourly.
      • Imp Hours per SCR - this is a number of hours to globally add for implementation per SCR, for the project costs. This covers standard consultancy and implementation changes, not SCR specific ones. Also used to calculate Super User Training costs, testing assistance, go live assistance.
      • Support Transition Hours per SCR - handover to support, plus 7.5 hours.

The result of this is the Summary of Costs tab, which is essentially a copy/paste into an SOW.

Note that the year 1/2 costs are included here. This can be modified to include the WCS/EPOD year 1/2 costs, or can be left blank/deleted - typically the Sales team will do this licensing and additional costs separately, and may need to include SaaS costs as well.

Retrieved from ‘https://calidusassist.adcservices.apteancloud.com/calidus-assist/OBS/index.php?title=Process_-_Using_Office_Templates&oldid=15065’