80.2K

Verified Solution

Link Copied!

In this final excel assignment you will develop a bar inventory sheet for your restaurant.

You will enter data and complete formulas for necessary calculations.

Format cells, columns and rows as needed to display information correctly.

When you are complete, upload the final excel workbook to the assignment link.

$1.$ Begin with a Blank Spreadsheet.

$2.$ Create a column for the $$Name of liquor$.$ Bold the label.

$3.$ Create a column and label it $$Pack Size$.$ Bold the label.

$4.$ Create a column for the $$bottle size$.$ Bold the label.

$5.$ Create a column and label it $$Bottle Cost$.$ Bold the label.

$6.$ Create a column for $$Last Month$$s Inventory$.$ Bold the label.

$7.$ Create a column for $$This Month$$s Inventory$.$ Bold the label.

$8.$ Create a column and label it $$Amount used$.$

$9.$ Create a column and label it $$Extended $$ Used$.$

$10.$ Create a formula to calculate the $$Ext $$ Used$.$

$11.$ Create a column and label it $$Purchases$.$

$12.$ Create a column labeled cost of purchases.

$13.$ Create a formula to calculate the cost of each liquor purchased.

$14.$ Create a column and label it $$Ending Inventory$.$ Bold the label.

$15.$ Create a formula to calculate Ending Inventory.

$16.$ Create a column and label it Current Inventory Value.

$17.$ Create a formula to calculate each liquors inventory current value.

$18.$ Create a column and formula to calculate total liquor costs used last month.

$19.$ Create a column and formula to calculate the Total Current Value of all liquor on hand. Bold the result.

$20.$ Taking last month$$s products costs, calculate Liquor Cost Percent. Total liquor sales for this past month are $$5517\mathrm{.}00.$

Data: Type Size Last Month This Month Purchases Price Each

Teachers Bottle $7504317\mathrm{.}99$

Bells Bottle $7505508\mathrm{.}99$

Bells Bottle $1\mathrm{.}5$ltr $33119\mathrm{.}99$

Glenfiddich Bottle $75021\mathrm{.}7024\mathrm{.}99$

Glenmorangie Bottle $75022027\mathrm{.}99$

Jack Daniels Bottle $1\mathrm{.}5$ltr $421224\mathrm{.}99$

Jim Beam Bottle $75064823\mathrm{.}99$

Jameson's Irish Bottle $75021\mathrm{.}8134\mathrm{.}00$

Southern Comfort Bottle $1\mathrm{.}5$ltr $42\mathrm{.}5637\mathrm{.}99$

Gordons' Gin Bottle $7501041218\mathrm{.}99$

Bombay Sapphire Bottle $75021\mathrm{.}5145\mathrm{.}00$

Smirnoff Red Bottle $75032\mathrm{.}4047\mathrm{.}00$

Absolut Bottle $750831223\mathrm{.}50$

Captain Morgan Bottle $7501272418\mathrm{.}99$

Bacardi Silver Bottle $750158\mathrm{.}52418\mathrm{.}00$

Bacardi Gold Bottle $1\mathrm{.}5$Ltr $21\mathrm{.}3038\mathrm{.}00$

Martell Bottle $75076\mathrm{.}5116\mathrm{.}00$

Remy Martin Bottle $75022027\mathrm{.}50$

Baileys Bottle $1\mathrm{.}5$Ltr $53\mathrm{.}2234\mathrm{.}5$

Cointreau Bottle $75021\mathrm{.}9047\mathrm{.}00$

Drambuie Bottle $75043\mathrm{.}9049\mathrm{.}00$

Malibu Bottle $75054\mathrm{.}5139\mathrm{.}00$

Archers Bottle $75065119\mathrm{.}00$

Tequila Bottle $7501832417\mathrm{.}00$

Sambuca Bottle $75043\mathrm{.}5128\mathrm{.}00$

Tia Maria Bottle $75022037\mathrm{.}00$

Answer & Explanation Solved by verified expert

See Answer