ask Description

.

You are the CFO of a start-up company called Trucks2Go. You are responsible for the cost-benefit analysis of the business plan. Your company is preparing a business plan to get funding or investment such as from Shark Tank, angel investors and other venture capitalists. The company's business model is essentially serving as an "Uber" platform for truck rentals, pairing together people who have trucks (pickups, vans, and even larger vehicles like moving vans) with people who need a short-term rental. Your task in this assignment is mainly concerned with the revenue model of Trucks2Go, which takes data input from other elements of Truck2Gos business model. Your company provides detailed description of Truks2Gos value proposition, competitive analysis, and marketing strategies in other sections of the business plan.

[ Note: all numbers and required calculations in this assignment are simplified to suit the purpose of this assignment. In your group project, you will need to provide detailed justification and calculations for any numbers you include in your cost-benefit analysis, based on your value proposition, marketing strategies and your competitive analysis. ]

A. Cost Assumptions:

The scope of the business is in the city of Los Angeles. Trucks2go needs funding for setting up the Inspection Center (for interviewing its gig-workers and inspecting their vehicles), the Trucks2go app, the website development, marketing, personnel, etc. The following are the cost assumptions: (detailed justification and calculations for each number is provided elsewhere.)

1. The founders of the company have secured a small business startup loan to start the company. The loan is secured on the physical assets of the Inspection Center and a founders personal assets. The loan will cover the initial investment of $120,000. in the Inspection Center and initial application development $200,000 which is planned to be outsourced. Its a 5 year loan, with an annual interest rate of 5.5% and monthly payment is scheduled for payback.
2. Initial website development will cost $300,000 in the first year.
3. Initial eCommerce back-end Database System development including cloud hosting services: $1,000,000.
4. Operation cost of Trucks2go system, including website/app/back-end upkeep and Admin personnel will be a total of $300,000 in the first year because it will only be operational for 3 months in a small test market. It will cost a total of $800,000 for Year 2, and this is projected to increase by 50% per year over the previous year for Year 3, Year 4 and Year 5.
5. Additional Infrastructure costs (such as building rental, office equipment, etc.): first year will be $100,000, and this is projected to increase by 30% per year over the previous year for Year 3, Year 4 and Year 5.
6. Additional Personnel costs: first year will be $200,000 (as all executives are not taking salary), the second year will be $800,000 and this is projected to increase by 50% per year over the previous year for Year 3, Year 4 and Year
7. Marketing costs: these are estimated to be $500,000 for the first year, and will increase: by 10% for Year 2, by 15% over Year 2 for Year 3, by 20% over Year 3 for Year 4, and by 20% over Year 4 for Year 5.

B. Benefits Assumptions:

Based on substantial market analysis and surveys. There are two major revenue streams

1. Transaction fees: Trucks2go plans to take 35% cut of the transaction amount which is on part with Ubers cut. Based on their extensive marketing research, transaction total is estimated at $300,000 in the first year as the company is just testing out the market and rolling out the app. The second year is estimated to be $7 million, and will continue to increase: by 50% over Year 2 for Year 3, by 60% over Year 3 for Year 4, and by 40% over Year 4 for Year 5.

2. Affiliate marketing revenue: It is estimated that the company will only begin to have an affiliated marketing revenue of $10,000 in the Year 2 and estimated to increase each year over the previous year by 20%.

Assignment Deliverables

You will create your analysis using 5 worksheets contained in a single spreadsheet. You must parameterize each variable (e.g., define and label each variable outside of a formula) in the spreadsheet for easy what-if analysis and also for readability of your spreadsheet. Remember, many other people will be reading and using this spreadsheet!

1. Cover Page:

Use a textbox to specify the title of your report, your name, and the date. 2. Executive Summary:

Summarize the purpose of the analysis, the content of your worksheets, and your final recommendation in terms of whether this is a good business plan. Indicate that the recommendation has included the results of your what-if analysis (see below).

3. Cost-benefit Analysis:

This sheet should include:

• An appropriate title for this analysis, using a textbox.
• Clear documentation of cost and benefit assumptions, using textboxes.
• The structure of the cost-benefit analysis should be similar to--but not exactly the same as--the one on page 353 of the course textbook. A sample layout is given on Weekly Schedule: C-B Sample Format
• The interest cost for each year in your calculation. (Hint: your monthly payment is not your cost).
• A clearly indicated breakeven year, using if-else function. No hand-input values. Print Breakeven year below that cell of the breakeven year only.
• A break-even analysis chart. Use the Chart functionality in Excel.
• A professional recommendation to the CIO regarding your proposal based on the cost-benefit analysis (and this should also be included in the Executive Summary). Do you recommend this investment?

4. Loan:

• Must refer to the Cost-benefit analysis sheet for the total amount of loan.
• You must use Excel's PMT function for the calculation of the monthly payment for the loan.
• An amortization table must be created, including the yearly interest cost for the loan.

5. "What-If" analysis: What would be the effects on the breakeven point if the marketing costs were increased to 2 million in the first year, and this resulted in increased sales of 30% in year 1 and year 2 (with all other increases remaining the same). Would you change your recommendation?

Copy the entire Cost-benefit Analysis worksheet, including the breakeven chart, documentation, etc.

Change the Marketing Costs and resulted increase sale percentage. Highlight your changes using a bold red font on this worksheet.

Describe the result of this what-if analysis. Does this cause you to change your recommendation?

Notes:

1) Your logic must be clear: a reader (e.g., a potential investor or executive) should be able to fully understand the logic of your spreadsheet without having to read the formulas hidden in each spreadsheet cell. The parameterization of variables as well as the clear naming of each variable is essential here for readability. Think of this Assignment 1 as a business case presentation and use your judgment to make the presentation attractive and easy to understand.

2) Your logic flow must utilize Excel capabilities, such as relative references, absolute references, and cross-worksheet references. DO NOT copy/paste values from one cell or one sheet to another--use references!

3) Differentiate inputs (original or formula): Use a light green colored background in cells that require manual data entry; use a light blue colored background in cells with formulas; use default coloring in cells with text information.

4) You must provide documentation within each of the worksheets for readability and communications for executive decision making. Documentation includes explanations of formulas, definition of terms, cost and benefit assumptions, an explanation of the chart, an explanation of if-else logic in your calculations, etc.

Submission:

Submit via Laulima in the Assignments area under Assignment 1. Two submissions are required:

1. Your spreadsheet named: LastName_FirstName_ASST1.xlsx

2. A PDF of your spreadsheet which shows that your spreadsheet in the printed form is readable and well-organized. Not readable PDF will result in 10 points deduction of this assignment (100 points).