Excel Budget and PO Generator
Budget: $30 – $250 USD
**Job Post: Create and Set Up a Purchase Order (PO) System in Excel**
**Dear Freelancer,**
We are looking for a skilled freelancer to create an Excel-based Purchase Order (PO) system. Below are the detailed instructions:
### Project Overview:
We need an Excel spreadsheet that will serve as our Purchase Order (PO) system and manage our IT budget. The system should automatically update when new budget items are entered and populate the following tabs:
- PO - All Costs
- PO - Monthly
- PO - Annually
- There should be buttons on the document to print the PO as PDF.
### Detailed Instructions:
#### Step 1: Create the Excel Spreadsheet
1. **Set Up the Spreadsheet Layout:**
- **Sheet Title:** "Purchase Order System"
- **Headers (First Row):**
- PO Number
- Vendor Name
- Item Description
- Quantity
- Unit Price
- Total Price
- Unit Time Frame (Month/Year)
- ARC/MRC (Annual Recurring Cost/Monthly Recurring Cost)
- Date of Order
- Approval Status
- Notes/Comments
2. **Column Details:**
- **PO Number:** Unique identifier for each PO.
- **Vendor Name:** Name of the vendor.
- **Item Description:** Description of the items/services being ordered.
- **Quantity:** Number of units ordered.
- **Unit Price:** Price per unit.
- **Total Price:** Auto-calculate (Quantity * Unit Price).
- **Unit Time Frame:** Specify if the units are billed per month or per year.
- **ARC/MRC:** Specify if the cost is Annual Recurring Cost (ARC) or Monthly Recurring Cost (MRC).
- **Date of Order:** Date when the PO is created.
- **Approval Status:** Indicate if the PO is approved (Yes/No).
- **Notes/Comments:** Any additional relevant information.
3. **Auto-Calculations:**
- Set up the Total Price column to automatically calculate the total for each line item using the formula: =Quantity * Unit Price.
4. **Formatting:**
- Use cell formatting to distinguish headers (e.g., bold font, background color).
- Ensure all monetary values are formatted as currency.
#### Step 2: Populate Initial Data
1. **Add Sample Data:**
- Populate the spreadsheet with sample data demonstrating ARC and MRC entries with various time frames (monthly/yearly).
2. **Approval Process:**
- Leave the Approval Status column blank initially. Highlight this column to indicate it requires updating once the PO is reviewed and approved.
#### Step 3: Establish the PO System
1. **Create a PO Template:**
- Design a separate sheet or section within the main sheet as a template for new POs, including all headers and a consistent structure.
2. **Setup Data Validation:**
- Use data validation for the ARC/MRC and Unit Time Frame columns (dropdown lists with options "ARC" and "MRC", "Month" and "Year").
3. **Create a PO Tracking System:**
- Develop a tracking mechanism to monitor the status of each PO:
- Include a summary section with a count of total POs, approved POs, and pending approvals.
- Implement conditional formatting to highlight pending approvals.
4. **Add Receipt Management:**
- Add a column or a separate sheet to attach links to digital receipts or indicate receipt status, including a method to verify receipt approval.
#### Step 4: Documentation and Instructions
1. **Instruction Sheet:**
- Create an additional sheet titled "Instructions" within the workbook, documenting step-by-step instructions on using the PO system.
2. **User Guide:**
- Write a brief user guide explaining the purpose of each column and how to enter data correctly. Include screenshots or visual aids if necessary.
#### Step 5: Final Review and Handover
1. **Review:**
- Ensure all formulas and data validation rules are working correctly.
- Test the system with sample entries to verify functionality.
2. **Handover:**
- Provide the final Excel file and a summary of the work done.
- Be available for a brief walkthrough or Q&A session if needed.
**Requirements:**
- The PO system must be user-friendly and clearly documented.
- Available for questions or further clarification if needed.
**Thank you!**
**Dear Freelancer,**
We are looking for a skilled freelancer to create an Excel-based Purchase Order (PO) system. Below are the detailed instructions:
### Project Overview:
We need an Excel spreadsheet that will serve as our Purchase Order (PO) system and manage our IT budget. The system should automatically update when new budget items are entered and populate the following tabs:
- PO - All Costs
- PO - Monthly
- PO - Annually
- There should be buttons on the document to print the PO as PDF.
### Detailed Instructions:
#### Step 1: Create the Excel Spreadsheet
1. **Set Up the Spreadsheet Layout:**
- **Sheet Title:** "Purchase Order System"
- **Headers (First Row):**
- PO Number
- Vendor Name
- Item Description
- Quantity
- Unit Price
- Total Price
- Unit Time Frame (Month/Year)
- ARC/MRC (Annual Recurring Cost/Monthly Recurring Cost)
- Date of Order
- Approval Status
- Notes/Comments
2. **Column Details:**
- **PO Number:** Unique identifier for each PO.
- **Vendor Name:** Name of the vendor.
- **Item Description:** Description of the items/services being ordered.
- **Quantity:** Number of units ordered.
- **Unit Price:** Price per unit.
- **Total Price:** Auto-calculate (Quantity * Unit Price).
- **Unit Time Frame:** Specify if the units are billed per month or per year.
- **ARC/MRC:** Specify if the cost is Annual Recurring Cost (ARC) or Monthly Recurring Cost (MRC).
- **Date of Order:** Date when the PO is created.
- **Approval Status:** Indicate if the PO is approved (Yes/No).
- **Notes/Comments:** Any additional relevant information.
3. **Auto-Calculations:**
- Set up the Total Price column to automatically calculate the total for each line item using the formula: =Quantity * Unit Price.
4. **Formatting:**
- Use cell formatting to distinguish headers (e.g., bold font, background color).
- Ensure all monetary values are formatted as currency.
#### Step 2: Populate Initial Data
1. **Add Sample Data:**
- Populate the spreadsheet with sample data demonstrating ARC and MRC entries with various time frames (monthly/yearly).
2. **Approval Process:**
- Leave the Approval Status column blank initially. Highlight this column to indicate it requires updating once the PO is reviewed and approved.
#### Step 3: Establish the PO System
1. **Create a PO Template:**
- Design a separate sheet or section within the main sheet as a template for new POs, including all headers and a consistent structure.
2. **Setup Data Validation:**
- Use data validation for the ARC/MRC and Unit Time Frame columns (dropdown lists with options "ARC" and "MRC", "Month" and "Year").
3. **Create a PO Tracking System:**
- Develop a tracking mechanism to monitor the status of each PO:
- Include a summary section with a count of total POs, approved POs, and pending approvals.
- Implement conditional formatting to highlight pending approvals.
4. **Add Receipt Management:**
- Add a column or a separate sheet to attach links to digital receipts or indicate receipt status, including a method to verify receipt approval.
#### Step 4: Documentation and Instructions
1. **Instruction Sheet:**
- Create an additional sheet titled "Instructions" within the workbook, documenting step-by-step instructions on using the PO system.
2. **User Guide:**
- Write a brief user guide explaining the purpose of each column and how to enter data correctly. Include screenshots or visual aids if necessary.
#### Step 5: Final Review and Handover
1. **Review:**
- Ensure all formulas and data validation rules are working correctly.
- Test the system with sample entries to verify functionality.
2. **Handover:**
- Provide the final Excel file and a summary of the work done.
- Be available for a brief walkthrough or Q&A session if needed.
**Requirements:**
- The PO system must be user-friendly and clearly documented.
- Available for questions or further clarification if needed.
**Thank you!**