<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 14 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
p.MsoAcetate, li.MsoAcetate, div.MsoAcetate
{mso-style-priority:99;
mso-style-link:"Balloon Text Char";
margin:0in;
margin-bottom:.0001pt;
font-size:8.0pt;
font-family:"Tahoma","sans-serif";}
p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
{mso-style-priority:34;
margin-top:0in;
margin-right:0in;
margin-bottom:0in;
margin-left:.5in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
span.EmailStyle18
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:windowtext;}
span.EmailStyle19
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.BalloonTextChar
{mso-style-name:"Balloon Text Char";
mso-style-priority:99;
mso-style-link:"Balloon Text";
font-family:"Tahoma","sans-serif";}
span.EmailStyle22
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:black;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-US" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p class="MsoNormal"><span style="color:black">We use a very simple spreadsheet to calculate this. It has the following columns: Resource name; Cost; Payment notes; “Search” usage; “Search” usage CPU; “Item” usage; “Item” usage CPU; Stats notes; Report type;
and Summary usage.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:black"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:black">Payment notes include information such as calculations used to determine cost (typically in the case of a single payment for multiple resources). We gather the most reliable data we can for usage, and add it to
the appropriate column; anomalies or special cases are noted in the Stats notes column (such as “Items = video playbacks”). Our usage is calendar year, but our cost is whatever the invoicing cycle for that specific product is, and includes hosting fees where
appropriate. In cases where the payment time period is split mid-calendar year, we assign it to whichever year is most reflects (for example, a September 2014-August 2015 payment would be used for calculating 2015 CPU). Report type is useful for quickly identifying
non-standard stat sources, and differentiating between BR1 and BR2. Summary usage is a formula that compares the “Searches” usage and the “Items” usage, and displays the highest number.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:black"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:black">Early each calendar year, I gather all of this information so it is available for our new fiscal year (April – March). This year, there were 252 resources in the spreadsheet.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:black"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:black">At the title level, we load JR1s into our ERMS (III’s Sierra ERM) and that automatically calculates CPU for us.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:black"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:black">Of course, CPU is not the only deciding factor during renewals, so the quality of this information works for our purposes. For context, we are a small research university with approximately 3400 FTE.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:black"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:black">I hope this helps!<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:black">Mandi<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:black"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">From:</span></b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> Eril-l [mailto:eril-l-bounces@lists.eril-l.org]
<b>On Behalf Of </b>Harker, Karen<br>
<b>Sent:</b> Monday, October 03, 2016 9:38 AM<br>
<b>To:</b> Terri Winchcombe; eril-l@lists.eril-l.org<br>
<b>Subject:</b> Re: [Eril-l] cost per use<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span style="color:#1F497D">If you use a subscription agent for your journals, you may be able to get them to provide this info for you.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">You don’t need a database, per se. Spreadsheets will work fine. The hardest part will be gathering the data and defining the variables. While there are only 2 parameters in a CPU calculation (cost and use),
you must be clear on what is meant by each one – what exactly do you mean by cost? What was paid? For what time frame? 1 year? 1 month? Which years? What about those years when the cost was extended to include more than one year? Or more than one resource
(like Big Deals)? What is a “use”? FTD? PDF only? PDF & HTML? Abstract view? What about those resources that don’t provide COUNTER stats?<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">The calculation itself is the easiest part – once you have defined the measures for each of the parameters and then gathered the data, just divide the Costs column by the Use column. I highly recommend learning
some advanced features of Excel to make it easier. The three features that have helped me the most are:<o:p></o:p></span></p>
<p class="MsoListParagraph" style="text-indent:-.25in"><span style="font-family:Symbol;color:#1F497D">·</span><span style="font-size:7.0pt;font-family:"Times New Roman","serif";color:#1F497D">
</span><span style="color:#1F497D">Format as Table makes it quick to add columns & calculations to all the cells in each column<o:p></o:p></span></p>
<p class="MsoListParagraph" style="text-indent:-.25in"><span style="font-family:Symbol;color:#1F497D">·</span><span style="font-size:7.0pt;font-family:"Times New Roman","serif";color:#1F497D">
</span><span style="color:#1F497D">VLOOKUP() makes it easy to tie data from multiple worksheets into one [it works like a database]<o:p></o:p></span></p>
<p class="MsoListParagraph" style="text-indent:-.25in"><span style="font-family:Symbol;color:#1F497D">·</span><span style="font-size:7.0pt;font-family:"Times New Roman","serif";color:#1F497D">
</span><span style="color:#1F497D">Conditional formatting – color scales (from the 10<sup>th</sup> to the 90<sup>th</sup> percentile) make decision-making easier by highlighting the really good & really bad performers.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<div>
<p class="MsoNormal"><span style="color:#1F497D">Karen R. Harker, MLS, MPH<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Collection Assessment Librarian<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">University of North Texas Libraries<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">1155 Union Circle, #305190<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Denton, Texas 76203-5017<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">940-565-2688<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><a href="http://librariesareforuse.wordpress.com">http://librariesareforuse.wordpress.com</a>
<o:p></o:p></span></p>
</div>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b>From:</b> Eril-l [<a href="mailto:eril-l-bounces@lists.eril-l.org">mailto:eril-l-bounces@lists.eril-l.org</a>]
<b>On Behalf Of </b>Terri Winchcombe<br>
<b>Sent:</b> Monday, October 03, 2016 10:24 AM<br>
<b>To:</b> <a href="mailto:eril-l@lists.eril-l.org">eril-l@lists.eril-l.org</a><br>
<b>Subject:</b> [Eril-l] cost per use<o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span lang="EN-CA">Hi There,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-CA">I’m trying to calculate cost per use of our electronic serials. So far I’m just putting the pieces together in excel. I don’t know much about access so I haven’t attempted to create a database. Do you have any advice
for me? Do you have spreadsheets or a database sample you could share with me?<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-CA"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-CA">Thanks in advance for your help.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-CA">Terri<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-CA"><o:p> </o:p></span></p>
<p class="MsoNormal"><i><span lang="EN-CA" style="font-size:9.0pt;color:#262626;mso-fareast-language:EN-CA">Terri Winchcombe, CAPM<o:p></o:p></span></i></p>
<p class="MsoNormal"><i><span lang="EN-CA" style="font-size:9.0pt;color:#262626;mso-fareast-language:EN-CA">Manager, Acquisition Services<o:p></o:p></span></i></p>
<p class="MsoNormal"><i><span lang="EN-CA" style="font-size:9.0pt;color:#262626;mso-fareast-language:EN-CA">Patrick Power Library (SMU6500)
<b>|</b> Saint Mary’s University<o:p></o:p></span></i></p>
<p class="MsoNormal"><i><span lang="EN-CA" style="font-size:9.0pt;color:#262626;mso-fareast-language:EN-CA">923 Robie Street
<b>| </b>Halifax, Nova Scotia <b>| </b>Canada B3H 3C3<o:p></o:p></span></i></p>
<p class="MsoNormal"><i><span lang="EN-CA" style="font-size:9.0pt;color:#262626;mso-fareast-language:EN-CA"><o:p> </o:p></span></i></p>
<p class="MsoNormal"><i><span lang="EN-CA" style="font-size:9.0pt;color:#262626;mso-fareast-language:EN-CA">Voice 902 420-5535
<b>|</b> Fax 902 420-5561<o:p></o:p></span></i></p>
<p class="MsoNormal"><i><span lang="EN-CA" style="font-size:9.0pt;color:#262626;mso-fareast-language:EN-CA"><a href="mailto:terri.winchcombe@smu.ca"><span style="color:#262626">terri.winchcombe@smu.ca</span></a><o:p></o:p></span></i></p>
<p class="MsoNormal"><span lang="EN-CA"><o:p> </o:p></span></p>
</div>
</body>
</html>