Link to Prompt on Chat GPT-4: Financial Statement & Projection.
on Chat GPT-3.5 : Financial Projection VBA
What the Prompt does
What does it do? The prompt generates a VBA code to create a statement of financial position, a projected financial statement, and a financial model and valuation for a small business.
Who is it for? The prompt is designed for small business owners who need assistance in creating their company’s financial statements, models, and valuation.
The pain point the prompt is designed to solve: The prompt aims to reduce the headaches of small business owners by providing them with a ready-to-use VBA code that automates the process of generating financial statements and models.
The prompt helps small businesses to:
- prepare their business plans
-apply for grants and investments
-gain insights into their company’s financials without any cost //before consulting a certified accounting and tax professional.
Example outputs demonstrating that the prompt works: The prompt generates a complete statement of financial position, a projected financial statement for the next two years, and a financial model and valuation based on assumed figures. The outputs are presented in a formatted Excel worksheet, providing a clear overview of the business’s financial position and projected performance.
## Example Prompt Output GPT3.5
Input:
Give the input as text - marked as code example
You are a certified accounting and tax professional. Generate a VBA code for a sample financial statement and a financial modelling and valuation from this prompt: Hello, you are a certified accounting and tax professional. Create a statement of financial position for a small business. Details of the small business { Company Overview: (Briefly describe your business), Business Structure: (Specify your business structure), EIN: (Provide your Employer Identification Number), Accounting Method: (Specify your accounting method), Assets: These are items of value that your business owns. You’ll need to provide details on:
Cash and cash equivalents: (Provide the total amount of cash and cash equivalents)
Accounts Receivable: (Money owed to your business by customers), Inventory: (Total value of all inventory), Prepaid Expenses: (Expenses paid in advance), Fixed Assets: (Long-term assets like property, plant, and equipment), Accumulated Depreciation: (Total depreciation on your fixed assets), Other Assets: (Any other assets not listed)
Liabilities: These are the amounts your business owes. You’ll need to provide details on:
Accounts Payable: (Money your business owes to suppliers)
Accrued Expenses: (Expenses recognized before they are paid), Notes Payable: (Short-term borrowings)
Current Portion of Long-Term Debt: (The part of long-term debt due within a year), Long-Term Debt: (Debt that is due more than a year from now), Other Liabilities: (Any other debts not listed)
Equity: This represents the ownership interest in the business. You’ll need to provide details on:
Common Stock: (Value of shares the company has issued)
Retained Earnings: (Company earnings that are reinvested in the business)
Treasury Stock: (Company’s own stock that it has reacquired) you can make everything up}. Also, create a proposition of the projected financial statement over the next 2 years. Use made-up figures and generate a VBA code to represent the information on Excel or sheets) using those assumptions.
Please write in English language.
Output:
Give the output as text - marked as code example
Sub GenerateFinancialStatementAndModel()
Dim ws As Worksheet
Dim rowNum As Integer
Dim cashAndEquivalents As Double
Dim accountsReceivable As Double
Dim inventory As Double
Dim prepaidExpenses As Double
Dim fixedAssets As Double
Dim accumulatedDepreciation As Double
Dim otherAssets As Double
Dim accountsPayable As Double
Dim accruedExpenses As Double
Dim notesPayable As Double
Dim currentPortionOfLongTermDebt As Double
Dim longTermDebt As Double
Dim otherLiabilities As Double
Dim commonStock As Double
Dim retainedEarnings As Double
Dim treasuryStock As Double
' Set the worksheet where the financial statements will be generated
Set ws = ThisWorkbook.Sheets("Financial Statements")
' Clear existing data in the worksheet
ws.Cells.Clear
' Set up headers for the statement of financial position
ws.Range("A1").Value = "Statement of Financial Position"
ws.Range("A3").Value = "Assets"
ws.Range("A4").Value = "Cash and cash equivalents"
ws.Range("A5").Value = "Accounts Receivable"
ws.Range("A6").Value = "Inventory"
ws.Range("A7").Value = "Prepaid Expenses"
ws.Range("A8").Value = "Fixed Assets"
ws.Range("A9").Value = "Accumulated Depreciation"
ws.Range("A10").Value = "Other Assets"
ws.Range("A12").Value = "Liabilities"
ws.Range("A13").Value = "Accounts Payable"
ws.Range("A14").Value = "Accrued Expenses"
ws.Range("A15").Value = "Notes Payable"
ws.Range("A16").Value = "Current Portion of Long-Term Debt"
ws.Range("A17").Value = "Long-Term Debt"
ws.Range("A18").Value = "Other Liabilities"
ws.Range("A20").Value = "Equity"
ws.Range("A21").Value = "Common Stock"
ws.Range("A22").Value = "Retained Earnings"
ws.Range("A23").Value = "Treasury Stock"
' Provide sample data for the statement of financial position
cashAndEquivalents = 50000
accountsReceivable = 25000
inventory = 75000
prepaidExpenses = 10000
fixedAssets = 200000
accumulatedDepreciation = 50000
otherAssets = 30000
accountsPayable = 40000
accruedExpenses = 20000
notesPayable = 60000
currentPortionOfLongTermDebt = 30000
longTermDebt = 150000
otherLiabilities = 10000
commonStock = 100000
retainedEarnings = 50000
treasuryStock = 20000
ws.Range("B4").Value = cashAndEquivalents
ws.Range("B5").Value = accountsReceivable
ws.Range("B6").Value = inventory
ws.Range("B7").Value = prepaidExpenses
ws.Range("B8").Value = fixedAssets
ws.Range("B9").Value = accumulatedDepreciation
ws.Range("B10").Value = otherAssets
ws.Range("B13").Value = accountsPayable
ws.Range("B14").Value = accruedExpenses
ws.Range("B15").Value = notesPayable
ws.Range("B16").Value = currentPortionOfLongTermDebt
ws.Range("B17").Value = longTermDebt
ws.Range("B18").Value = otherLiabilities
ws.Range("B21").Value = commonStock
ws.Range("B22").Value = retainedEarnings
ws.Range("B23").Value = treasuryStock
' Set up headers for the projected financial statement
ws.Range("D1").Value = "Projected Financial Statement (Next 2 Years)"
ws.Range("D3").Value = "Year"
ws.Range("E3").Value = "Revenue"
ws.Range("F3").Value = "Expenses"
ws.Range("G3").Value = "Net Income"
' Provide sample data for the projected financial statement
For rowNum = 4 To 5 ' Year 1 and Year 2
ws.Cells(rowNum, "D").Value = "Year " & (rowNum - 3)
ws.Cells(rowNum, "E").Value = 500000 ' Revenue projection
ws.Cells(rowNum, "F").Value = 400000 ' Expenses projection
ws.Cells(rowNum, "G").FormulaR1C1 = "=RC[-2]-RC[-1]" ' Net Income calculation
Next rowNum
' Apply formatting to the financial statements and the projection table
ws.Range("A1:G3").Font.Bold = True
ws.Range("A1:G3").HorizontalAlignment = xlCenter
ws.Range("A3:G3").Borders(xlEdgeBottom).LineStyle = xlContinuous
ws.Range("A4:G23").HorizontalAlignment = xlLeft
ws.Range("B4:B23").NumberFormat = "$#,##0"
ws.Range("D1:G1").Font.Bold = True
' Auto-fit column widths for better readability
ws.Columns.AutoFit
' Display a message indicating that the financial statements and projection are generated
MsgBox "Financial statements and projection generated successfully!", vbInformation
' Perform basic financial modeling and valuation
Dim netIncome As Double
Dim discountRate As Double
Dim valuation As Double
' Assume net income from Year 1 projection
netIncome = ws.Range("G4").Value
' Assume a discount rate of 10% for valuation
discountRate = 0.1
' Calculate the valuation using the discounted cash flow method
valuation = netIncome / (1 + discountRate)
' Display the valuation in the worksheet
ws.Range("G7").Value = "Valuation"
ws.Range("H7").Value = valuation
' Apply formatting to the valuation
ws.Range("G7:H7").Font.Bold = True
ws.Range("G7:H7").HorizontalAlignment = xlCenter
' Auto-fit column widths for better readability
ws.Columns.AutoFit
End Sub
## Example Prompt Output GPT4
Input:
Give the input as text - marked as code example
You are a certified accounting and tax professional. Generate a VBA code for a sample financial statement and also, a financial model and valuation of the business from this prompt: Hello, you are a certified accounting and tax professional. Create a statement of financial position for a small business. Details of the small business { Company Overview: (Briefly describe your business), Business Structure: (Specify your business structure), EIN: (Provide your Employer Identification Number), Accounting Method: (Specify your accounting method), Assets: These are items of value that your business owns. You’ll need to provide details on:
Cash and cash equivalents: (Provide the total amount of cash and cash equivalents)
Accounts Receivable: (Money owed to your business by customers), Inventory: (Total value of all inventory), Prepaid Expenses: (Expenses paid in advance), Fixed Assets: (Long-term assets like property, plant, and equipment), Accumulated Depreciation: (Total depreciation on your fixed assets), Other Assets: (Any other assets not listed)
Liabilities: These are the amounts your business owes. You’ll need to provide details on:
Accounts Payable: (Money your business owes to suppliers)
Accrued Expenses: (Expenses recognized before they are paid), Notes Payable: (Short-term borrowings)
Current Portion of Long-Term Debt: (The part of long-term debt due within a year), Long-Term Debt: (Debt that is due more than a year from now), Other Liabilities: (Any other debts not listed)
Equity: This represents the ownership interest in the business. You’ll need to provide details on:
Common Stock: (Value of shares the company has issued)
Retained Earnings: (Company earnings that are reinvested in the business)
Treasury Stock: (Company’s own stock that it has reacquired) you can make everything up}. Also, create a proposition of the projected financial statement over the next 2 years. Use made-up figures and generate a VBA code to represent the information on Excel or sheets) using those assumptions.
Output:
Give the output as text - marked as code example
Sub CreateFinancialStatement()
' Define the worksheet
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
' Define the header
ws.Range("A1:L1").Value = Array("Company Overview", "Business Structure", "EIN", "Accounting Method", _
"Cash and Cash Equivalents", "Accounts Receivable", "Inventory", "Prepaid Expenses", "Fixed Assets", _
"Accumulated Depreciation", "Other Assets", "Total Assets")
ws.Range("A2:L2").Value = Array("ABC Inc.", "Sole Proprietorship", "12-3456789", "Cash", _
20000, 15000, 10000, 5000, 30000, 10000, 5000, (20000+15000+10000+5000+30000+10000+5000))
' Define the liabilities
ws.Range("A4:H4").Value = Array("Accounts Payable", "Accrued Expenses", "Notes Payable", _
"Current Portion of Long-Term Debt", "Long-Term Debt", "Other Liabilities", "Total Liabilities")
ws.Range("A5:H5").Value = Array(5000, 2000, 3000, 5000, 10000, 3000, (5000+2000+3000+5000+10000+3000))
' Define the equity
ws.Range("A7:D7").Value = Array("Common Stock", "Retained Earnings", "Treasury Stock", "Total Equity")
ws.Range("A8:D8").Value = Array(20000, 10000, 5000, (20000+10000-5000))
End Sub
## How I came up with it
I was trying to set up an advertising business with a couple of friends but we did not have any funding to begin to hire people so we had to do everything by ourselves. One challenge was having an idea of how our financials would like after 2 years to guide how much we needed to generate and spend. I decided to create this prompt to easily create a financial model, projection, and valuation that small business owners can use to base their assumptions and convince investors before they can finally afford to hire accounting experts.