Question

Create a worksheet to keep track of revenue collected and expenses done in conducting tour programs at different tourist places during 2004 to 2008. Format the numeric data in currency format, prepare year-wise columns for revenue and expenses for each tourist place and calculate the difference. The calculated difference may be negative; the format of negative balance may be red coloured. Use conditional formatting for higher and lower values of revenue and expenses. Align the entire text in the centre. The font of tourist place is Arial with 14 point while the font of year is Times Roman with 14 points.

Tourist Place	2004		2005		2006		2007		2008
	Rev	Exp	Rev	Exp	Rev	Exp	Rev	Exp	Rev	Exp
Manali	123	55	234	123	345	333	333	365	365	453
Kashmir	234	123	123	55	365	453	345	333	333	365
Shilong	345	333	333	365	123	55	234	123	456	233
Kerala	333	365	365	453	234	123	123	55	345	333

Answer 1

Tourist Place	2004			2005			2006			2007			2008
	Rev	Exp	Diff	Rev	Exp	Diff	Rev	Exp	Diff	Rev	Exp	Diff	Rev	Exp	Diff
Manali	123	55	=B3-C3	234	123	=E3-F3	345	333	=H3-I3	333	365	=K3-L3	365	453	=N3-O3
Kashmir	234	123	=B4-C4	123	55	=E4-F4	365	453	=H4-I4	345	333	K4-L4	333	365	=N4-O4
Shilong	345	333	=B5-C5	333	365	=E5-F5	123	55	=H5-I5	234	123	=K5-L5	456	233	=N5-O5
Kerala	333	365	=B6-C6	365	453	=E6-F6	234	123	=H6-I6	123	55	=K6-L6	345	333	=N6-O6

• A: Calculate the Difference
  • Click cell D3 and type =B3-C3. Press [Enter].
  • Use the AutoFill handle (bottom-right corner of cell D3) and drag it down to cell D6.
  • Repeat this subtraction formula logic for the remaining years in columns G, J, M, and P (i.e., =Revenue - Expenses).
• B: Align and Apply Fonts
  • Highlight the entire worksheet grid, go to the Home tab, and click the Centre Align button.
  • Highlight Column A (Tourist Places), change the font family to Arial and font size to 14.
  • Highlight Row 1 (Years), change the font family to Times New Roman and font size to 14.
• C: Format as Currency
  • Highlight all numerical cells containing values and formulas (B3:P6).
  • Right-click the selected area, choose Format Cells, go to the Number tab, and select Currency. Set decimal places to 0.
• D: Colour Negative Balances in Red
  • While still inside the Format Cells \(\rightarrow \) Currency dialogue box, look at the Negative numbers selection box.
  • Select the option that shows numbers in Red colour text with a minus sign or brackets, then click OK.
• E: Apply Conditional Formatting for High and Low Values
  • Select all the numeric data fields.
  • Go to the Home tab, click Conditional Formatting \(\rightarrow \) Colour Scales, and choose the Green-Yellow-Red Colour Scale.
  • Result: Excel will automatically shade the highest revenues/expenses in solid green and the lowest values in deep red.