Others

How to Create a Workplace Tuck Shop IOU System with Microsoft Forms

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to set up a digital IOU process for a workplace tuck shop where colleagues can select items, have the selections tied to their names, and automatically generate monthly totals for invoicing.

Product: Microsoft Forms and Excel
Device & OS: not provided
Scenario: Managing a workplace tuck shop and tracking employee IOUs for monthly billing.
Observed behavior: Looking for a method to link form submissions to specific users without anonymity and calculate aggregated monthly totals.

Before you start

Ensure you have administrative access to your organization's Microsoft 365 account so you can require user authentication for form submissions.

Solution 1Recommended

Use Microsoft Forms and Excel to Track IOUs

This method uses Microsoft Forms to collect purchases and syncs the data to Excel to calculate totals using formulas or PivotTables.

By restricting form access to your organization, you can automatically capture the submitter's name and email address. This eliminates the need for manual name entry and prevents anonymous submissions.

Create a New Form

Open Microsoft Forms, click 'New Form', and list your available tuck shop products and their prices as multiple-choice options.

Configure User Sign-in

Go to the form's Settings (via the three-dot menu in the top right) and select 'Only people in my organization can respond'. Ensure the 'Record name' box is checked so responses are linked to users rather than appearing as Anonymous.

Link to an Excel Workbook

Navigate to the 'Responses' tab in Microsoft Forms and click 'Open in Excel'. This will create an associated workbook that stores all incoming IOUs along with timestamps and submitter names.

Calculate Monthly Totals

In the associated Excel workbook, use a PivotTable or SUMIFS formulas to aggregate the purchase amounts by the 'Name' column and filter by the current month to generate invoices.

Automating with Power Automate: For advanced setups, you can use Power Automate to trigger an automatic email summary to each employee at the end of the month based on the Excel data.

Build Your Tuck Shop Tracking System with WPS Office

WPS Office provides seamless integration between WPS Forms and WPS Spreadsheet. You can easily collect IOU entries from colleagues and automatically tally monthly totals using built-in spreadsheet functions.

1. Create the Tuck Shop Form: Open WPS Office and select WPS Form to create your tuck shop item list.
2. Collect Submissions: Share the form with your colleagues and collect their daily IOU entries.
3. Export Data: Export the collected responses directly into a WPS Spreadsheet with one click.
4. Calculate Totals: Use PivotTables or SUMIF formulas in WPS Spreadsheet to automatically calculate monthly totals for each person.

Completely free to create, share, and manage formsSeamless data synchronization to WPS Spreadsheet for easy tracking100% compatibility with Microsoft Excel formats (.xlsx)Easy-to-use PivotTables for generating monthly invoices

microsoft office alternative - wps office

Frequently Asked Questions

Can I prevent anonymous submissions in my tuck shop form?

Yes. In Microsoft Forms, adjust the sharing settings to 'Only people in my organization can respond'. This forces users to sign in, automatically recording their exact names and emails.

How do I calculate the monthly totals automatically?

Once your form data is synced to an Excel or WPS Spreadsheet, you can insert a PivotTable. Group the data by the 'Name' and 'Date' columns to automatically sum up the purchased amounts for any given period.

How do I share the monthly totals with employees?

You can manually send a summary by copying the PivotTable data from your spreadsheet, or you can set up a Microsoft Power Automate flow to automatically email individual totals to each colleague on the last day of the month.

Office Quick Solutions