BUILD sql schema to analyze and calculate profit & loss and balance sheet statement from accounting and stored into procedures.
Budget: $10 – $30 USD
You will need to build queries and insert results into tmp tables through a stored procedure to create the Profit and Loss (P&L) and Balance Sheet (B/S) statements.
You will be given access (read-only) to an existing database - the schema is called "accounting" in the Online Server. You can take a look at the EER diagram here: Accounting ER.mwb.
The stored procedure must achieve the following:
Receive the year in which the user wants to produce the P&L and the B/S for.
Do the necessary calculations to obtain the net profit or loss for that year.
Use that value, to produce the B/S.
Demonstrarte that A = L + E
Print both the P&L and B/S as clear as possible on the Result Grids
Show the % change vs. the previous year for every major line item on the P&L and B/S
Give some headings to each major account line item. And to each section of the P&L and B/S
Add any additional financial metrics and ratios that could be useful to analyze this start-up.
Produce a cash flow statement as an added challenge
You will submit the following:
#1 SQL script (if you save it as txt, be sure to keep the adequate indentations - you will receive points for correct syntax, following best practices, comments in the code, correctness, efficiency ):
While there are multiple ways to approach the building of their P&L and B/S statements, most likely you will need a stored procedure.
However, if you do not use a stored procedure, then be sure to include the comprehensive list of queries in ONE single .sql script that runs on the online server and will be able to provide exactly the same results as per your P&L and B/S screenshots from .
#2 Screenshot of the resulting P&L and B/S outputs. You will receive points for correct results
#3 A reflection paper (400 to 600 words) including the following:
What were the key SQL/technical learnings behind this challenge? If you were to do it all over again with the same time pressure, what would you have done differently from a technical point of view?
What were the key learnings in this task?
You will be given access (read-only) to an existing database - the schema is called "accounting" in the Online Server. You can take a look at the EER diagram here: Accounting ER.mwb.
The stored procedure must achieve the following:
Receive the year in which the user wants to produce the P&L and the B/S for.
Do the necessary calculations to obtain the net profit or loss for that year.
Use that value, to produce the B/S.
Demonstrarte that A = L + E
Print both the P&L and B/S as clear as possible on the Result Grids
Show the % change vs. the previous year for every major line item on the P&L and B/S
Give some headings to each major account line item. And to each section of the P&L and B/S
Add any additional financial metrics and ratios that could be useful to analyze this start-up.
Produce a cash flow statement as an added challenge
You will submit the following:
#1 SQL script (if you save it as txt, be sure to keep the adequate indentations - you will receive points for correct syntax, following best practices, comments in the code, correctness, efficiency ):
While there are multiple ways to approach the building of their P&L and B/S statements, most likely you will need a stored procedure.
However, if you do not use a stored procedure, then be sure to include the comprehensive list of queries in ONE single .sql script that runs on the online server and will be able to provide exactly the same results as per your P&L and B/S screenshots from .
#2 Screenshot of the resulting P&L and B/S outputs. You will receive points for correct results
#3 A reflection paper (400 to 600 words) including the following:
What were the key SQL/technical learnings behind this challenge? If you were to do it all over again with the same time pressure, what would you have done differently from a technical point of view?
What were the key learnings in this task?