57
EXL
5
your TASK
The staff at The Sub
Hub (The Hub's build-
your-own sandwich
shop) needs to know
how much food has
been sold and disposed
of during the week.
Using Excel, you will
key data that shows
the units and value
of menu ingredients
used. You will compare
an inventory value at
the start of the week
to a value at the end.
You have also been
given the amount sold
based on sales receipt
data. You will use a
formula to calculate
the amount of food
disposed of each week.
the SKILLS
· Subtract cells
· Shade cells
S
uccessful businesses have a good handle on their merchandise inventory.
Food service businesses especially need to maintain adequate inventory
control methods. Since the inventory of a restaurant is typically the largest
asset,weeklyreportingisdonetomaintainaccuratefigures.Thisinformation
becomesessentialwhenfoodmanagersorderinventoryforthenextweek.
1. BeginallformulasinExcelwithanequalsign.
2. Use cell references instead of values so that spreadsheets can later be used to
create what-if scenarios.
3. Readthroughallinstructionspriortocompletingtheproject.
1. UsingMicrosoftExcel,createanewblankworkbook.
2. SavetheworkbookasProjectEXL-5SubHubInventoryinyour"Excel"folder
under "The Hub" folder.
3. KeythedataasitappearsinFigureEXL-5.1intothespreadsheet.Unless
otherwisenoted,thefontshouldbesettoArial10point.
4. UseAutoFittoadjustthewidthofthecolumnssothatdatafitsappropriately.
5. EnteraformulaincellF4tocalculatetheunitsoffood"Used."Thisiscalculated
bysubtractingthe"EndingInventory"unitsfromthe"BeginningInventory"
units.
Hint:
=B4-D4
6. UsetheAutoFillfeaturetocopytheformuladowntocellsF5-F12.
7. EnteraformulaincellH4tocalculatetheunitsoffood"Disposed."Thisis
calculatedbysubtractingthe"Sold"unitsfromthe"Used"units.
Hint:
=F4-G4.
8. UsetheAutoFillfeaturetocopytheformuladowntocellsH5-H12.
9. ShadecellsF4throughF12andH4throughH12usinglightgrayfillcolor.
10. Display formulas in your spreadsheet by using Ctrl +` to check for accuracy.
11. Carefully proofread your work for accuracy and format.
12. Resavethefile.
13. SetthePrintAreatoincludeallcellscontainingdatainthespreadsheet.
Sub Hub Weekly Inventory
For Evaluation Purposes Only