2c:null 2d:["$","title",null,{"children":"Financial Statement Data | Mario Delgado | Observable"}] 32:T65d,statementData = { const statementData = []; const getData = (year, rows) => { const getRow = (column, cell) => { const row = column[cell - 1]; return Math.round(row[Object.keys(row)[0]] * 100) / 100; }; const getCategory = (category) => data.filter(({ Category, Year }) => Category == category && Year == year); let sheet = workBook.sheet(`${year} Balance Sheet`, { range: "C:C" }); // Reserve Column const totalReserve = getRow(sheet, rows[0]); sheet = workBook.sheet(`${year} Balance Sheet`, { range: "D:D" }); // Total Column const totalAssets = getRow(sheet, rows[0]); const totalLiabilities = getRow(sheet, rows[1]); const fundBalance = getRow(sheet, rows[2]); sheet = workBook.sheet(`${year} Balance Sheet`, { range: "G:G" }); // Analysis Column const numLots = getRow(sheet, 5) / 1000; sheet = workBook.sheet(`${year} Revenue and Expenses`, { range: "C:C" }); // Budget Column const budgetAssessmentIncome = getRow(sheet, 4); const totalIncome = Math.round(d3.sum(getCategory("Income"), (d) => d.Actual) * 100) / 100; const totalExpense = Math.round(d3.sum(getCategory("Expense"), (d) => d.Actual) * 100) / 100; statementData.push({ year, totalReserve, totalAssets, totalLiabilities, numLots, fundBalance, budgetAssessmentIncome, totalIncome, totalExpense }); }; // getData getData(2019, [10, 17, 27]); getData(2020, [12, 20, 30]); getData(2021, [13, 24, 34]); getData(2022, [14, 25, 36]); getData(2023, [21, 35, 47]); return statementData; }33:[{"id":"42e401b115c8dea86dae446e4b0aaf4a4c127ae8ec781005be0e35a4e7e77865a8110c7f56eb288376494e21dec4e70866a829de6f7e97728abb4f840e692e59","href":"https://static.observableusercontent.com/files/42e401b115c8dea86dae446e4b0aaf4a4c127ae8ec781005be0e35a4e7e77865a8110c7f56eb288376494e21dec4e70866a829de6f7e97728abb4f840e692e59","name":"ConsolidatedBexleyFiancialStatements-3@1.xlsx","type":"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet","lastModified":1706980541182.265,"size":252500},{"id":"68273599d26388c07aba429facfdadf6f808c113d034d3210ac2e2e73056f0b418859095e221bee2116095a669140ba8dc47e47a0ef50f1cac814127a38351ce","href":"https://static.observableusercontent.com/files/68273599d26388c07aba429facfdadf6f808c113d034d3210ac2e2e73056f0b418859095e221bee2116095a669140ba8dc47e47a0ef50f1cac814127a38351ce","name":"ConsolidatedBexleyFiancialStatements@1.xlsx","type":"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet","lastModified":1666782144689.596,"size":197425},{"id":"8b3183081df36fb26b3c1bb2eb5c23676fe2e280a840a769b5775150e4c3de1c9e7e7d37090078b8742a31dbecbb957e07d03bfea899d4de2fe6a4b26d5e44b0","href":"https://static.observableusercontent.com/files/8b3183081df36fb26b3c1bb2eb5c23676fe2e280a840a769b5775150e4c3de1c9e7e7d37090078b8742a31dbecbb957e07d03bfea899d4de2fe6a4b26d5e44b0","name":"ConsolidatedBexleyFiancialStatements@2.xlsx","type":"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet","lastModified":1674940319088.276,"size":226901},{"id":"03cc02f039d28020b434d2d2920b98a00c2cb22343962ee826ee5919700a879d35f819833bd81aa866b7d1a7d96a0c5d5afbd4ef270db5a558fe78f69f1ab0fa","href":"https://static.observableusercontent.com/files/03cc02f039d28020b434d2d2920b98a00c2cb22343962ee826ee5919700a879d35f819833bd81aa866b7d1a7d96a0c5d5afbd4ef270db5a558fe78f69f1ab0fa","name":"ConsolidatedBexleyFiancialStatements@3.xlsx","type":"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet","lastModified":1674943330883.114,"size":229508},{"id":"d090a9eab0022d31a12b2a792ce3714c44c9c4b6d2066cd58894cfbd69c66428ea66093910c41d5475833ff080fc54b7c9e92c5053de8b9c17d06984c5052107","href":"https://static.observableusercontent.com/files/d090a9eab0022d31a12b2a792ce3714c44c9c4b6d2066cd58894cfbd69c66428ea66093910c41d5475833ff080fc54b7c9e92c5053de8b9c17d06984c5052107","name":"ConsolidatedBexleyFiancialStatements-3.xlsx","type":"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet","lastModified":1706733857954.472,"size":252483},{"id":"fb072d114498f25d4199d541bac0664ccaa6b3a73ae5de51411c379f17fc309e5fc6fe4662161c52612a4eb661595bcbbd8e44d42689c3aef3970259e11e4c52","href":"https://static.observableusercontent.com/files/fb072d114498f25d4199d541bac0664ccaa6b3a73ae5de51411c379f17fc309e5fc6fe4662161c52612a4eb661595bcbbd8e44d42689c3aef3970259e11e4c52","name":"ConsolidatedBexleyFiancialStatements-3@2.xlsx","type":"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet","lastModified":1706981099444.631,"size":252580}] 30:{"id":"43ec945a2f75b0a0","slug":null,"title":"Financial Statement Data","thumbnail":"455d947b2840b2c78a6ee923308c888530c31450b5e2fa806cc5520f4eafa207","access_level":"public","create_time":"$D2022-10-22T23:12:08.695Z","update_time":"$D2024-02-03T17:24:58.841Z","view_time":null,"first_public_time":"$D2022-10-22T23:32:52.254Z","has_unpublished_edits":null,"fork":false,"stars":0,"starred":false,"owner":{"type":"team","id":"f57b9f8a19d28778","login":"mariodelgadosr","name":"Mario Delgado ","avatar_url":"https://avatars-next.observableusercontent.com/2d2982ad09c4ca9dfd6570810ae00301e0db6ef798c4f7025ea03d4325d4807d","tier":"starter_2024","bio":"Designing and Engineering a Better Tomorrow"},"version":180,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"1d3988d482a8ec0f","login":"mariodelgadosr","name":"Mario Delgado ","avatar_url":"https://avatars-next.observableusercontent.com/2d2982ad09c4ca9dfd6570810ae00301e0db6ef798c4f7025ea03d4325d4807d","tier":null,"bio":"Designing and Engineering a Better Tomorrow"}],"members":[{"type":"individual","id":"1d3988d482a8ec0f","login":"mariodelgadosr","name":"Mario Delgado ","avatar_url":"https://avatars-next.observableusercontent.com/2d2982ad09c4ca9dfd6570810ae00301e0db6ef798c4f7025ea03d4325d4807d","tier":null,"role":"editor"}],"comments":[{"id":"c9d203a4442ef60f","reply_to_id":null,"content":"Data wrangled for use in other notebooks.","create_time":"$D2022-10-24T16:49:04.409Z","update_time":null,"resolved":true,"user":{"type":"individual","id":"1d3988d482a8ec0f","login":"mariodelgadosr","name":"Mario Delgado ","avatar_url":"https://avatars-next.observableusercontent.com/2d2982ad09c4ca9dfd6570810ae00301e0db6ef798c4f7025ea03d4325d4807d","tier":null}}],"custom_thumbnail":true,"trashed":false,"lapsed":null,"subscribed":null,"forks":1,"collection":{"id":"16d43afe613515a1","slug":"financials","title":"Financials","owner":{"id":"f57b9f8a19d28778","login":"mariodelgadosr","name":"Mario Delgado "}},"type":"notebook","body":{"titling":"auto","title":"Financial Statement Data","cells":[{"id":0,"value":"# Financial Statement Data","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":48,"value":"* [2019 Financial Statement](https://1drv.ms/x/s!AhxoDV92fbJirBaBORA34qzbXvmc?e=QKdXPA)\n* [2020 Financial Statement](https://1drv.ms/x/s!AhxoDV92fbJirBs1jdm2YrGJjx7x?e=AWerSk)\n* [2021 Financial Statement](https://1drv.ms/x/s!AhxoDV92fbJirB-WP7F6YDYaPfdK?e=CKscEp)\n* [2022 Financial Statement](https://1drv.ms/x/s!AhxoDV92fbJirkmgBsXkMoYkSMuf?e=lnSvXb)\n* [2023 Financial Statement](https://1drv.ms/x/s!AhxoDV92fbJirzQMTPjtBI3Lc9Ud?e=JQFjHP)","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":40,"value":"md`Embedded Excel Workbook ([Direct Link to Online Version](https://1drv.ms/x/s!AhxoDV92fbJirkd9APIJYODCYcB9?e=ePre01)):\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":23,"value":"// https://observablehq.com/@observablehq/standard-library\n// Notebook users can download embedded workbook with the download option at bottom right of the iframe\n/* downloadXMLButton = DOM.download(\n await FileAttachment(\"ConsolidatedBexleyFiancialStatements.xlsx\").blob(),\n \"ConsolidatedBexleyFiancialStatements.xlsx\",\n \"Download Excel file\"\n) */\nmd``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":13,"value":"viewof search = Inputs.search(data, {\n label: \"Seach Table\",\n columns: data.columns.slice(1)\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":17,"value":"tableRows = Inputs.table(search)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":5,"value":"workBook = await FileAttachment(\n \"ConsolidatedBexleyFiancialStatements-3@2.xlsx\"\n).xlsx()","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":159,"value":"**Note**: ***data*** is including:\n* Account ***9910 Reserves Pooled*** as an expense line item for analyses. Rizzetta Financial Statements don't include this when summarizing total expenses.\n* Yearly Net Capital Contributions is included as an income item used for expenses","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":7,"value":"// Sankey Analysis includes 9910 Reserves Pooled as an expense\ndata = {\n const data = workBook.sheet(\"Sankey Analysis Data\", { headers: true });\n // Extract Year\n data.forEach((d) => (d.Year = `${d.Year.getFullYear().toString()}`));\n return data;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":75,"value":"$32","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W33","stdlib":"1"}} 31:"$30:body"