import gradio as gr import pandas as pd import matplotlib.pyplot as plt from openpyxl import Workbook from io import BytesIO import base64 import re def parse_assumptions(assumptions_text): # Default values gr.Markdown("# Projections for Tea Production Machine Replacement") params = { 'initial_investment': 1000000000, # Rp 1M 'annual_revenue': 500000000, # Rp 500jt/tahun 'revenue_growth': 0.05, # 5% per tahun 'operating_cost': 300000000, # Rp 300jt/tahun 'cost_growth': 0.03, # 3% per tahun 'depreciation_years': 7, # Umur ekonomis mesin 'tax_rate': 0.25, # Pajak 25% 'working_capital': 50000000 # Modal kerja Rp 50jt } # Parsing assumptions from text patterns = { 'initial_investment': r'Initial Investment[\s:]*Rp\s*([\d,.]+)', 'annual_revenue': r'Annual Revenue[\s:]*Rp\s*([\d,.]+)', 'revenue_growth': r'Revenue Growth[\s:]*([\d.]+)\%', 'operating_cost': r'Operating Cost[\s:]*Rp\s*([\d,.]+)', 'cost_growth': r'Cost Growth[\s:]*([\d.]+)\%', 'depreciation_years': r'Depreciation Years[\s:]*(\d+)', 'tax_rate': r'Tax Rate[\s:]*([\d.]+)\%', 'working_capital': r'Working Capital[\s:]*Rp\s*([\d,.]+)' } for key, pattern in patterns.items(): match = re.search(pattern, assumptions_text, re.IGNORECASE) if match: value = match.group(1) if key in ['initial_investment', 'annual_revenue', 'operating_cost', 'working_capital']: value = float(value.replace(',', '').replace('.', '')) elif key in ['revenue_growth', 'cost_growth', 'tax_rate']: value = float(value) / 100 else: value = float(value) params[key] = value return params def generate_charts(pl_df, cf_df): # Create Profit Loss chart plt.figure(figsize=(10, 6)) plt.plot(pl_df['Year'], pl_df['Revenue'], label='Revenue', marker='o') plt.plot(pl_df['Year'], pl_df['Net Income'], label='Net Income', marker='s') plt.title('Profit and Loss Trend') plt.xlabel('Year') plt.ylabel('Amount (Rp)') plt.grid(True) plt.legend() plt.tight_layout() # Save PL chart to BytesIO pl_chart_io = BytesIO() plt.savefig(pl_chart_io, format='png') plt.savefig('pl_chart_base64.png') # Save the image plt.close() pl_chart_io.seek(0) pl_chart_base64 = base64.b64encode(pl_chart_io.read()).decode('utf-8') # Create Cashflow chart plt.figure(figsize=(10, 6)) plt.plot(cf_df['Year'], cf_df['Net Cashflow'], label='Net Cashflow', marker='o', color='green') plt.title('Cashflow Trend') plt.xlabel('Year') plt.ylabel('Amount (Rp)') plt.grid(True) plt.legend() plt.tight_layout() # Save CF chart to BytesIO cf_chart_io = BytesIO() plt.savefig(cf_chart_io, format='png') plt.savefig('cf_chart_base64.png') # Save the image plt.close() cf_chart_io.seek(0) cf_chart_base64 = base64.b64encode(cf_chart_io.read()).decode('utf-8') return pl_chart_base64, cf_chart_base64 def generate_financials(assumptions_text): params = parse_assumptions(assumptions_text) print("halo mulai", params) years = range(1, 8) # Initialize data structures profit_loss = [] cashflow = [] # Calculate financials initial_investment = params['initial_investment'] working_capital = params['working_capital'] depreciation = initial_investment / params['depreciation_years'] print("depreciation:", depreciation) revenue = params['annual_revenue'] operating_cost = params['operating_cost'] for year in years: # Profit Loss revenue *= (1 + params['revenue_growth']) operating_cost *= (1 + params['cost_growth']) ebitda = revenue - operating_cost ebit = ebitda - depreciation tax = max(ebit * params['tax_rate'], 0) net_income = ebit - tax profit_loss.append({ 'Year': year, 'Revenue': revenue, 'Operating Cost': operating_cost, 'EBITDA': ebitda, 'Depreciation': depreciation, 'EBIT': ebit, 'Tax': tax, 'Net Income': net_income }) # Cashflow cash_in = revenue cash_out = operating_cost + tax if year == 1: cash_out += initial_investment + working_capital if year == 7: cash_in += working_capital # Recovery of working capital net_cashflow = cash_in - cash_out cashflow.append({ 'Year': year, 'Cash In': cash_in, 'Cash Out': cash_out, 'Net Cashflow': net_cashflow }) # Create DataFrames print("proses:", profit_loss, cashflow) pl_df = pd.DataFrame(profit_loss) cf_df = pd.DataFrame(cashflow) # Create an Excel writer object with pd.ExcelWriter('financial_projections.xlsx') as writer: # Write the first DataFrame to the first sheet pl_df.to_excel(writer, sheet_name='Sheet1', index=False) # Write the second DataFrame to the second sheet cf_df.to_excel(writer, sheet_name='Sheet2', index=False) print("Excel file 'output.xlsx' created with two sheets.") # Generate charts pl_chart_base64, cf_chart_base64 = generate_charts(pl_df, cf_df) #return 'financial_projections.xlsx', f"data:image/png;base64,{pl_chart_base64}", f"data:image/png;base64,{cf_chart_base64}" return 'financial_projections.xlsx','pl_chart_base64.png','cf_chart_base64.png' # Gradio interface with gr.Blocks() as demo: gr.Markdown("# Financial Projections for Tea Production Machine Replacement") assumptions = gr.Textbox( label="Input Assumptions", placeholder="""Example format: Initial Investment: Rp 1,000,000,000 Annual Revenue: Rp 500,000,000 Revenue Growth: 5% Operating Cost: Rp 300,000,000 Cost Growth: 3% Depreciation Years: 7 Tax Rate: 25% Working Capital: Rp 50,000,000""", lines=10, value= """Initial Investment: Rp 1,000,000,000 Annual Revenue: Rp 500,000,000 Revenue Growth: 5% Operating Cost: Rp 300,000,000 Cost Growth: 3% Depreciation Years: 7 Tax Rate: 25% Working Capital: Rp 50,000,000""" ) generate_button = gr.Button("Generate Financials") output_file = gr.File(label="Download Excel File") pl_chart = gr.Image(label="Profit and Loss Chart") cf_chart = gr.Image(label="Cashflow Chart") generate_button.click( fn=generate_financials, inputs=assumptions, outputs=[output_file, pl_chart, cf_chart] ) demo.launch()