Technical Business Analyst with 15 years professional experience delivering deceptively simple solutions to complex business reporting and automation solutions utilising Excel and VBA.
I am degree qualified in finance and have utilised Excel to build numerous financial reporting and forecasting solutions for my previous employer. These have included:
1. Automated balanced scorecard reports - linked data from back-end of accounting, POS, and payroll solutions to create complex financial metrics (e.g. HR spend / FTE)
2. Financial reporting suite - linking data from back end accounting package to create a dynamic financial reporting suite.
Looking at the sheet you provided I can easily populate the actual forecast as required. What I did:
Created a sheet called "setup" cell B3 = JL, B4 = RL, C3 = Johnson, C4 = Reemiah
On the sales forecast actual tab I added I set cell P2 to be Actual Margin
Defined a number of names as follows:
dates ='Core Data TARGETS'!$A$2:$A$53
headings ='Core Data TARGETS'!$A$1:$K$1
pMap =Settings!$B$2:$C$4
Formula for sales forecast actual cell B7
=IFERROR(INDEX('Core Data TARGETS'!$A$2:$K$53,MATCH(D$5,dates,FALSE),MATCH(VLOOKUP(RIGHT($A11, LEN($A11) - SEARCH(" ",$A11)),pMap,2,FALSE)&$P$2,headings,FALSE)),"")
This formula will also work for the Forecast Budget tab if you simply change the P2 value on that sheet to Margin Target
I would love that chance to help you out - please let me know if you would like to chat further!