Begin in the Documentation worksheet: enter your name and date $($use a function for the current

date$).(1$ mark$)$

$2.$ Copy the second worksheet and re$-$name it Consolidated Annual Sales. Move it to the right of the

Documentation worksheet. $(1)$

$3.$ In the Consolidated Annual Sales worksheet, delete all the numeric data under the headings Period $1,$

Period $2,$ Period $3$ and Period $4($C$5$:F$53).$

$4.$ In C$5$ use a $3$D reference to calculate the sales for each product for all locations. Copy the formula to

F$5,$ and down to F$53.(2)$

$5.$ Group Consolidated Annual Sales and the $4$ locations worksheets. In cell G$5$ create a formula to

calculate the Annual Sales Total for all months and autofill the formula. $(2)$

$6.$ Ungroup the sheets. In the Consolidated Annual Sales worksheet, in cell I$5,$ insert an external link to

Test $1$ Part $2$ V#### Start File.xlsx$.$ Make sure the Text to Display makes sense to users. $(3)$

$7.$ Create a new worksheet and name it Year $3$ Projections. Move it to the left of the INTEGRITY CHECK

worksheet. $($if required$).(1)$

$8.$ In the Consolidated Annual Sales worksheet, in cell I$7,$ create a hyperlink to link within your workbook

to A$1$ in the Year $3$ Projections worksheet. Make sure the Text to Display makes sense to users. $(2)$

$9.$ In the Documentation worksheet, in cell B$10$ create an email link to the administrator $-$ use

Admin@gearup.com to ask any questions regarding this workbook. Fill in the necessary details. $($Email

Address, Text to Display, Subject and Screentip.$)$ Increase the font size to $14.(3)$

$10.$ In Year $3$ Projections, in cell A$3$ type Year $3$ Projections and make it a Title Style.

$11.$ In Cell A$4$ type Year $1$ Sales. In cell A$5$ create a formula that will reference the Overall Annual Sales in

the Consolidated Annual Sales worksheet $($G$54),$ so the company can compare once the figures are in

for Year $2.$ Apply accounting number style. Widen the column to $16\mathrm{.}5$ points. $(2)$

$12.$ Select the Documentation worksheet, SAVE and upload it to the drop box. $(1)$

$13.$ Finally, save the workbook as a template file with the name Your Condor ID$-$Template into your exam

folder. In the template file delete all the financial data in the cells C$5$:F$53$ of the $4$ location worksheets

to prepare it for Year $3$ data. Select the Document worksheet and save the template file once again

and upload it to your drop box. $(4)$