Problem
Use formulae wherever calculations are required. No marks will be given for figures typed into the sheet where a formula should have been used. For optimal marks, use absolute cell references where appropriate.
A. Download the Excel document "Widget Orders" from Brightspace. The file shows details of orders received by the Widget Retail Company. Each order is for a quantity of widgets as detailed in the Order Quantity column. The Delivery Charge (in Euro) is also recorded for each order.
B. In the "Order Value" column, insert a formula to calculate the value of each order (Order Quantity multiplied by Unit Price).
C. Insert a formula in the "Gross Charge" column (Order Value plus Delivery Charge).
D. In the "VAT" column, insert a formula to calculate the VAT on each order (Gross Charge multiplied by VAT Rate).
E. In the "Total" column, insert a formula to calculate Gross Charge plus VAT.
F. In row 13, add a formula to calculate the average for each column of figures.
G. In row 14, add formula to calculate the sum for each column of figures.
H. In the "Percent of Sum" column, insert a formula to calculate the percentage of the sum (G14) which each order total represents.
I. Ensure all amounts are in currency format (€) or percentage format as appropriate. Percentages should be shown to one decimal place.
J. Centre all column headings (in row 1) and reduce the font size to 8. Make the column widths equal for columns "A" to "H".
K. Insert a new row at the top of the spreadsheet. Insert the title "Widget Retail Company" in this row so that it is merged and centred across columns "A" to "H". Change the fill colour (cell background colour) for this title to light green.
L. Insert a column chart with the 'Order No.' details on the horizontal axis and columns representing the figures in the "Total" column. Add suitable Axis Titles and Data Labels.
M. Save the File as 'Widget Orders Completed' on your home. Drive and upload it to the assignment link on Brightspace.