Andreas's Data Portfolio

Category: Data

  • US-CR Exchange rate analysis for 2025

    US-CR Exchange rate analysis for 2025

    Business task

    This analysis explores the relationship between Costa Rica’s exchange rate, interest rate differentials, and tourism arrivals to determine the optimal timing for purchasing CRC. Using correlation analysis and Granger Causality Tests on data from BCCR, ICT, and FRED, the goal is to help investors and bankers better understand how key economic variables influence exchange rate behavior and enhance their decision-making strategies.

    Data sources used

    The analysis uses publicly available, credible data from official sources:

    • BCCR exchange rate and interest rate datasets
    • ICT tourism arrivals dataset
    • FRED Federal Funds Effective Rate dataset

    The data covers the period from January 1, 2009 to December 31, 2024, organized daily or monthly depending on the source. To ensure consistency, monthly averages were calculated where necessary. All datasets were reviewed and show no missing values. There are no concerns regarding bias, licensing, privacy, or security. These datasets support robust correlation and causality analyses to evaluate the drivers of Costa Rica’s exchange rate.

    Tools used

    For this project, I used Google Sheets and R (ggplot2 and lmtest packages).

    Manipulation of data

    Monthly averages were calculated from BCCR datasets using the formula:

    =AVERAGEIFS(B6:B371, A6:A371, “*Ene*”, B6:B371, “>0”)

    This method excludes zero values typically recorded on weekends or holidays, ensuring that averages reflect active market data only.

    Analysis

    Tourist arrivals peak in January and drop in September. Major economic events—such as the 2008 U.S. financial crisis, 2014 BCCR policy shifts, 2018 fiscal crisis, and 2020 COVID-19 pandemic—corresponded with sharp exchange rate increases, followed by gradual normalization.

    Despite a steady rise in tourism revenue, it has not significantly suppressed the exchange rate, with recent data showing increased tourism alongside a declining exchange rate. Interest rate differentials (CR – US) often rise after exchange rate shocks, suggesting a monetary policy response to attract capital inflows. However, the current historically low differential, paired with a continued decline in the exchange rate, casts doubt on the short-term predictive power of this metric.

    These patterns indicate that neither tourism nor interest rate differentials alone can reliably forecast exchange rate trends, highlighting the need for a more integrated modeling approach when advising on CRC purchasing decisions.

    Granger Causality Test Results:

    • Tourist Arrivals → Exchange Rate: No significant causality (p = 0.1457 at 1 lag).
    • Exchange Rate → Tourist Arrivals: Some reverse causality (p = 0.0839 at 4 lags), suggesting the exchange rate may influence tourism demand.
    • Interest Rate Delta (CR – US) → Exchange Rate: Statistically significant (p = 0.0046), indicating the differential impacts the exchange rate.
    • Exchange Rate → Interest Rate Delta: Strong reverse causality (p = 7.1e-06), suggesting exchange rate changes trigger monetary policy responses.
    • Costa Rica’s Interest Rate → Exchange Rate: Very strong causality (p = 3.4e-08), showing a direct effect of domestic monetary policy on the exchange rate.

    Visualizations

    Further exploration

    • Areas for deeper analysis include:
      • Capital inflows from international loans
      • Unrecorded currency inflows (e.g., narco dollars)
    • Scenario analysis tools to simulate interest rate shocks and their effects on exchange rate dynamics.

    Insights

    • Domestic interest rates are the primary driver of Costa Rica’s exchange rate. Granger tests show strong causality and feedback effects, indicating that monetary policy both influences and responds to exchange rate changes.
    • Tourism has limited influence on exchange rate movements. While some weak reverse causality exists, tourism is not a major determinant of currency fluctuations.
    • Interest rate differentials with the US remain relevant, highlighting the importance of tracking external financial conditions in parallel with domestic policy.

  • Normalized Revenue & Profit Dashboard with Google Sheets Automation

    Normalized Revenue & Profit Dashboard with Google Sheets Automation

    Business task

    This analysis focuses on the accurate prorating of revenue and expense data from different SaaS products and cost sources—whether billed on a monthly basis or on an hourly basis—into a standardized Monthly Revenue and Profit view. By uniformly distributing variable-period flows into calendar months, the goal is to create a reliable profitability dashboard that reflects operational performance across time regardless of billing periodicity.

    This approach enables founders, finance teams, and analysts to make more informed business decisions based on normalized monthly performance, even when real income and costs follow irregular or custom schedules.

    Data sources used

    This project draws from operational and financial data tracked in a structured Google Sheets document:

    • SaaS revenue and contract data (monthly and hourly billing)
    • Regular business expenses

    The data is updated by operators manually and automatically processed through Google Apps Script (JavaScript), which ensures reproducibility and quick updates. The system supports flows with:

    • Defined start and end dates
    • Optional hourly rates
    • One-off income and expenses

    All data is reviewed for accuracy at input and validated by script logic to avoid double-counting and misalignment.

    Tools used

    For this project, I used Google Sheets and Google Apps Script (JavaScript).

    Manipulation of data

    The script employs custom JavaScript functions within Google Apps Script to distribute financial flows into corresponding months. Key preprocessing includes:

    • Calculating the number of billed days per contract
    • Adjusting for edge cases (e.g., partial months, open-ended contracts)
    • Handling both “Hourly” and “Monthly” billing modes by normalizing to a daily rate
    • Using fixed average month length (30.417 days) for consistent prorating across calendar months

    Here’s a simplified view of the main formulaic logic:

    If full-time:
  Daily Rate = Monthly Salary / 30.417
Else if hourly:
  Daily Rate = Total Hourly Revenue / Billed Days

For each month:
  Revenue = Daily Rate × Active Days in That Month

    Analysis

    The script-generated table provides a consistent timeline of monthly performance. Key observations from initial use:

    • Monthly contracts tend to span multiple months, showing consistent monthly revenue streams, but drop off sharply at contract end.
    • Hourly contracts create more variable month-to-month revenue.

    This method ensures that short-term performance shocks (like sudden contract ends) are immediately visible in monthly summaries, supporting proactive decision-making.

    Further exploration

    To extend this analysis, potential improvements could include:

    • Creating cumulative profit dashboards or year-to-date summaries
    • Incorporating forecasting models based on active contracts and churn

    Insights

    This project demonstrates how JavaScript automation in Google Sheets can transform inconsistent financial input into clear, actionable monthly insights. Core takeaways:

    • Prorating revenue and costs accurately allows consistent performance measurement across time
    • Monthly vs hourly models must be handled differently to avoid misrepresentation
    • Automation reduces manual error and makes dashboards easy to update and scale

    By leveraging smart scripting in a spreadsheet environment, teams can bridge the gap between raw business data and strategic insight, without needing external BI tools.

  • Forecasting spare parts demand

    Forecasting spare parts demand

    Business task

    This project, developed in collaboration with a fellow industrial engineering student, focused on designing a predictive inventory management system for spare parts within a complex supply chain environment. The goal was to address low product availability, high inventory holding costs, and reliance on costly emergency orders by applying advanced data analytics to support smarter planning and purchasing decisions.

    Data sources used

    The system was based on structured datasets typically found in spare parts operations, including:

    • SKU-level historical demand (3-year period)
    • Inventory turnover and rotation indicators
    • Cost breakdowns for standard vs. emergency logistics
    • Lead time records by sourcing method
    • Records of expert adjustments to system-generated orders

    All data was anonymized and used solely for academic and methodological purposes.

    Tools used

    For this project, I used Microsoft Excel and VBA.

    Manipulation of data

    To enable a robust analytics foundation, the data was prepared through:

    • Profiling SKUs by criticality, value, sales, and rotation frequency
    • Classifying items with multi-criteria techniques (e.g., ABC-VED hybrid)
    • Smoothing demand signals with methods like weighted moving averages
    • Using Croston-based models for intermittent demand
    • Analyzing overrides of system recommendations to detect bias or inefficiencies

    Analysis

    We designed and tested a modular forecasting and inventory planning system that included:

    1. Demand Forecasting Modules
      • Regular SKUs: Holt-Winters and Croston variants
      • New SKUs: fuzzy logic rules to infer likely demand ranges
    2. Inventory Policy Module
      • Fill rate optimization based on inventory cost tradeoffs
      • Simulation of multiple service level targets
    3. Order Quantity Engineering
      • Adjusted EOQ models for intermittent-demand SKUs
    4. System Evaluation & Simulation
      • Measured fill rate, inventory cost, and logistics method impact
      • Compared historical performance vs. model simulation outputs

    Key results from the simulation:

    • Improved fill rate by over 14%
    • Significant reduction in emergency procurement needs
    • Lower inventory policy cost relative to sales
    • Quantified financial gains using improved forecasting accuracy and order planning

    Further exploration

    Potential directions for further development:

    • Incorporating real-time demand sensing
    • Supplier lead time variability modeling
    • Reinforcement learning to improve order policy tuning
    • Integration into a web-based analytics dashboard for decision support

    Insights

    • Hybrid forecasting methods significantly outperformed traditional models in environments with high SKU variability and intermittent demand.
    • Manual overrides of system suggestions, though well-intentioned, often contributed to inefficiencies—underscoring the value of data-driven decision support.
    • Predictive analytics can be a powerful tool in aligning service goals with cost efficiency in spare parts logistics.