ExcelVirgin
New Member
- Joined
- Jan 27, 2010
- Messages
- 1
I've been tasked with getting to grips a little more with Excel. I've been given a scenario to do at work and would appreciate if you could give me some advice on how to tackle it. I've got a 2003 Excel Bible to help out but any pointers would be fab. Here's the scenario:
Background
The Hotel Inventory System (“CT”) is used to create Purchase Orders for food, bar and hotel inventory items to be consumed onboard by passengers. CT is also used to record the receipts of goods, record the consumption of items and for general inventory control onboard. All deliveries are generally made to the ship at the start of each cruise.
The finance system is used to make supplier payments (via the AP module) and record the relevant warehouse transfer transactions in the general ledger.
CT - Purchasing/Receiving Items
Goods are either purchased directly from a supplier or transferred from previously purchased stock stored at a warehouse location. In CT these can be separately identified by the “Receipt Type” in the CT data worksheet.
The Purchase orders are generated from CT for both types of transactions and sent to the relevant supplier/warehouse. These purchase orders detail the price and quantity of goods required as well as the cruise that the items are required for.
When the goods are received by the ship, a receipt is recorded in CT against the original purchase order by inputting the units received.
Finance System – Recording Payments & Transfers in the GL
For supplier orders :
When the order is raised in CT, an interface replicates the purchase order in the finance system. When the order is receipted in CT, the receipts are interfaced into the finance system against the relevant PO. Invoices received from suppliers are then “matched” against these purchase orders in order to generate the payment to the supplier. These transactions are then transferred to the GL with a JE Source of “Payables”.
If there is a receipt, but no invoice at a month end for a purchase order, then an accrual will be made in the general ledger. These transactions are recorded with a JE Source of “Purchasing”.
For warehouse orders:
The goods have been previously purchased from the vendors and therefore the interface does not replicate the purchase order in the finance system. The transaction is recorded in the GL by a journal (using JE Source “Manual”) which credits the stock account of the warehouse and debits the stock account of the ship receiving the items.
Scenario
A new ship – Adonis - has entered service and has now been on two voyages (cruise numbers A001 & A002). There have been a number of transactions in the GL related to these cruises.
The value of the GL transactions (Net GBP) and Hotel Inventory Systems (Value GBP) needs to be reconciled and any differences noted. A comparison of the total value of each Purchase Order in each system may be required.
Requirements:
1. Identify the total value of the difference between the two systems for each cruise.
2. Identify the difference between the two systems for both warehouse receipts and supplier receipts for each cruise.
3. Define which cruise has the greatest variance between the two systems. If possible, identify the main the transaction(s) creating the difference.
I've been given a workbook with two worksheets: one for the G/L data; one for the CV data. I want to try and arrange this data in some meaningful way as per the above. THANKS!
Background
The Hotel Inventory System (“CT”) is used to create Purchase Orders for food, bar and hotel inventory items to be consumed onboard by passengers. CT is also used to record the receipts of goods, record the consumption of items and for general inventory control onboard. All deliveries are generally made to the ship at the start of each cruise.
The finance system is used to make supplier payments (via the AP module) and record the relevant warehouse transfer transactions in the general ledger.
CT - Purchasing/Receiving Items
Goods are either purchased directly from a supplier or transferred from previously purchased stock stored at a warehouse location. In CT these can be separately identified by the “Receipt Type” in the CT data worksheet.
The Purchase orders are generated from CT for both types of transactions and sent to the relevant supplier/warehouse. These purchase orders detail the price and quantity of goods required as well as the cruise that the items are required for.
When the goods are received by the ship, a receipt is recorded in CT against the original purchase order by inputting the units received.
Finance System – Recording Payments & Transfers in the GL
For supplier orders :
When the order is raised in CT, an interface replicates the purchase order in the finance system. When the order is receipted in CT, the receipts are interfaced into the finance system against the relevant PO. Invoices received from suppliers are then “matched” against these purchase orders in order to generate the payment to the supplier. These transactions are then transferred to the GL with a JE Source of “Payables”.
If there is a receipt, but no invoice at a month end for a purchase order, then an accrual will be made in the general ledger. These transactions are recorded with a JE Source of “Purchasing”.
For warehouse orders:
The goods have been previously purchased from the vendors and therefore the interface does not replicate the purchase order in the finance system. The transaction is recorded in the GL by a journal (using JE Source “Manual”) which credits the stock account of the warehouse and debits the stock account of the ship receiving the items.
Scenario
A new ship – Adonis - has entered service and has now been on two voyages (cruise numbers A001 & A002). There have been a number of transactions in the GL related to these cruises.
The value of the GL transactions (Net GBP) and Hotel Inventory Systems (Value GBP) needs to be reconciled and any differences noted. A comparison of the total value of each Purchase Order in each system may be required.
Requirements:
1. Identify the total value of the difference between the two systems for each cruise.
2. Identify the difference between the two systems for both warehouse receipts and supplier receipts for each cruise.
3. Define which cruise has the greatest variance between the two systems. If possible, identify the main the transaction(s) creating the difference.
I've been given a workbook with two worksheets: one for the G/L data; one for the CV data. I want to try and arrange this data in some meaningful way as per the above. THANKS!
