Assistant Cost Accountant
- Assistant Cost Accountant – Allan Myers
- I mainly automated data manipulation processes for the Director of Costing via Power Query/Power BI, reducing the time he had to spend developing financial reports.
- Most of my projects were developing queries where you could drop a file into a folder, and then you could refresh a table (query) in a separate file with specific data that needed to be extracted from the source files. For example, I needed to develop a table that takes the revenue and book & burn data from each of the source files, and subtracts them in order to calculate the secured positions for the next 2 years. Each source file had a specific month and business unit (subsidiary), but I also needed to break the data down further to find the secured positions for each job type in a specific month for a specific BU. I developed a network of queries (one for revenues, book & burn, file details) that fine-tuned the data in each file through a series of filters specific to the source file format, and then I merged them into one large query where I could create calculated fields for the secured position values for one and two years into the future. For the revenues, not all of the files had summary tables, so I had to do a grouping function with the raw data in order to find the values. I had another project where I had to map out rainfall data by county in Power BI, so I had to use a combination of Power Query (M language) and DAX in order to create a similar drop and refresh system where you could drop source data from a specific .gov website, and then it would consolidate into a color-schemed weather map by county. This map also had a slider so you could adjust for months and years, plus another filter where you could get the average monthly data for an entire year. For specific counties, I made a 12-month moving average graph through Power BI/Query, using lists and index columns (including a grouped index column that would reset for each county – important for setting conditionals so that county-month data points with less than 12 months of history pull zero instead of data from another county).
- This internship allowed me to pull concepts from my data & analytics course and put it into action. When creating multiple tables in Power BI in order to set up specific visuals, you need to mind the cardinality before connecting them. For example, I had a table that listed all the job numbers and their specific attributes, so when connecting it to a table where a job appears multiple times (there was a table that updated monthly, showing month-to-date figures for each job on the WIP list), it would be a one-to-many relationship. Also when merging tables in Power Query, I had to mind certain concepts from SQL (inner joins, outer joins, etc.). A join that we did not go over in class was anti-joins, which I had to use when creating a table that essentially acted as a manual filter input (certain job #s had to be excluded).