Help needed creating a comprehensive spreadsheet for managing a bar, hotel, accommodation

Hello Excel community,

I’m working on creating a spreadsheet to manage a bar, hotel, and accommodation services effectively. I've attached a draft spreadsheet with three sheets: Control Sheet, Daily Sales, and Daily Stock Management. I’d appreciate your guidance in improving and automating the functionality.

Overview of the Sheets

1. Control Sheet

This sheet contains a list of all items and services offered, such as:

Drinks: Includes their sizes (e.g., 250ml), and serving capacities for measured drinks. I’m planning to start selling some drinks in tots (smaller quantities like 25ml or 50ml), but I’m unsure what data to include here to manage this effectively.

• Meals: Listed with wholesale prices, retail prices, and expected profit margins.
• Accommodation: Rooms are currently priced at Kshs. 1,000 per night.

The Control Sheet is intended to serve as the master database where:

• New items/services can be added.
• Prices (wholesale and retail) and expected profits are set and adjusted.
• Other sheets pull their data automatically from here.

2. Daily Sales Sheet

This sheet is designed to record all sales transactions. Here’s how I envision it working:

• Item Selection: When entering an item in the “Item” column, I’d like it to provide an auto-complete dropdown based on the data from the Control Sheet, showing options like specific drink capacities (e.g., 250ml, 125ml, or tots).
• Auto-Fill: All other columns, such as price, should auto-fill based on the selected item.
• Mode of Payment: This should also have a dropdown with two options: cash and M-Pesa, and allow for easy selection as the user starts typing.
• Daily Transactions: The sheet should include dates and automatically calculate daily profits or losses.

3. Daily Stock Management Sheet

This sheet is for tracking stock levels. The workflow should look like this:

• Opening Quantity: The starting quantity for each item at the beginning of the day.
• Additions: Quantities added during the day.
• Total: The sum of opening and added quantities (e.g., if Chrome Vodka 250ml starts with 3 units, and 7 are added, the total becomes 10).
• Sold: Automatically calculated as Total - Closing Quantity.
• Closing Quantity: Updated at the end of the day.

Other columns, like totals, profits, and losses, should be automated for ease of use.

Additional Features Needed

1. Restocking Alerts:

o A list of items needing restocking should be automatically generated based on minimum and maximum stock thresholds.
o Use conditional formatting (e.g., red and green font colors or shading) to visually indicate stock levels.

2. Profit and Loss Calculations:

o Daily, monthly, and annual profit/loss reports should be automated.
o Losses should be highlighted in red, while profits should be in green.

3. Monthly Bills Management:

I pay the following monthly expenses:

o Bartender: 6,000 Kshs
o Waiter: 5,000 Kshs
o Cook: 6,000 Kshs
o Security: 6,000 Kshs
o Rent: 15,000 Kshs
o Electricity: 2,000 Kshs
o Rooms Steward: 6,000 Kshs

I need a way to track these expenses. If there’s a better way to handle these bills, I’m open to suggestions.

Key Considerations

• Non-VBA Approach: I would prefer solutions that do not involve VBA for simplicity and compatibility.
• Centralized Control: Ideally, everything should be manageable from the Control Sheet, with minimal manual input on the other sheets.


Help needed.xlsx

Administrative Note:

Is your forum profile up-to-date and showing ONLY the oldest Excel PRODUCT that you need this to work for?

Members will tailor the solutions they offer to the Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your product is for Mac, please also state this.

The four most recent Excel products are Excel 2019, Excel 2021, Excel 2024 and MS365 - if you are using MS365, please give this name along with the version number in your profile (e.g. MS365 Version 2306). This is in the About Excel section further down the Account page.

Thanks.

Thanks Ali. I am still using Excel 2010. I see Microsoft Office Professional Plus 2010 below the logo.

Hello Excel Community,

I am still hoping to get help with this.

Thanks in advance.

I have populated the Daily Sales sheet as best I understand post #1.
I was not able to get an autofill drop down in column A but did arrange the data on the Control Sheet in alphabetical order based on the Item, so perhaps that will help.
Columns C:D are populated using:

Formula:

Please Login or Register  to view this content.

Column F is populated using:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Thanks for looking at my problem JeteMC. Let me go through your attachment then get back to you.