Automate formula and insert row for foreign exchange calculation

Question

Hi, I have a number of worksheets which are all in the same format/layout - see attached sample input sheet. These documents are in local currencies so need to be converted to USD. All the 'brought forward' values (rows 15,16,24,25,34,35) need to be converted using the exchange rate at 31.12.2023. The other inputs need to be converted using the exchange rate at 31.03.24, and cells with formulas to add/sum need to remain functioning formulas - is that possible? Furthermore, is there a way to insert rows (ie between row 20 -21, 29 - 30, 39 - 40 and 46 - 47) which can calculate the foreign exchange movement (cell b21 - sum(b20:b17).

Would it be possible to do this in a workflow which will allow use of the join tool to create an overall summary schedule all in the same currency (USD)? Thank you

CIT1 - UK co.xlsx

Raj · Answer

@NTurnbull share expected output 
achieving this will be possible using alteryx.

jdminton · Answer

The short answer is yes, but I think you are thinking Alteryx works like Excel. If the data is stored in different sheets, the most likely useful scenario would be to use Alteryx to bring all of the information into one data table, perform the needed calculations, and then output into the desired format. Alteryx does not present formulas in the output like Excel, but if you are needing the formula for audit purposes, you can provide the workflow as evidence. You might need to provide a different question after considering this.