Question 1

Your CEO, Mr Bennet, will be presenting the latest Rainbow Textiles business plan to Mr Ling in Singapore. This business plan will consist of a large amount of data and information that will need to be summarised in graphical or table format for easier analysis and interpretation. Mr Bennet has requested you to present this information for him in an Excel spreadsheet. He has asked that you display each item in a separate sheet within the same workbook.

Sheet 1:

In Sheet 1, create a simple table displaying the following profit figures. The figures are expressed in rands.

Previous year: Gross profit: 197,450,000

Net Profit: 98,725,000

This year: Gross Profit: 234,500,500

Net Profit: 117,250,250

Next year: Gross profit: 304,654,000

Net Profit: 152,327,000

Below this table, create a bar or column chart displaying the growth in annual profits (gross and net) over the past two years, as well as the projected growth for the following year. You will need to display the year and the amount of both gross profit and net profit in the chart. Label this sheet Profits.

Sheet 2:

In Sheet 2, first create a simple table displaying the following information. It represents the type and amount of fabric (in metres) sold for the current year by Rainbow Textiles. Make use of formulas and functions in the table to work out the total amount sold in the current year, and the percentage of each fabric sold.

Chiffon: 800,000

Cotton: 650,000

Crinkle de chine: 150,000

Georgette: 500,000

Linen: 725,000

Muslin: 400,000

Silk: 125,000

Then, below this table, create a pie chart displaying these percentages of each specific fabric sold in the current year. Label the sheet Fabrics sold.

Sheet 3:

In Sheet 3, first create a table displaying the following information. It represents Rainbow Textiles, and its top 10 competitors, as well as their price ranges (which is based on the retail price of the Formal wear fabric per metre).

Rainbow Textiles: R70.00 (South Africa)

Worthington Fabrics: R80.00 (USA)

SOL Limited: R70.00 (Germany)

Textile Variations Pty Ltd: R65:00 (Namibia)

Higher Brands: R90.00 (USA)

Bright Changes: R65.00 (United Kingdom)

Benton: R120 (USA)

G-Star 4: R75 (France)

Taipeng Cloth: R60 (Taiwan)

Lunga Latte: R85 (Italy)

Su Li Fen Fabrics: R100 (South Korea)

As you can see, you have been provided with the prices in Rands, but these should also be displayed in US dollars so that it is easier for Mr Ling to compare prices. Your table must indicate the exchange rate that you have applied for this calculation. Show your workings in your table, ensuring you make use of the Excel functions and formulas. Label the sheet Competition.

At the bottom of your table, calculate the average selling price between Rainbow Textiles and its competitors. Then, make use of the conditional formatting function to display those competitors who have a cheaper price than Rainbow Textiles (in red) and those who are more expensive (in green).

Once complete, use a filter to show Mr Ling only those companies based in Europe that are in direct competition with Jalan Fabrics.

Remember to implement the design tips outlined in the interactive video to make a visually pleasing spreadsheet.