{"text_format":"1","workbook":"xlsx","n_sheets":6,"sheets":["Instructions","Period End Dates","Lookups","Mapping","JE Output","Data Source"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":4,"comments":2,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Instructions","index":0,"n_rows":37,"n_cols":13,"rows_kept":37,"cut":false,"hidden":false,"merged":[[29,0,32,9],[17,1,21,9],[15,1,16,9],[22,1,22,9],[1,1,2,9],[3,1,3,9],[4,1,6,9],[7,1,12,9],[23,1,26,9]]}
["Instructions - Initial Setup"]
[1,"Fill in your period end schedule on the Period End Dates sheet.  An example is listed by default - clear the example labels/dates to insert your own.",null,null,null,null,null,null,null,null,null,null,"JE Start Date:"]
[null,null,null,null,null,null,null,null,null,null,null,null,"JE End Date:"]
[{"$f":"A2+1","v":2},"Identify the data source for your JE and copy it to the Data Source sheet."]
[{"$f":"A4+1","v":3},"On the JE Output sheet, add the labels for the fields required for your journal entry (i.e. amount, date, reference, description, currency, etc.).  Enter these in the exact same order/format you would for a JE import."]
[]
[]
[{"$f":"+A5+1","v":4},"On the Mapping sheet, set up a mapping of the columns from the Data Source sheet and how they should flow into the JE Output sheet.  This can be by directly copying the values from the Data Source sheet, applying fixed values, or applying formulas.  Make sure to set the mapping for two separate lines (i.e. the debit and the credit) in the two sets of columns.  Leverage formulas to reverse the signs or apply other calculations on the values in the Data Source sheet."]
[]
[]
[]
[]
[]
[]
["Instructions - Ongoing Usage"]
[1,"Run the Clear Data macro by clicking the green button to the right.  This will clear the data from row 2 down to the end on both the Data Source and JE Output sheets."]
[]
[{"$f":"A16+1","v":2},"To record journal entries for multiple periods, enter dates in the JE Start Date and JE End Date fields above to the right.  If these are used, a date field must be specified on the Mapping sheet using a [PED] fixed value.  The period-end date will be inserted for the JE lines.  If the JE start/end dates are not used (left blank), only one set of JE lines will be created, and the dates used for them can flow from the Data Source sheet."]
[]
[]
[]
[]
[{"$f":"A18+1","v":3},"Get the data to be used in the Data Source sheet and copy it to that sheet."]
[{"$f":"A23+1","v":4},"Run the Create JE Lines macro by clicking the green button to the right.  This will create JE lines on the JE Output sheet for one or more periods by leveraging the Data Source sheet and the Mapping sheet to copy the data to the right place, apply fixed values, or apply formulas."]
[]
[]
[]
[]
["Development Notes",null,null,null,null,null,null,"Last updated:",null,"2021-10-19"]
["This template was developed by Derek Henry, CPA, CFE after working at several different companies that followed retail calendars.  It can be used, modified, and distributed without limitation.  The developer and any affiliates assume no liability for any damages caused based on the reliance of this file.  Adequate testing for your use case is highly recommended."]
[]
[]
[]
["The live version of this file is hosted at:"]
["https://excelevate.co/wp/wp-content/uploads/2021/10/JE-Automation-Template.xlsm"]
["For comments, questions, and suggestions, please submit them at:"]
["https://excelevate.co/wp/index.php/home/support/feedback-form/"]

{"sheet":"Period End Dates","index":1,"n_rows":37,"n_cols":3,"rows_kept":37,"cut":false,"hidden":false,"merged":[]}
["Period Label","Period Start Date","Period End Date"]
[{"$f":"YEAR(C2)&\"-\"&IF(MONTH(C2)<10,\"0\",\"\")&MONTH(C2)","v":"2020-01"},"2020-01-01",{"$f":"EOMONTH(B2,0)","v":"2020-01-31"}]
[{"$f":"YEAR(C3)&\"-\"&IF(MONTH(C3)<10,\"0\",\"\")&MONTH(C3)","v":"2020-02"},{"$f":"C2+1","v":"2020-02-01"},{"$f":"EOMONTH(B3,0)","v":"2020-02-29"}]
["2020-03",{"$f":"C3+1","v":"2020-03-01"},{"$f":"EOMONTH(B4,0)","v":"2020-03-31"}]
["2020-04","2020-04-01","2020-04-30"]
["2020-05","2020-05-01","2020-05-31"]
["2020-06","2020-06-01","2020-06-30"]
["2020-07","2020-07-01","2020-07-31"]
["2020-08","2020-08-01","2020-08-31"]
["2020-09","2020-09-01","2020-09-30"]
["2020-10","2020-10-01","2020-10-31"]
["2020-11","2020-11-01","2020-11-30"]
["2020-12","2020-12-01","2020-12-31"]
[{"$f":"YEAR(C14)&\"-\"&IF(MONTH(C14)<10,\"0\",\"\")&MONTH(C14)","v":"2021-01"},"2021-01-01","2021-01-31"]
["2021-02","2021-02-01","2021-02-28"]
["2021-03","2021-03-01","2021-03-31"]
["2021-04","2021-04-01","2021-04-30"]
["2021-05","2021-05-01","2021-05-31"]
["2021-06","2021-06-01","2021-06-30"]
["2021-07","2021-07-01","2021-07-31"]
["2021-08","2021-08-01","2021-08-31"]
["2021-09","2021-09-01","2021-09-30"]
["2021-10","2021-10-01","2021-10-31"]
["2021-11","2021-11-01","2021-11-30"]
["2021-12","2021-12-01","2021-12-31"]
["2022-01","2022-01-01","2022-01-31"]
["2022-02","2022-02-01","2022-02-28"]
["2022-03","2022-03-01","2022-03-31"]
["2022-04","2022-04-01","2022-04-30"]
["2022-05","2022-05-01","2022-05-31"]
["2022-06","2022-06-01","2022-06-30"]
["2022-07","2022-07-01","2022-07-31"]
["2022-08","2022-08-01","2022-08-31"]
["2022-09","2022-09-01","2022-09-30"]
["2022-10","2022-10-01","2022-10-31"]
["2022-11","2022-11-01","2022-11-30"]
["2022-12","2022-12-01","2022-12-31"]

{"sheet":"Lookups","index":2,"n_rows":27,"n_cols":8,"rows_kept":27,"cut":false,"hidden":false,"merged":[]}
["Employee ID","Cost Center",null,null,null,"Cost Center","Account","Entity"]
["Adam.Johnson",1012,null,null,null,1001,6510,"S1000"]
["Fred.Adams",1008,null,null,null,1003,6510,"S1000"]
["Lance.Swift",1007,null,null,null,1004,6510,"S1000"]
["Quinten.Roberts",1010,null,null,null,1006,6510,"S1000"]
["Will.Harper",1001,null,null,null,1007,6510,"S1000"]
["Charlie.Henson",1001,null,null,null,1008,6510,"S1000"]
["Greg.Morris",1023,null,null,null,1009,6510,"S1000"]
["Jason.Washington",1004,null,null,null,1010,6510,"S1000"]
["Nolan.Scott",1021,null,null,null,1012,6510,"S1000"]
["Ralph.Powell",1008,null,null,null,1014,6510,"S1000"]
["Uberto.Jaurez",1009,null,null,null,1016,6510,"S1000"]
["Zach.Avery",1020,null,null,null,1020,6515,"S1005"]
["Bob.Jones",1001,null,null,null,1021,6515,"S1005"]
["Harry.Sierra",1025,null,null,null,1022,6515,"S1005"]
["Karl.Baker",1014,null,null,null,1023,6515,"S1005"]
["Paul.Zedco",1004,null,null,null,1025,6515,"S1005"]
["Trevor.Williams",1022]
["Xavier.Thompson",1006]
["Don.Pettis",1012]
["Edgar.Gonzalez",1008]
["Ian.Clonts",1008]
["Mark.Brantley",1003]
["Oscar.Tanner",1025]
["Steve.Lansing",1008]
["Victor.McDonald",1016]
["Yazier.Hernandez",1014]

{"sheet":"Mapping","index":3,"n_rows":16,"n_cols":16,"rows_kept":16,"cut":false,"hidden":false,"merged":[]}
["JE Output Line 1",null,null,null,null,"JE Output Line 2"]
["JE Column","SD Column","Fixed Value","Formula",null,"JE Column","SD Column","Fixed Value","Formula",null,null,"JE Output",null,null,"Data Source"]
["A",null,"GL",null,null,"A",null,"GL",null,null,null,"A","Source",null,"A","First Name"]
["B",null,"2021-03-31",null,null,"B",null,{"$f":"C4","v":"2021-03-31"},null,null,null,"B","Transaction Date",null,"B","Last Name"]
["C","N",null,"=VLOOKUP(L2,Lookups!$F:$H,3,FALSE)",null,"C","N",null,"=VLOOKUP(L2,Lookups!$F:$H,2,FALSE)",null,null,"C","Entity",null,"C","Employee ID"]
["D","M",null,"=VLOOKUP(L2,Lookups!$F:$H,2,FALSE)",null,"D","M",3005,null,null,null,"D","Account",null,"D","Grant ID"]
["E","L",null,"=VLOOKUP(C2,Lookups!$A:$B,2,FALSE)",null,"E","L",null,"=VLOOKUP(C2,Lookups!$A:$B,2,FALSE)",null,null,"E","Cost Center",null,"E","Grant Date"]
["F","G",null,null,null,"F","K",null,"=-G2",null,null,"F","Amount",null,"F","Type"]
["G","J",null,"=\"Grant ID \"&D2",null,"G","J",null,"=\"Grant ID \"&D2",null,null,"G","Reference",null,"G","Period Expense"]
["H",null,"Record stock option expense",null,null,"H",null,{"$f":"C10","v":"Record stock option expense"},null,null,null,"H","Description",null,"H","Before Accumulated Expense"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,"I","After Accumulated Expense"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,"J","Reference"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,"K","Inverse"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,"L","Cost Center"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,"M","Account"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,"N","Entity"]

{"sheet":"JE Output","index":4,"n_rows":1,"n_cols":13,"rows_kept":1,"cut":false,"hidden":false,"merged":[]}
["Source","Transaction Date","Entity","Account","Cost Center","Amount","Reference","Description",null,"Checks:",{"$f":"SUM(F2:F5000)","v":0},{"$f":"COUNTIFS(F2:F5000,\">\"&0)","v":0},{"$f":"COUNTIFS(F2:F5000,\">\"&0)","v":0}]

{"sheet":"Data Source","index":5,"n_rows":27,"n_cols":14,"rows_kept":27,"cut":false,"hidden":false,"merged":[]}
["First Name","Last Name","Employee ID","Grant ID","Grant Date","Type","Period Expense","Before Accumulated Expense","After Accumulated Expense","Reference","Inverse","Cost Center","Account","Entity"]
["Adam","Johnson","Adam.Johnson","00000001","2020-03-15","RSU",1790.37,17903.699999999997,16113.329999999998]
["Fred","Adams","Fred.Adams","00000002","2020-03-15","RSU",3410.29,34102.9,30692.61]
["Lance","Swift","Lance.Swift","00000003","2020-03-15","RSU",300.99,3009.9,2708.91]
["Quinten","Roberts","Quinten.Roberts","00000004","2020-03-15","RSU",4035.8,40358,36322.199999999997]
["Will","Harper","Will.Harper","00000005","2020-03-15","RSU",551.53,5515.2999999999993,4963.7699999999995]
["Charlie","Henson","Charlie.Henson","00000006","2020-06-15","RSU",1439.49,15834.39,14394.9]
["Greg","Morris","Greg.Morris","00000007","2020-06-15","RSU",3644.77,40092.47,36447.700000000004]
["Jason","Washington","Jason.Washington","00000008","2020-06-15","RSU",4361.0600000000004,47971.66,43610.600000000006]
["Nolan","Scott","Nolan.Scott","00000009","2020-06-15","RSU",3684.84,40533.240000000005,36848.400000000009]
["Ralph","Powell","Ralph.Powell","00000010","2020-06-15","NQSO",2159.5500000000002,23755.050000000003,21595.500000000004]
["Uberto","Jaurez","Uberto.Jaurez","00000011","2020-06-15","NQSO",3219.53,35414.83,32195.300000000003]
["Zach","Avery","Zach.Avery","00000012","2020-06-15","RSU",984.62,10830.82,9846.1999999999989]
["Bob","Jones","Bob.Jones","00000013","2020-09-15","RSU",921.32,11055.84,10134.52]
["Harry","Sierra","Harry.Sierra","00000014","2020-09-15","NQSO",1063.51,12762.119999999999,11698.609999999999]
["Karl","Baker","Karl.Baker","00000015","2020-09-15","RSU",533.83000000000004,6405.9600000000009,5872.130000000001]
["Paul","Zedco","Paul.Zedco","00000016","2020-09-15","RSU",727.84,8734.08,8006.24]
["Trevor","Williams","Trevor.Williams","00000017","2020-09-15","RSU",1579.5,18954,17374.5]
["Xavier","Thompson","Xavier.Thompson","00000018","2020-09-15","NQSO",3986.95,47843.399999999994,43856.45]
["Don","Pettis","Don.Pettis","00000019","2020-12-15","NQSO",2581.8000000000002,33563.4,30981.600000000002]
["Edgar","Gonzalez","Edgar.Gonzalez","00000020","2020-12-15","RSU",2514.42,32687.46,30173.040000000001]
["Ian","Clonts","Ian.Clonts","00000021","2020-12-15","NQSO",4925.6099999999997,64032.929999999993,59107.319999999992]
["Mark","Brantley","Mark.Brantley","00000022","2020-12-15","RSU",3641.71,47342.23,43700.520000000004]
["Oscar","Tanner","Oscar.Tanner","00000023","2020-12-15","NQSO",2751.11,35764.43,33013.32]
["Steve","Lansing","Steve.Lansing","00000024","2020-12-15","RSU",1258.68,16362.84,15104.16]
["Victor","McDonald","Victor.McDonald","00000025","2020-12-15","NQSO",4628.96,60176.480000000003,55547.520000000004]
["Yazier","Hernandez","Yazier.Hernandez","00000026","2020-12-15","RSU",1582.99,20578.87,18995.879999999997]