create a bilance
Budget: €8 – €30 EUR
Starting from the file "file downloaded":
1 – we have to delete the first 7 rows so that the first line becomes “Data/Ora, Numero pagamento and so on”.
2 – add some columns. The new colmuns are highlighted in yellow
• Column AB “Quantità” just need to copy the values in the column G. This value is available only if in the column C there is “Ordine” if not don’t insert nothing.
• Column AC “prezzo unitario ivato” take the value “Prezzo Ivato” column K of the file “listino prezzi.ods”. To find the correct value we have to search the “SKU” column E of the file “file downloaded.csv” in the file “listino prezzi.ods”
• Column AD “TOTALE COSTO PRODOTTI IVATO” = column AB * column AC
• Column AE “costo spedizione” in in the column I “gestione” of the file “file downloaded.csv” there is write Amazon insert the value 0 otherwise if there is write “venditore” search the “order number” column D of the file “file downloaded.csv” in the file “ordini spediti marco.ods” in the sheet of the month (in the file “ordini spediti marco.ods” there are more sheets”) and take the value in the same row column C “Amazon”.
• Column AF “IVA sulla spedizione” there is the formula inside the cell.
• Column AG “Costo spedizione ivato” there is the formula inside the cell.
• Column AI “utile” there is the formula inside the cell.
• Column AL “utile + IVA” there is the formula inside the cell.
3 – add a sheet called “Costi generici”:
Create a report like the one in the sheet “Costi generici” where:
• For each row do the sum of the values in the column AA of the sheet “file downloaded”. For example for “Costo di stoccaggio Logistica di Amazon” do the sum of the column AA if in the cell C of the sheet “file downloaded” there is “Costo di stoccaggio Logistica di Amazon”.
• And so on the the others options: “Rimborso”, “trasferimento”, “commissione di servizio”
• In the sheet “file downloaded” for “TOTALE COSTO PRODOTTI IVATO” the sum of the values in the column AD of the sheet “file downloaded”.
• In the sheet “file downloaded” for “TOTALE COSTO SPEDIZIONE” the sum of the values in the column AE of the sheet “file downloaded”.
• In the sheet “file downloaded” for “TOTALE COSTO SPEDIZIONE IVATO” the sum of the values in the column AG of the sheet “file downloaded”.
• In the sheet “file downloaded” for “TOTALE IVA sulla spedizione” the sum of the values in the column AF of the sheet “file downloaded”.
• In the sheet “file downloaded” for “TOTALE UTILE” the sum of the values in the column AI of the sheet “file downloaded”.
• In the sheet “file downloaded” for “TOTALE UTILE + IVA” the sum of the values in the column AJ
of the sheet “file downloaded”.
The file “listino prezzi.ods” can change because if we add new products there will be more rows.
For any further information do not hesitate to contact me.
Thanks,
Regards,
Marco
1 – we have to delete the first 7 rows so that the first line becomes “Data/Ora, Numero pagamento and so on”.
2 – add some columns. The new colmuns are highlighted in yellow
• Column AB “Quantità” just need to copy the values in the column G. This value is available only if in the column C there is “Ordine” if not don’t insert nothing.
• Column AC “prezzo unitario ivato” take the value “Prezzo Ivato” column K of the file “listino prezzi.ods”. To find the correct value we have to search the “SKU” column E of the file “file downloaded.csv” in the file “listino prezzi.ods”
• Column AD “TOTALE COSTO PRODOTTI IVATO” = column AB * column AC
• Column AE “costo spedizione” in in the column I “gestione” of the file “file downloaded.csv” there is write Amazon insert the value 0 otherwise if there is write “venditore” search the “order number” column D of the file “file downloaded.csv” in the file “ordini spediti marco.ods” in the sheet of the month (in the file “ordini spediti marco.ods” there are more sheets”) and take the value in the same row column C “Amazon”.
• Column AF “IVA sulla spedizione” there is the formula inside the cell.
• Column AG “Costo spedizione ivato” there is the formula inside the cell.
• Column AI “utile” there is the formula inside the cell.
• Column AL “utile + IVA” there is the formula inside the cell.
3 – add a sheet called “Costi generici”:
Create a report like the one in the sheet “Costi generici” where:
• For each row do the sum of the values in the column AA of the sheet “file downloaded”. For example for “Costo di stoccaggio Logistica di Amazon” do the sum of the column AA if in the cell C of the sheet “file downloaded” there is “Costo di stoccaggio Logistica di Amazon”.
• And so on the the others options: “Rimborso”, “trasferimento”, “commissione di servizio”
• In the sheet “file downloaded” for “TOTALE COSTO PRODOTTI IVATO” the sum of the values in the column AD of the sheet “file downloaded”.
• In the sheet “file downloaded” for “TOTALE COSTO SPEDIZIONE” the sum of the values in the column AE of the sheet “file downloaded”.
• In the sheet “file downloaded” for “TOTALE COSTO SPEDIZIONE IVATO” the sum of the values in the column AG of the sheet “file downloaded”.
• In the sheet “file downloaded” for “TOTALE IVA sulla spedizione” the sum of the values in the column AF of the sheet “file downloaded”.
• In the sheet “file downloaded” for “TOTALE UTILE” the sum of the values in the column AI of the sheet “file downloaded”.
• In the sheet “file downloaded” for “TOTALE UTILE + IVA” the sum of the values in the column AJ
of the sheet “file downloaded”.
The file “listino prezzi.ods” can change because if we add new products there will be more rows.
For any further information do not hesitate to contact me.
Thanks,
Regards,
Marco