Closed

Create Spreadsheet template

Hello,

I need to create an excel template for my manager to fill in.

My concept is below.

I have 150 x items (maybe more to add later on). ITEM-1 to ITEM-150

These items are imported to my warehouse in parcels that contain various combinations of ITEM-1 through to ITEM-150.

For example:

Parcel 1, Tracking number #0001

May contain:

25 x ITEM-2

20 x ITEM-7

10 x ITEM-120

———-

I need to be able to enable a macro that automatically updates my warehouse stock once that parcel has arrived with one click, so that my local inventory is updated.

———-

Once items are arrived to my warehouse, I sort them and store them until customers order. All my customers have the same flat rate per item, however I need to be able to add a function where I can discount their total order by whatever % I wish if I feel it is applicable.

Some customers purchase products in full,

Other customers purchase products on consignment

And some customers purchase items with a % deposit and the rest on consignment

I have up to 50 x customers (need to be able to add more later)

——-

As for staff, they are paid by commission.

Staff receives X% commission for every item sold.

Each item the staff has different commission X% for.

I need a function to be able to give staff bonuses.

———

I also have overheads. I need a function for this.

——

I need to be able track profit. I have a cost price per item, and customers have a price per item.

I need to be able to track staffs weekly commission, weekly profit/loss and overall profit/loss/expenses/staff.

——-

I need to be alerted by a colour when items reach a low point of 25 x items each, or whatever low point I delegate.

——-

I have multiple suppliers who supply different items to me within my inventory. I need to be able to track what I owe/pay each supplier. At current, I have 5 suppliers.

My suppliers list items to me on consignment also and sometimes I pay up front.

———-

For my template, I have the following scenario I would like entered so that we can create the sheet:

I have $10,000 AUD in my business account

I currently owe no suppliers any money and my customers currently owe me no money either.

I order the following items from Supplier 1 on 1/12/2017 on consignment

10 x ITEM-1

10 x ITEM-2

I order the following items from Supplier 2 3/12/2017 up front payment

10 x ITEM-3

10 x ITEM-4

Supplier 1 sends the following parcels on the 2/12/3017

Parcel 1, tracking #001

- 5 x ITEM-1

- 5 x ITEM-2

Parcel 2, tracking #002

- 5 x ITEM-1

- 5 x ITEM-2

Supplier 2 sends the following parcels 4/12/2017

-10 x ITEM-3

-10 x ITEM-4

—————-

All three parcels then arrive to my warehouse on the same date, the 10/12/2017 and are updated on my local

Inventory

—————-

Week 1 of business 10/12/2017-17/12/2017:

Customer-A places an order of

2 x ITEM-1

2 x ITEM-2

He pays for all the items up front. I discount him 10%

—————

Customer-B places an order of

2 x ITEM-1

2 x ITEM-2

He pays for 50% of the items up front, and 50% he has a consignment owing.

———-

Customer-C places an order of

9 x ITEM-4

He accepts the entire order on consignment.

———-

My cost price for items:

ITEM-1 $10

ITEM-2 $10

ITEM-3 $20

ITEM-4 $50

———-

Customers cost price for items:

ITEM-1 $20

ITEM-2 $20

ITEM-3 $40

ITEM-4 $100

————

Staff is paid 5% commission per item

I give staff a bonus of $10

————

Overheads for the week cost $20

————

Low points to be alerted are when an item only has 1 item left on local stock.

————

What is my weekly profit/loss?

What was overall weekly staff costs?

What was overall weekly overheads?

Do I need to order any products from suppliers ASAP?

What do I owe suppliers?

Skills: Data Entry, Excel, Powerpoint, Word

See more: excel macro create spreadsheet template list data, create simple excel spreadsheet template macros, create report template via vba access database, create typo3 template dreamweaver, create wordpress template psd, create xhtml template constant contact, create pligg template, create dreamweaver template, create css template php, create cart template, create dynamic template web gmail, create xhtml template, create email template graphics, create zencart template, create joomla template

About the Employer:
( 0 reviews ) Thiruvananthapuram, India

Project ID: #15700990

36 freelancers are bidding on average $20/hour for this job

schoudhary1553

Hello Sir, I am the expert freelancer for Create Spreadsheet template job . i have deliver more than 250 projects with 100% client satisfaction. so please give me chance to work on your job. I have more than 5 More

$15 AUD / hour
(203 Reviews)
7.2
SShtv

I have a lot of experience in excel, including automation of warehouses and transportation organizations. [url removed, login to view] [url removed, login to view] More

$15 AUD / hour
(24 Reviews)
4.8
chintuag

Dear Hiring Manager, I have read the project description and would like to offer my expertise for your project. I am an expert in excel and have worked on multiple automation projects. My expertise in excel inclu More

$21 AUD / hour
(4 Reviews)
3.4
indicomworld

Hi There, I can fulfill all your requirement, recently i developed similar project in MS excel. I am highly Skilled in MS-Excel and have been developing Spreadsheets for Several Years. it will cost 20K INR and a we More

$15 AUD / hour
(2 Reviews)
2.3
rokunzzaman99

Dear Hiring Manager, I have gone through your job-posting and become very eager to work with you.I have a group of young energetic team. Best in Business Rokunzzaman

$16 AUD / hour
(2 Reviews)
0.4
Ecosantos

A proposal has not yet been provided

$22 AUD / hour
(0 Reviews)
0.0
hegde87

A proposal has not yet been provided

$18 AUD / hour
(0 Reviews)
0.0
$22 AUD / hour
(0 Reviews)
0.0
juanchigarin

I'm very resposible person and I could help you when the work is complicated because I have tools to solve the problems and I'm too proactive. I'm enginner with a lot of experience programming macros to excel for to More

$16 AUD / hour
(0 Reviews)
0.0
daniyal0320

I have understand your need and demand, and I completely assure you that I will complete this task in an hour and it would be much efficient.

$22 AUD / hour
(0 Reviews)
0.0
desie151515

Saya pekerja keras dan yakin mampu menyelesaikan pekerjaan dengan baik Relevant Skills and Experience Bidang administrasi adalah keterampilan saya dan entri data salah satunya

$22 AUD / hour
(0 Reviews)
0.0
kmsn

Hi EXPERT IN MS OFFICE ( EXCEL, WORD, POWER-POINT ) I have more than 17 years of experience in data management Expert In Data Formatting, Data Entry, Excel Formulas, Report Creation, Pivot Table, Mail Merging, More

$22 AUD / hour
(0 Reviews)
0.0
shanswu3

A proposal has not yet been provided

$20 AUD / hour
(0 Reviews)
0.0
vidadk

Judging by project description, I think that it is better to prepare data base in Access than working in Excel. My bid is for that solution.

$22 AUD / hour
(0 Reviews)
0.0
Reshika123

A proposal has not yet been provided

$16 AUD / hour
(0 Reviews)
0.0
Smitakokane

Hello I have 11 years of experience in Accounting and Finance stream on excel as well ERP Software. I can provide you a reliable and On time Accounting support Thank You smita

$16 AUD / hour
(0 Reviews)
0.0
freelancersree24

Working as Senior Macro Developer in an MNC for the pat 7 years. Developed multitude of solutions for the MNC's and automated their manual works using VBA Macros. Received Awards for saving 300000 USD using a macro More

$15 AUD / hour
(0 Reviews)
0.0
Lawatonyema

I am good at my job. I understand project [url removed, login to view] application of my Secretarial and administrative skills as a freelancer makes the job easier. I will ensure to complete the task before deadline.

$22 AUD / hour
(0 Reviews)
0.0
abbas46

I has check the Jon & willing yo do it as already I am working with my store team for warehouse data management. I has approach, of how to proceed & has to complete the job. I am managing regional & headquarter store & More

$16 AUD / hour
(0 Reviews)
0.0
matejdblatnik

A proposal has not yet been provided

$20 AUD / hour
(0 Reviews)
0.0