Exploring Excel 2016 Project
80 Marks = __/15%
Assume you are a restaurant superintendent. You are because penetrateing into a long-tidings conformity delay a poultry crew and planning to dissipation a new cooling part to accumulation the chicken. If you penetrebuke into this conformity, the poultry crew agrees to get a existing abatement for a ascertained limit of epoch. You would approve to exasperebuke sales for the definite wealth producing chicken part on the menu as courteous as any chicken parts not discourse a sales exploit commencement. Thus, some of this savings succeed be passed on to the guests by placing these chicken parts on sale.
Name the Lookup Consultation – 4 marks
a) Set-out Excel and Unconcealed the Sales V?.xlsx improve getd by your zealot. Determine you unconcealed and perfect the account assigned to you, otherwise you may not hold a gait for this contrivance. Save As Sales_YourName (where YourName is replaced by your primeval and definite spectry).
b) Frame the Lookup Consultation resultprevarication the free prevarication. Penetrebuke sound Prices in the B shaft for each of the Menu Items. Assign the spectry Charge to the class containing menu parts and charge.
Insert Functions and Formulas – 22 marks
You insufficiency to collate the contrivanceed sales quantities to the real sales quantities aggravate a disgusting month limit. Notice that postulates on the ChickenSales resultprevarication in class A15:E20 contains disgusting months of real sales quantities for each menu part. Whereas, the class A24:E29 contains the sales contrivanceions made for the identical disgusting months previous to the set-out of each month.
a) Frame the ChickenSales resultprevarication the free prevarication. Wrap extract in the class A5:I5 to frame the shaft headings underneathstandable. Determine row heights and shaft widths are set so as to flaunt cell satisfieds.
b) In Cell B6 penetrebuke a formula to compute the sum Contemplated Sales Quantity for this menu part aggravate the disgusting months.
c) In Cell C6 penetrebuke a formula to compute the sum Real Sales Quantity for this menu part aggravate the disgusting months.
d) In Cell D6 penetrebuke a formula to compute the middle Real Sales Quantity aggravate the disgusting months.
e) In Cell E6 penetrebuke a formula to compute the charge of the menu part by looking up the charge of the menu part from delayin the Charge class.
f) In Cell F6 penetrebuke a formula to compute the Sum Wealth on each menu part fixed upon the Sum Qty Sold and the Charge of the menu part.
g) In Cell G6 penetrebuke a formula to compute the percentage of Sum Qty Contemplated sales that were really sold (Total Qty Sold). For in, mayhap 92% of the Sum Qty Contemplated was really sold.
h) In Cell H6 penetrebuke use a close administration in a formula to enumerebuke which menu part has the chief Sum Revenue. For the menu part that has the chief wealth penetrebuke the tone Best Part and for all other menu semblance a bare cell.
i) In Cell I6 penetrebuke a close administration in a formula to enumerebuke which parts succeed be put on sale contiguous in adjust to exasperebuke sales. An part should be put On Sale if its % of Projection is less than the Sale Commencement in Cell L6. An part should so be put On Sale if its Sales Wealth is the definite. Leave the cell bare for parts that are not put on sale.
j) Portraiture the formulas and administrations down their not-despotic shafts.
k) In Cell L9 penetrebuke a formula that flaunts the estimate of days between the Set-out of the sale and the End of the sale. The Set-out and End epochs keep been getd by the poultry crew and are penetrateed in Cells L7 and L8.
Determine Mortgage Payments – 8 marks
You succeed insufficiency a mortgage in adjust to dissipation a new cooling part to accumulation the poultry. You hope to frame quarterly reimbursements (4 reimbursements per year) internal your mortgage retribution. You succeed then deficiency to see how these reimbursements succeed alter fixed on the estimate of years to reward and altering share rebukes. You should produce one formula using Not-despotic and Despotic Cell References and then portraiture the satisfieds of the one cell into all the other cells. Manually penetrateing irnot-despotic formulas into each cell succeed outcome in half marks.
a) Click on the Poultry Mortgage resultprevarication tab. Use a financial administration in a formula in Cell C12 to compute the mortgage reimbursement aggregateity ardent the mortgage aggregateity in Cell C6 and the annual share rebuke in Cell B12. Use the alienate not-absolute, modified, and/or despotic cell regards in the formula.
b) Portraiture the formula to Cells C13 and C14. The formula in C12 succeed regard the B12 share rebuke, but when you portraiture the formula to C13 the formula should modify to regard the B13 share rebuke. Similarly, for C14 referencing the B14 share rebuke.
c) Portraiture the formula to Cells D12 and E12. The formula in C12 succeed regard the C11 year, but when you portraiture the formula to D12 the formula should modify to regard the D11 year. Similarly, for E12 referencing the E11 year.
d) Perfect portraitureing the formula to Cells D13:E14 and determine the share rebuke cell regards and the year cell regards modify precisely.
Set Improve Properties – 6 marks
You insufficiency to set some details encircling the resultbook.
a) In the File, Properties, penetrebuke a Fashion for this Workbook RestaurantName Sales (where RestaurantName is the spectry you keep separated for the restaurant).
b) In the Improve Properties, penetrebuke a Tag for this Workbook YourFirstandLastNames, Superintendent (where YourFirstandLastNames is your primeval and definite spectrys and Superintendent is your job fashion).
c) Underneath File, Properties penetrebuke your primeval and definite spectry as Author. You may insufficiency to adjust the File, Options for your spectry to answer as Author.
Format Postulates – 18 marks
You insufficiency to format the fashions and numeric postulates in the ChickenSales prevarication. In observation, you deficiency to benumb the shaft labels so that they do not scroll offscreen. You so deficiency to devote impeded formatting to emphadimension appreciates aggravate the middle appreciate.
a) Invent a spectry for this restaurant and a crew logo (observation improve). Insinuate your crew logo (observation improve) into the ChickenSales and PoultryLoan resultsheets and locate the top-upexact cavity of the logo in cell A1. Determine the logo does not aggravatelap other satisfied. Determine the logo is the identical dimension on twain resultsheets.
b) On the ChickenSales resultsheet, add and cpenetrebuke the deep fashion Chicken Sales Analysis, counter the Menu Part consultation. Format the fashion in a larger font and replenish the cell delay colour.
c) Move the epoch cells (C4:D4) to H1:I1.
d) Devote the “Currency” estimate format to the monetary appreciates in shafts E and F.
e) Format the Avg Monthly Qty Sold appreciates delay one decimal locate.
f) Benumb shaft A so it does on scroll offscreen.
g) Devote impeded formatting to appreciates in the Sum Wealth shaft. When a cell’s appreciate is greater than the middle of the appreciates in the shaft the cell replenishs delay colour.
h) Modify the ebon purple formatting on the shaft headings on the ChickenSales resultprevarication to some other colour(s) that reform assist your crew logo and crew colours. Determine the cell satisfieds fit the cell and are lucidly palpable.
i) Click on the PoultryLoan resultsheet. You would approve to use the retribution discretion that keeps the quarterly reimbursement aggregateity underneathneath $2000, has the shortest tidings, and the definite share rebuke. Locate the ONE cell that meets these provisions and add a Comment dictum Winner! (or celebrity of that character).
Create Sparklines and Insinuate a Chart – 6 marks
You succeed invent a chart comparing the sum wealth for each menu part, and you succeed insinuate sparklines to flaunt trends aggravate a disgusting-month limit of epoch.
a) On the ChickenSales resultprevarication invent Sparklines (lines or shafts) in Cells F15:F20 to flaunt disgusting-month trends for real sales for each menu part. Semblance the violent summit in each sparkline. Devote a Sparkline Fashion of your select.
b) Invent a Chart comparing the Sum Wealth for each of the Menu Items. Select any one of Pie, Line, or Bar chart sign.
c) Position the Chart near the Real Sales Quantities area and determine it does not augment elapsed shaft P. Devote any Chart Fashion to this Chart. Modify the Chart Fashion to Chicken Sales Wealth by Menu Item.
Sort and Clarify the Postulates – 16 marks
To protect the entireness of the initiatory postulates, you portraiture the resultsheet. You succeed then transform the postulates in the copies resultprevarication to a consultation, and devote a consultation fashion, character and clarify the postulates, and then flaunt sums.
a) Portraiture the ChickenSales prevarication and locate the copied prevarication at the end of the resultbook.
b) Remove the impeded formatting administration on the ChickenSales (2) prevarication.
c) Transform the postulates in the Menu Part area in the ChickenSales (2) prevarication to a consultation.
d) Character the consultation by % of Projection in ascending adjust (if the charactering does not result, you succeed peaceful get the marks for this scrutiny, as there is a glitch invariably associated delay this scrutiny)
e) Devote a clarify to flaunt the top 5 performers solely.
f) Flaunt a sum row. Add sums for shafts B, C, and F.
g) On the ChickenSales resultsheet, invent a footer delay the improve spectry enactment on the left party, your spectry in the kernel, and the prevarication tab enactment on the upupexact party.
h) On the ChickenSales (2) resultsheet, devote 0.5” (1.25 cm) left and upupexact margins. Scale to fit on one page. Select Landscape orientation. Preview to determine all is set right.
Save the resultbook. Close the resultbook and departure Excel. Submit the resultbook as directed by your pedagogue.