Yes, but they cannot receive any payment for the work while they are a councillor or for twelve months afterwards.
Generally, this should be avoided except as a temporary expedient. Councils are also expected to hold regular councillor checks on financial records, and that would plainly have to be different councillors from the one acting as RFO. In addition, the council must appoint an independent internal auditor to carry out an investigation each year. No specific qualifications are required, but an internal auditor should be familiar with local council procedures and the legal framework, as well as having an understanding of financial matters.