Its a SpreadSheet Related Question,

All spreadsheets should be titled and contain a footer with the name ABC and the date.

You are required to open or download an existing spreadsheet for some of these tasks.

Johnsons is a wholesaler of surplus stocks, which they resell to small traders, either via the sales team or over the internet on EBid.

You are employed as an accounts clerk in Johnsons. The computer system has crashed and the back up will not load due to a technical problem.

The accountant has asked you to collate some figures into a spreadsheet to give an overview of the activity for the last year

Over the past year the monthly results have been as follows:

Sales

January 42,980

February 55,980

March 92,600

April 118,206

May 117,420

June 120,115

Expenses

January 15,390

February 23,602

March 28,750

April 35,060

May 37,420

June 38,790

Cost of sales

January 14,620 February 17,940 March 21,405

April 29,800 May 30,650 June 34,020

Task 1.1

Prepare a spreadsheet, showing all the figures displayed. Formulate cells for January to show gross profit and net profit then copy these formulas into the remaining cells. Gross profit is sales less cost of sales, and net profit is gross profit less expenses.

Task 1.2

Use formulae to total each column

Task 1.3

Gross profit margin is calculated as gross profit expressed as a percentage of sales and net profit margin as net profit expressed as a percentage of sales , and use formula to calculate these figures for each month (as a percentage rounded to two decimal places)

Task 1.4

Title this worksheet as Johnsons Monthly Figures for 2010 and save as worksheet JOHN 1. Copy this to a new worksheet and display as formula. Save this worksheet as JOHN2.

Task 1.5

Print out copies of JOHN1 & JOHN 2

Task 2

Open the EBid worksheet

Task 2.1

Open a new worksheet and copy the information from the EBID worksheet. Give this the title Ebid History using font size 16 for the title, centred on the page.

Format headers to bold and ensure column widths and row heights are suitable. Then use the spellchecker function to check and resolve any errors.

Task 2.2

Insert a row between books and collectables, type 'coins' in cell A7 and type '1910 shilling' in cell B7 . Input bids 1 to 12 of 5, 15, 75, 180, 200, 195, 215, 465, 320, 299, 450 and 445, respectively.

Task 2.3

Insert three new columns between columns B & C and label these "lowest bid", "average bid" and "highest bid", and enter the necessary formulae to calculate the additional columns.

Task 2.4

Change the format of all numerical cells to currency rounded to the nearest and use conditional formatting to change cell content to a red of the highest bid for the Special Brand computers category on the worksheet.

Task 2.5

Produce a chart to show the bid history of Special Brand computers and save this. Insert the line chart below the bid figures, ensure it is appropriately labelled and has a suitable title.

Task 2.6

Save worksheet as E-BID and print, ensuring the data and graph will print onto one sheet of A4 paper.

1c Gross profit margin is calculated as gross profit expressed as a percentage of sales and net profit marate these figures for each month (as a percentage rounded to two decimal places)

1 AUCTION TYPE ITEM BID 1 BID 2 BID 3 BID 4 BID 5 BID 6 BID 7 BID 8 BID 9 BID 10 BID 11 BID 12 2 Electronis 100 special brand computers E2,345.00 E3,500.00 E3,675.00 $4,000.00 $5,720.00 $6,795.00 18,200.00 $9,950.00 67,540.00 $4,250.00 19,360.00 $7,450.00 3 Art & music Signed collectors item album E1,000.00 $1,250.00 $4,230.00 $2, 100.00 E3,000.00 E3, 150.00 13,350.00 E3,500.00 $1,475.00 E1,545.00 2,390.00 $3,765.00 4 Fashion 50 designer dresses $2,500.00 1,225.00 $2,975.00 3,500.00 6,230.00 $4,707.00 $5,575.00 $6,375.00 $5,975.00 $4, 150.00 $2,250.00 $6,450.00 5 Books 10 signed 1st edition E12,500.00 $25,500.00 $75,750.00 $55,555.00 64,900.00 E427,575.00 192,400.00 $1,000, 100.00 $37,500.00 105,000.00 $79,599.00 199,999.00 6 Collectibles Limited edition figurine E25.00 $50.00 f100.00 $1,295.00 1950.00 $1,000.00 $1,425.00 E1, 100.00 $575.00 f.1,001.00 $1, 150.00 $1,430.00 7 Electronics 500 computer systems E4,400.00 $4,550.00 $4,700.00 $4,275.00 $5,400.00 $7,430.00 6,900.00 $7,200.00 65,760.00 $4,900.00 $7,425.00 $5,675.00 8 Games 500 eletronic games E10.00 E125.00 $75.00 E350.00 1225.00 E345.00 $475.00 $700.00 E424.00 190.00 1675.00 E701.00