Revenue-Cost Forecast with Sales Channel

AnalyticsComputational Methods in Civil Engineering Project

Opus Raven• An aggregator of Libraries- Bridge between Physical book users and

Libraries in a locality• Hyper Local Business Model: Location-Dependent• In-house Logistics and Supply Chain Team• A delivery system similar to Grofers: Fixed-time, Everyday Delivery• Nil Ownership of Inventory• Revenue on the basis of Commission from private tied-up libraries• Sales through Website, Mobile App and Toll-free call booking.

Revenue- Cost Model• This project involves the forecast of revenue and cost numbers• Cost divided into four Main Categories:

• Employee Costs• Marketing Costs• One-Time Costs• Other Costs

• Revenue through Four Channels:• Customer Call/ Website Booking• Monthly Pack Sales• Half Yearly Pack Sales• Student Pack Sales

General Costs: Market ResearchFY17

Employee Costs ₹ 5,820,000

Marketing Costs ₹ 3,480,000

One-Time Costs ₹ 646,500

Other Costs ₹ 930,000

Total Costs ₹ 10,876,500

FY18-FY21

Employee Costs ₹ 5,820,000

Marketing Costs ₹ 3,480,000

Other Costs ₹ 930,000

Total Costs ₹ 10,230,000

• One-Time costs need to be spent only at the beginning of the first fiscal year• Total Costs from FY18 to FY21 is almost the same excluding factors such as inflation and

exchange rates.• Data was obtained after detailed market research regarding marketing costs(digital marketing,

newspaper ads) and employee costs. Prices might change from market to market.

Costs: Spending Mediums FY17• The distribution of costs is

displayed in the pie chart• It helps in decreasing costs

from specific cost types• Suggests the most efficient

spending channels and return from each cost category

• One-time costs spent only at the start of first Fiscal Year

Costs: Spending Mediums FY18 to FY21

• Depicts the change in cost distribution from FY17 to FY18

• The costs are calculated from market research and effects of inflation and exchange costs are not considered

Revenue and Sales Mediums• The code is based on user inputs• User Inputs include first month of FY17 sales, first three month increment in

sales, following six month increment in sales, last three month increment in sales and Sales multiplication factor between two consecutive fiscal years• The function for sales Calculation from user inputs:

• function [ z1 ] = sales( strt_value1,k1,k2,k3,k4 )• Strt_value1: First month sales from FY17• k1: First Three month increment in sales• k2: Fourth to Ninth Month increment in sales• K3: Last three month increment in sales• K4: Sales multiplication factor between two consecutive fiscal years• z1: Matrix with the sales of Every month of FY17 and Total sales of FY17 to FY21

Different User Input ValuesCase 1 Cust_call Monthly Pack Half Year Pack Student Pack

First Month sales (FY17) 600 100 75 150

1-3 month increment 250 50 50 50

4-9 month increment 300 50 50 50

10-12 month increment 400 50 50 50

Yearly Multiplier 1.2 1.2 1.2 1.2

Case 2 Cust_call Monthly Pack Half Year Pack Student Pack

First Month sales (FY17) 500 75 100 100

1-3 month increment 150 25 25 50

4-9 month increment 200 25 25 50

10-12 month increment 250 25 25 50

Yearly Multiplier 1.2 1.2 1.2 1.2

Case 3 Cust_call Monthly Pack Half Year Pack Student Pack

First Month sales (FY17) 400 100 75 100

1-3 month increment 100 25 25 25

4-9 month increment 150 25 25 25

10-12 month increment 200 25 25 25

Yearly Multiplier 1.2 1.2 1.2 1.2

Revenue –Case1

Revenue- Case2

Revenue- Case3• The three different

sales combinations allows us to compare which sales channel gives maximum revenue

• It gives the analytics of which sales channel is more efficient and revenue-pulling

Revenue-Case Comparisons (Bar Charts)

Revenue Case1- Cost Comparison (Bar Chart)

The comparison of revenue and cost for the first fiscal years for case 1 values.

Revenue Case2- Cost Comparison (Bar Chart)

The comparison of revenue and cost for the first fiscal years for case 2 values.

Revenue Case3- Cost Comparison (Bar Chart)

The comparison of revenue and cost for the first fiscal years for case 2 values.

Customer Analytics- Case 1• The distribution of customers

from different sales packages is depicted in this pie chart

• This analytics helps in understanding which sales medium is lacking behind and which sales medium gives maximum returns

Customer Analytics- Case 2

Customer Analytics for Case2 Sales.

Customer Analytics- Case 3

Customer Analytics for Case3 Sales.

Break-Even Estimation• Break-Even is the point where the revenue and cost curves meet on the

axis of time• It is very important in knowing the time of occurrence of break-even to

run a business successfully• Break-Even occurrence time gives an estimation of the funding required

for all the costs incurred• It is calculated by drawing the graph of revenue(starts from origin) and

cost in the same plot against time• The point where revenue exceeds cost is the break-even point and the

business starts to earn profits from this point

Break-Even Point Estimation Plot

costfy17=cost_fy17();costfyrest=cost_fyrest();sales_cust1=sales(600,250,300,400,1.2);sales_mon1=sales(100,50,50,50,1.2);sales_half1=sales(75,50,50,50,1.2);sales_stud1=sales(150,50,50,50,1.2);cust_call1=revenue(sales_cust1,40);mon_pack1=revenue(sales_mon1,200);half_pack1=revenue(sales_half1,500);stud_pack1=revenue(sales_stud1,150);sales_cust2=sales(500,150,200,250,1.2);sales_mon2=sales(75,25,25,25,1.2);sales_half2=sales(100,25,25,25,1.2);sales_stud2=sales(100,50,50,50,1.2);cust_call2=revenue(sales_cust2,40);mon_pack2=revenue(sales_mon2,200);half_pack2=revenue(sales_half2,500);stud_pack2=revenue(sales_stud2,150);sales_cust3=sales(400,100,150,200,1.2);sales_mon3=sales(100,25,25,25,1.2);sales_half3=sales(75,25,25,25,1.2);sales_stud3=sales(150,25,25,25,1.2);cust_call3=revenue(sales_cust3,40);mon_pack3=revenue(sales_mon3,200);half_pack3=revenue(sales_half3,500);stud_pack3=revenue(sales_stud3,150);rev_cost_compare1=Revcost_bar(cust_call1,mon_pack1,half_pack1,stud_pack1);rev_cost_compare2=Revcost_bar(cust_call2,mon_pack2,half_pack2,stud_pack2);rev_cost_compare3=Revcost_bar(cust_call3,mon_pack3,half_pack3,stud_pack3);revenue_distribution1=revenue_distribution(cust_call1,mon_pack1,half_pack1,stud_pack1);revenue_distribution2=revenue_distribution(cust_call2,mon_pack2,half_pack2,stud_pack2);revenue_distribution3=revenue_distribution(cust_call3,mon_pack3,half_pack3,stud_pack3);combined_revenue=revenue_comparison1(cust_call1,mon_pack1,half_pack1,stud_pack1,cust_call2,mon_pack2,half_pack2,stud_pack2,cust_call3,mon_pack3,half_pack3,stud_pack3);revenue_bar(combined_revenue);revenue_cost_plot(cust_call1,mon_pack1,half_pack1,stud_pack1,cust_call2,mon_pack2,half_pack2,stud_pack2,cust_call3,mon_pack3,half_pack3,stud_pack3);sales_channel_distribution=cust_analytics(sales_cust1,sales_mon1,sales_half1,sales_stud1);sales_channel_distribution=cust_analytics(sales_cust2,sales_mon2,sales_half2,sales_stud2);sales_channel_distribution=cust_analytics(sales_cust3,sales_mon3,sales_half3,sales_stud3);

Thank You

Project by:- Goutham (ce12b1018)- Mayank (ce12b1013)