Exploring Excel 2016 Project
80 Marks = __/15%
RESTAURANT ANALYSIS:
Assume you are a restaurant director. You are because invadeing into a long-signal bond delay a poultry concourse and planning to dissipation a new subsidence ace to stock the chicken. If you invade into this bond, the poultry concourse agrees to agree a true remittance for a limited limit of age. You would affect to kindle sales for the definite produce producing chicken individual on the menu as well-mannered-mannered as any chicken individuals not parley a sales exploit commencement. Thus, some of this savings gain be passed on to the guests by placing these chicken individuals on sale.
TASKS:
Name the Lookup Board – 4 marks
a) Begin Excel and Public the Sales V?.xlsx improve agreed by your adherent. Secure you public and consummate the account assigned to you, inadequately you may not assent-to a gradation for this contrivance. Save As Sales_YourName (where YourName is replaced by your primitive and definite spectry).
b) Effect the Lookup Board operationprevarication the locomotive prevarication. Invade unexcited Prices in the B support for each of the Menu Items. Assign the spectry Worth to the rank containing menu individuals and worth.
Insert Functions and Formulas – 22 marks
You failure to collate the contrivanceed sales quantities to the explicit sales quantities aggravate a impure month limit. Notice that axioms on the ChickenSales operationprevarication in rank A15:E20 contains impure months of explicit sales quantities for each menu individual. Whereas, the rank A24:E29 contains the sales contrivanceions made for the corresponding impure months former to the begin of each month.
a) Effect the ChickenSales operationprevarication the locomotive prevarication. Wrap quotation in the rank A5:I5 to effect the support headings underneathstandable. Secure row heights and support widths are set so as to flaunt cell interruption.
b) In Cell B6 invade a formula to investigate the aggregate Designed Sales Quantity for this menu individual aggravate the impure months.
c) In Cell C6 invade a formula to investigate the aggregate Explicit Sales Quantity for this menu individual aggravate the impure months.
d) In Cell D6 invade a formula to investigate the mediocre Explicit Sales Quantity aggravate the impure months.
e) In Cell E6 invade a formula to investigate the worth of the menu individual by looking up the worth of the menu individual from delayin the Worth rank.
f) In Cell F6 invade a formula to investigate the Aggregate Produce on each menu individual established upon the Aggregate Qty Sold and the Worth of the menu individual.
g) In Cell G6 invade a formula to investigate the percentage of Aggregate Qty Designed sales that were explicitly sold (Total Qty Sold). For sample, perhaps 92% of the Aggregate Qty Designed was explicitly sold.
h) In Cell H6 invade use a close part in a formula to indicate which menu individual has the pre-eminent Aggregate Revenue. For the menu individual that has the pre-eminent produce invade the suffrage Best Individual and for all other menu demonstration a broad cell.
i) In Cell I6 invade a close part in a formula to indicate which individuals gain be put on sale direct in dispose to kindle sales. An individual should be put On Sale if its % of Projection is less than the Sale Commencement in Cell L6. An individual should so be put On Sale if its Sales Produce is the definite. Leave the cell broad for individuals that are not put on sale.
j) Representation the formulas and parts down their referring-to supports.
k) In Cell L9 invade a formula that flaunts the reckon of days between the Begin of the sale and the End of the sale. The Begin and End durations enjoy been agreed by the poultry concourse and are invadeed in Cells L7 and L8.
Determine Advance Payments – 8 marks
You gain failure a advance in dispose to dissipation a new subsidence ace to stock the poultry. You craving to effect quarterly cancelments (4 cancelments per year) toward your advance requital. You gain then failure to see how these cancelments gain modify established on the reckon of years to remunereprimand and modifying concern reprimands. You should beget one formula using Referring-to and Irresponsible Cell References and then representation the interruption of the one cell into all the other cells. Manually invadeing opposed formulas into each cell gain outcome in half marks.
a) Click on the Poultry Advance operationprevarication tab. Use a financial part in a formula in Cell C12 to investigate the advance cancelment equality absorbed the advance equality in Cell C6 and the annual concern reprimand in Cell B12. Use the mismisappropriate referring-to, adulterated, and/or irresponsible cell relations in the formula.
b) Representation the formula to Cells C13 and C14. The formula in C12 gain relation the B12 concern reprimand, but when you representation the formula to C13 the formula should qualify to relation the B13 concern reprimand. Similarly, for C14 referencing the B14 concern reprimand.
c) Representation the formula to Cells D12 and E12. The formula in C12 gain relation the C11 year, but when you representation the formula to D12 the formula should qualify to relation the D11 year. Similarly, for E12 referencing the E11 year.
d) Consummate representationing the formula to Cells D13:E14 and secure the concern reprimand cell relations and the year cell relations qualify accurately.
Set Improve Properties – 6 marks
You failure to set some details environing the operationbook.
a) In the File, Properties, invade a Indicate for this Workbook RestaurantName Sales (where RestaurantName is the spectry you enjoy selected for the restaurant).
b) In the Improve Properties, invade a Tag for this Workbook YourFirstandLastNames, Director (where YourFirstandLastNames is your primitive and definite spectrys and Director is your job indicate).
c) Underneath File, Properties invade your primitive and definite spectry as Author. You may failure to direct the File, Options for your spectry to show as Author.
Format Axioms – 18 marks
You failure to format the indicates and numeric axioms in the ChickenSales prevarication. In importation, you failure to congelate the support labels so that they do not scroll offscreen. You so failure to apportion occupied formatting to emphagreatness objurgates balance the mediocre objurgate.
a) Produce a spectry for this restaurant and a concourse logo (likeness improve). Inoculate your concourse logo (likeness improve) into the ChickenSales and PoultryLoan operationsheets and situate the top-suitable hole of the logo in cell A1. Secure the logo does not aggravatelap other accomplishing. Secure the logo is the corresponding greatness on twain operationsheets.
b) On the ChickenSales operationsheet, incorpoobjurgate and cinvade the ocean indicate Chicken Sales Analysis, athwart the Menu Individual board. Format the indicate in a larger font and increase the cell delay colour.
c) Move the duration cells (C4:D4) to H1:I1.
d) Apportion the “Currency” reckon format to the monetary objurgates in supports E and F.
e) Format the Avg Monthly Qty Sold objurgates delay one decimal situate.
f) Congelate support A so it does on scroll offscreen.
g) Apportion occupied formatting to objurgates in the Aggregate Produce support. When a cell’s objurgate is senior than the mediocre of the objurgates in the support the cell increases delay colour.
h) Qualify the sombre purple formatting on the support headings on the ChickenSales operationprevarication to some other colour(s) that improve subserve your concourse logo and concourse colours. Secure the cell interruption fit the cell and are evidently perceptible.
i) Click on the PoultryLoan operationsheet. You would affect to use the requital non-interference that keeps the quarterly cancelment equality underneathneath $2000, has the shortest signal, and the definite concern reprimand. Locate the ONE cell that meets these conditions and add a Comment assertion Winner! (or something of that genus).
Create Sparklines and Inoculate a Chart – 6 marks
You gain produce a chart comparing the aggregate produce for each menu individual, and you gain inoculate sparklines to flaunt trends aggravate a impure-month limit of age.
a) On the ChickenSales operationprevarication produce Sparklines (lines or supports) in Cells F15:F20 to flaunt impure-month trends for explicit sales for each menu individual. Demonstration the elevated subject-matter in each sparkline. Apportion a Sparkline Diction of your dainty.
b) Produce a Chart comparing the Aggregate Produce for each of the Menu Items. Select any one of Pie, Line, or Bar chart image.
c) Position the Chart moreover the Explicit Sales Quantities area and secure it does not direct gone-by support P. Apportion any Chart Diction to this Chart. Qualify the Chart Indicate to Chicken Sales Produce by Menu Item.
Sort and Refine the Axioms – 16 marks
To defend the uprightness of the first axioms, you representation the operationsheet. You gain then direct the axioms in the copies operationprevarication to a board, and apportion a board diction, genus and refine the axioms, and then flaunt aggregates.
a) Representation the ChickenSales prevarication and situate the copied prevarication at the end of the operationbook.
b) Remove the occupied formatting government on the ChickenSales (2) prevarication.
c) Direct the axioms in the Menu Individual area in the ChickenSales (2) prevarication to a board.
d) Genus the board by % of Projection in ascending dispose (if the genusing does not operation, you gain quiet get the marks for this investigation, as there is a glitch systematically associated delay this investigation)
e) Apportion a refine to flaunt the top 5 performers solely.
f) Flaunt a aggregate row. Add aggregates for supports B, C, and F.
g) On the ChickenSales operationsheet, produce a footer delay the improve spectry regulation on the left plane, your spectry in the courage, and the prevarication tab regulation on the suitable plane.
h) On the ChickenSales (2) operationsheet, apportion 0.5” (1.25 cm) left and suitable margins. Scale to fit on one page. Select Landscape orientation. Preview to secure all is set properly.
Save the operationbook. Close the operationbook and egress Excel. Submit the operationbook as directed by your professor.