Difference between revisions of "Excel Practice Problems"
Jump to navigation
Jump to search
(→List of Problem Types) |
(→List of Problem Types) |
||
| Line 78: | Line 78: | ||
'''Latest Update''': Feb 19, 2023 | '''Latest Update''': Feb 19, 2023 | ||
| + | |||
| + | Problem types <span style="background-color: lightblue;">highlighted in blue</span> are also available as ''[https://battleactsmain.ca/FC.php?selectString=**&filter=both&sortOrder=natural&colorFlag=allFlag&colorStatus=allStatus&priority=A&prefix=all&suffix=all§ion=all&subSection=all&subsetFlag=CP&examRep=all&examYear=all&examTerm=all&quizNum=0&select07=0&select08=0&select09=0 web-based problems]''. | ||
{| class='wikitable' | {| class='wikitable' | ||
| Line 85: | Line 87: | ||
|- | |- | ||
|| [[OSFI.MCT-IFRS]] | || [[OSFI.MCT-IFRS]] | ||
| − | || MCT '''Ratio''' (given all components) <br> MCT '''Capital Available''' <br> MCT '''Capital Required for Insurance Risk''' (TOTAL) <br> MCT Capital Required for Insurance Risk (Earthquakes) <br> MCT Capital Required for Insurance Risk (Deduction from Capital Available for Unregistered Reinsurance) <br> MCT Capital Required for Insurance Risk (Margin Required for Unregistered Reinsurance) <br> MCT '''Capital Required for Market Risk''' (TOTAL) <br> MCT Capital Required for Market Risk (Interest Rate Risk - easy version) <br> MCT Capital Required for Market Risk (Interest Rate Risk - harder version) <br> MCT Capital Required for Market Risk (Duration of Assets & Liabilities) <br> MCT '''Capital Required for Operational Risk''' (TOTAL) <br> MCT '''Capital Required for Credit Risk''' (TOTAL) | + | || <span style="background-color: lightblue;">MCT '''Ratio''' (given all components)</span> <br> MCT '''Capital Available''' (TOTAL) <br> <span style="background-color: lightblue;">MCT Capital Available (Deduction due to Capital Composition)</span> <br> MCT '''Capital Required for Insurance Risk''' (TOTAL) <br> <span style="background-color: lightblue;">MCT Capital Required for Insurance Risk (Earthquakes)</span> <br> MCT Capital Required for Insurance Risk (Deduction from Capital Available for Unregistered Reinsurance) <br> MCT Capital Required for Insurance Risk (Margin Required for Unregistered Reinsurance) <br> <span style="background-color: lightblue;">MCT '''Capital Required for Market Risk'''</span> (TOTAL) <br> <span style="background-color: lightblue;">MCT Capital Required for Market Risk (Interest Rate Risk - easy version)</span> <br> <span style="background-color: lightblue;">MCT Capital Required for Market Risk (Interest Rate Risk - harder version)</span> <br> MCT Capital Required for Market Risk (Duration of Assets & Liabilities) <br> <span style="background-color: lightblue;">MCT '''Capital Required for Operational Risk''' (TOTAL)</span> <br> MCT '''Capital Required for Credit Risk''' (TOTAL) |
|| Excel files coming... | || Excel files coming... | ||
| Line 91: | Line 93: | ||
|- | |- | ||
|| [[CCIR.Instructions]] | || [[CCIR.Instructions]] | ||
| − | || '''Calculating Equity''' <br> • using Fundamental Accounting Equation <br> • using prior year's equity and current year's income <br> • using Statement of Financial Position - Liabilities & Equity, page 20.11) <br> '''Other Financial Statement Quantities''' <br> • Net Income / Investment Income / Investment Return <br> • Adjusted equity / Investment Yield / ROE (Return on Equity) <br> • Claims Development as a % of Adjusted Equity <br> • Agents and Brokers Balances Due from Subs and Associates as a % of Adjusted Eq. <br> • Insurance Service Result <br> • Excess (Deficiency) Amount | + | || '''Calculating Equity''' <br> • <span style="background-color: lightblue;">using Fundamental Accounting Equation</span> <br> • using prior year's equity and current year's income <br> • using Statement of Financial Position - Liabilities & Equity, page 20.11) <br> '''Other Financial Statement Quantities''' <br> • <span style="background-color: lightblue;">Net Income / Investment Income / Investment Return</span> <br> • <span style="background-color: lightblue;">Adjusted equity / Investment Yield / ROE (Return on Equity)</span> <br> • <span style="background-color: lightblue;">Claims Development as a % of Adjusted Equity</span> <br> • <span style="background-color: lightblue;">Agents and Brokers Balances Due from Subs and Associates as a % of Adjusted Eq.</span> <br> • <span style="background-color: lightblue;">Insurance Service Result / Total Insurance Revenue / Excess (Deficiency) using page 60.45</span> <br> • <span style="background-color: lightblue;">Excess (Deficiency) Amount (using page 60.35)</span> |
|| Excel files coming... | || Excel files coming... | ||
|- | |- | ||
|| [[MSA.Ratios]] | || [[MSA.Ratios]] | ||
| − | || Calculate MSA Ratios (must also know acceptable ranges) | + | || <span style="background-color: lightblue;">Calculate MSA Ratios (must also know acceptable ranges)</span> |
|| Excel files coming... | || Excel files coming... | ||
| Line 126: | Line 128: | ||
|- | |- | ||
|| [[BCAR.Cdn2018]] | || [[BCAR.Cdn2018]] | ||
| − | || Determine the A.M. Best rating <br> Calculate B5 for BCAR for each VaR level | + | || <span style="background-color: lightblue;">Determine the A.M. Best rating</span> <br> <span style="background-color: lightblue;">Calculate B5 for BCAR for each VaR level</span> |
|| Excel files coming... | || Excel files coming... | ||
|- | |- | ||
|| [[Frei.RskTrans]] | || [[Frei.RskTrans]] | ||
| − | || Use the 10-10 rule to assess risk transfer | + | || <span style="background-color: lightblue;">Use the 10-10 rule to assess risk transfer</span> |
|| Excel files coming... | || Excel files coming... | ||
|- | |- | ||
|| [[Odo.FinReg]] | || [[Odo.FinReg]] | ||
| − | || Regulator action under Solvency II <br> Value of commuted claims | + | || <span style="background-color: lightblue;">Regulator action under Solvency II</span> <br> Value of commuted claims |
| + | || [https://www.battleactsmain.ca/Excel/POWER_PACK/Solvency_II_1.xlsx <span style="color: white; font-size: 12px; background-color: blue; border: solid; border-width: 2px; border-radius: 0px; border-color: blue; padding: 5px 15px 5px 15px; margin: 0px;">'''Solvency II: Set 1'''</span>] | ||
| + | |||
| + | [https://www.battleactsmain.ca/Excel/POWER_PACK/Solvency_II_1_(solutions).xlsx <span style="color: white; font-size: 12px; background-color: chocolate; border: solid; border-width: 2px; border-radius: 0px; border-color: blue; padding: 5px 15px 5px 15px; margin: 0px;">'''Solvency II: Set 1 (Solutions)'''</span>] | ||
| + | |||
| + | |- | ||
| + | || [[PACICC.Comp]] | ||
| + | || Calculate policyholder compensation <br> Calculate insurer assessment | ||
|| Excel files coming... | || Excel files coming... | ||
|- | |- | ||
| − | || . | + | || [[CIA.Taxes]] |
| − | || | + | || Calculate estimated effect of discounting the asset for future income taxes |
| − | || Excel files coming... | + | || Excel files coming... |
|} | |} | ||
Revision as of 22:23, 19 February 2023
Contents
BattleActs Practice Exams (2022-Fall)
Useful Information:
- The CAS posted a NEW Excel file with sample IFRS 17 questions on the Exam 6-Canada page. This is not listed as being part of the required readings but it looks like something you should definitely look at. You can download it from this page: (You have to scroll down to find it.)
Notes:
- If you find any typos or errors, please post to the PRACTICE EXAM category of the forum here: Forum
- For the IFRS 17 Bonus Question, you will need to refer to the source text for the CIA.IFRS17-LRC.
- For all calculation problems in the practice exam (except #14 and #18) you can change the input values and the answers will automatically update. In other words, you can create your own practice problems.
| Excel Problems | Answer File |
| BA Practice Exam 1 (2022-Fall) | BA Practice Exam 1 (2022-Fall) (with answers) |
| Bonus Question on IFRS 17 | Answer in same file as question |
BattleActs Practice Exams for 2022-Spring (Many Problems are Now Outdated)
|
| Excel Problems | Examiner's Report |
| BA Practice Exam 1 | BA Practice Exam 1 (with answers) |
| BA Practice Exam 2 | BA Practice Exam 2 (with answers) |
Past CAS Exams (Many Problems are Now Outdated)
| Excel Problems | Examiner's Report |
| Exam 6C: 2019.Fall | 2019.Fall Examiner's Report |
| Exam 6C: 2019.Spring | 2019.Spring Examiner's Report |
| Exam 6C: 2018.Fall | 2018.Fall Examiner's Report |
List of Problem Types
Latest Update: Feb 19, 2023
Problem types highlighted in blue are also available as web-based problems.
| Wiki Link | Problem Type | Practice Problems |
|---|---|---|
| OSFI.MCT-IFRS | MCT Ratio (given all components) MCT Capital Available (TOTAL) MCT Capital Available (Deduction due to Capital Composition) MCT Capital Required for Insurance Risk (TOTAL) MCT Capital Required for Insurance Risk (Earthquakes) MCT Capital Required for Insurance Risk (Deduction from Capital Available for Unregistered Reinsurance) MCT Capital Required for Insurance Risk (Margin Required for Unregistered Reinsurance) MCT Capital Required for Market Risk (TOTAL) MCT Capital Required for Market Risk (Interest Rate Risk - easy version) MCT Capital Required for Market Risk (Interest Rate Risk - harder version) MCT Capital Required for Market Risk (Duration of Assets & Liabilities) MCT Capital Required for Operational Risk (TOTAL) MCT Capital Required for Credit Risk (TOTAL) |
Excel files coming... |
| CCIR.Instructions | Calculating Equity • using Fundamental Accounting Equation • using prior year's equity and current year's income • using Statement of Financial Position - Liabilities & Equity, page 20.11) Other Financial Statement Quantities • Net Income / Investment Income / Investment Return • Adjusted equity / Investment Yield / ROE (Return on Equity) • Claims Development as a % of Adjusted Equity • Agents and Brokers Balances Due from Subs and Associates as a % of Adjusted Eq. • Insurance Service Result / Total Insurance Revenue / Excess (Deficiency) using page 60.45 • Excess (Deficiency) Amount (using page 60.35) |
Excel files coming... |
| MSA.Ratios | Calculate MSA Ratios (must also know acceptable ranges) | Excel files coming... |
| Dutil.FA | RSP Participation Ratio Company Loss Ratio • On company's share of RSP • Total including cessions to RSP • Total assuming no cession to RSP |
Excel files coming... |
| CIA.FCT-1 | Determine whether insurer is in satisfactory financial condition | Excel files coming... |
| CIA.IFRS17-LRC | Calculate the CSM using coverage units Calculate LRC under PAA |
Excel files coming... |
| CIA.IFRS17-DR | Calculate the IFRS 17 discount rate using a bottom-up and top-down approach Calculate the FCFs for issue years |
Excel files coming... |
| CIA.IFRS17-PAA | Assess PAA eligibility for calculating LRC | Excel files coming... |
| BCAR.Cdn2018 | Determine the A.M. Best rating Calculate B5 for BCAR for each VaR level |
Excel files coming... |
| Frei.RskTrans | Use the 10-10 rule to assess risk transfer | Excel files coming... |
| Odo.FinReg | Regulator action under Solvency II Value of commuted claims |
Solvency II: Set 1 |
| PACICC.Comp | Calculate policyholder compensation Calculate insurer assessment |
Excel files coming... |
| CIA.Taxes | Calculate estimated effect of discounting the asset for future income taxes | Excel files coming... |
