This week in class, we took our first (major) step into the world of financial modeling. We learned to translate the basic unit economics and operating structure of a business into a tunable financial model in Excel.

The world of financial modeling.

HOMEWORK 3: MODEL BUILD Farm2U: Model Build Background This week in class, we took our first (major) step into the world of financial modeling. We learned to translate the basic unit economics and operating structure of a business into a tunable financial model in Excel. Even with this relatively simple level of exposure to the field, we now possess a powerful framework to break down businesses into simple variables, equations, and reports. Your Task In this assignment, you’ll be placed in the role of the financial advisor at a startup incubator.

The world of financial modeling.

In your new role, you will be helping an early-stage business translate their business concept into a testable financial model through which you can help them determine the overall profitability and viability of their operating assumptions. The company you’ve been assigned to support is called Farm2U. Founded by two brothers with agricultural roots, Farm2U aims to help local farmers across the country benefit from the direct-to-consumer subscription trend that has emerged in other fields. Farm2U sells fruit and vegetable subscriptions to consumers. Consumers pay a one-time fee to become a member of the Farm2U network.

The world of financial modeling.

They then pay an upfront subscription fee which covers a set number of produce boxes that will be delivered monthly over a three-year period. Consumers benefit from low-cost, high-quality produce, and farmers benefit from upfront cash for produce to be delivered in the future. Part I: Unit Economics As a first step, open a blank Excel spreadsheet and create a first tab titled: “Unit Economic Model”. Using the information provided by the team in the following bullets, construct a tunable unit economic model for the Farm2U team. Information from the founders: · Our model is a relatively simple one.

The world of financial modeling.

We make money in two ways. Upfront, our consumers pay $250 to become members of the Farm2U network. Upon sign-up, members agree to pre-pay for a set number of boxes over a three-year period. The more boxes they sign up for upfront, the better the price per box. Based on our historic data, on average, customers sign up for 48 boxes, which comes out to a price per box of $65.00. · Our expenses are split into three major categories: Production, Delivery, and Marketing. · We pay roughly $10 per box to farmers to cover their labor in working with us, $15 per box to pay for their products, and $5 on packaging per box.

The world of financial modeling.

We spend about $15 per box on delivery labor and another $10 per box on shipping. · We are big on advertising and use a mix of social media channels and radio. We’d estimate that we are paying about $500 on ads to acquire 1 subscriber. Your unit economic model should capture the above inputs before producing gross margin calculations and expenses as a percentage of revenue. Part II: Unit Revenue and Expense Schedule Next, create a revenue schedule that spans a three-year period starting from the coming month. Then use the information provided by the founders in the next bullet to model your unit revenue and unit expense schedule.   https://youtu.be/RghUyVCSdM4

Attached Files

Download  |