Using the spreadsheet provided, edit the cells to include the following controls:

1. Annual fee cannot exceed 8% of purchase cost.
2. Training costs cannot exceed $12,000 in year 0 and $3,000 thereafter.

(Enter the annual training costs in cell B10 and enter initial year 0 training costs in cell D10).

1. Annual savings due to efficiency cannot exceed $5,000, but must be zero in year 0.
2. Cell C2 (highlighted in yellow to the right of the word Answer) must force entry of either YES or NO no other values can be accepted.

(hint: use cells A30 and A31 as the source for a dropdown list).

1. Your name must appear in cell C3, and the cell must ensure that any name entered cannot exceed 30 characters total.
2. Cell B8 (annual fee) must NOT permit any alphabetic text.
3. The reduction in risk due to the security investment (cell B18) must range between 2% and 8%, inclusive.
4. Lock the spreadsheet so that data can only be entered in these cells (all highlighted in yellow in the spreadsheet):
5. C2
6. C3
7. B8
8. B10
9. B12
10. B18
11. D10
12. A30
13. A31

\begin{tabular}{|c|c|c|c|c|c|c|c|c|c|c|} \hlineA & A & B & C & D & E & F & G & H & 1 & J \\ \hline 1 & & Request: & \multicolumn{8}{|c|}{ Should we invest $100,000 in the proposed security solution to reduce the risk of an issue below 10% ? } \\ \hline 2 & & Answer: & & & & & & & & \\ \hline 3 & & Your name: & & & & & & & & \\ \hline \multicolumn{11}{|l|}{4} \\ \hline 5 & & & & \multicolumn{2}{|r|}{ Year } & & & & \multirow{2}{*}{\multicolumn{2}{|c|}{5 Total }} \\ \hline 6 & & & & 0 & 1 & 2 & 3 & 4 & & \\ \hline 7 & Purchase Cost (initial) & & & $100,000 & & & & & & $100,000 \\ \hline 8 & Annual fee & $0 & & & $0 & $0 & $0 & $0 & $0 & $0 \\ \hline \multicolumn{11}{|c|}{9} \\ \hline 10 & Training costs & $0 & & $0 & $0 & $0 & $0 & $0 & $0 & $0 \\ \hline \multicolumn{11}{|l|}{11} \\ \hline 12 & Savings due to efficiency & $0 & & $0 & $0 & $0 & $0 & $0 & $0 & $0 \\ \hline \multicolumn{11}{|l|}{13} \\ \hline 14 & Reduction in threat: & & & & & & & & & \\ \hline 15 & Impact & $500,000 & & & & & & & & \\ \hline 16 & Current risk & 10% & & & & & & & & \\ \hline 17 & Expected loss & $50,000 & & & & & & & & \\ \hline 18 & New risk & 4.0% & & & & & & & & \\ \hline 19 & New expected loss & $20,000 & & & & & & & & \\ \hline 20 & Benefit & $30,000 & annually & $0 & $30,000 & $30,000 & $30,000 & $30,000 & $30,000 & $150,000 \\ \hline \multicolumn{11}{|l|}{21} \\ \hline 22 & Total Cash Outflows & & & $100,000 & $0 & $0 & $0 & $0 & $0 & $100,000 \\ \hline 23 & Total Benefits & & & $0 & $30,000 & $30,000 & $30,000 & $30,000 & $30,000 & $150,000 \\ \hline 24 & Net Benefits & & & $100,000 & $30,000 & $30,000 & $30,000 & $30,000 & $30,000 & $50,000 \\ \hline 25 & Discount rate & 10% & & & & & & & & \\ \hline 26 & Discounted value & & & $100,000 & $27,273 & $24,793 & $22,539 & $20,490 & $18,628 & $13,724 \\ \hline 27 & NPV & $13,723.60 & & & & & & & & \\ \hline 28 & IRR & 15% & & & & & & & & \\ \hline \end{tabular}