MGS 3100 Business Analysis

Project 1: Profit Modeling

You are considering starting a new business, the XYZ day care facility (make up a name for the business) for children under the age of 5. You have looked at a space with building that you can purchase for about $200,000. The exact price may change upon negotiation. You will have to spend $XX,XXX.XX (make up a 5 digit dollar figure with two decimals for cents) to make modifications, like adding a playground set. You also have several other expenses to consider. There will be an annual advertising expense of $XX,XXX.XX. Initial supplies for the daycare, including toys, teaching materials, office supplies, bathroom supplies, etc., amount to $XX,XXX.XX. There must also be a budget for monthly replenishment of those same supplies ($XX.XX per child – make up a number between 10 and 15 dollars per child). Legal fees to set up the corporation to do business will cost you ($X,XXX.XX). You have to provide milk and snacks for the children, and that costs you $100 per month for every 5 children enrolled (for example, if you have 6-10 children, the cost is $200). Utilities cost $150 per month.

You will be the primary caretaker of the children, and you want to get a salary of $XX,XXX.XX per year. You can handle up to 5 children on your own, but when the number goes beyond 5, you need an assistant that you pay $9 per hour. Assume she will work 8 hours a day, 25 days a month. If your day care expands enough to have more than 20 children, you will need a receptionist to handle administrative tasks, who is paid $10 per hour. Assume you only need the receptionist half time (4 hours per day). Your facility is permitted to have no more than 36 children based on the city regulations for that space.

Your fees amount to $ XXX.XX per child per month (make up a number between $700 and $1000). Children may stay from 8:30 AM until 5:00 PM with the standard monthly fee. If children come earlier in the morning or stay longer in the evening, they are charged at the rate of $10 for each additional hour or part thereof. So if a child is dropped off at 8:10 and picked up at 5:20, the parents still pay an extra $20. On average, you estimate that a third of the students you have will stay on an extra hour, and a fourth of the students will arrive an hour early in the morning. The facility is only open from 7:30 AM until 6:00 PM.

Your daycare will also do a fundraiser twice a year, consisting of an auction to parents of things made by the children. The auction will expected to raise on average $ XXX.XX per child that is enrolled.

Assume that you are going to borrow 90% of the initial money you need (building, advertising, supplies, etc.) from a bank at 4%, payable over 15 years. The other 10% is money you are putting into the business from your savings. Use the =PMT() function in Excel to figure out the monthly payment to the bank.

Your job for the Project

1. Draw an influence diagram detailing the different Revenues and Costs. Make sure that the details on the diagram match the basic inputs in your spreadsheet. You can draw either in Word or Excel, using the drawing tools built in. (Insert/Shapes).

2. Create a spreadsheet file with two worksheets (Analyze all data on a monthly basis):

a. In the first, analyze the Revenues, Costs, and Profit for various levels of possible enrollment, based on all the data given above. The spreadsheet must be neat, easy to read, and well designed (do not embed numbers in formulas – list the basic inputs at the top, with proper labels). Find the breakeven point from your spreadsheet analysis.

b. In the second worksheet, assume that the demand for enrollment depends on the price you charge, according to the following equation:

Enrollment = 60 – 0.04p, where p represents the monthly fee. Vary the basic fees between $500 and $1200, compute the corresponding demand, and find the revenues, costs, profits various prices. What is the best price to charge?

3. Write a report that shows your analysis and interpretation. It must have the following sections:

a. A cover page with title, names of all the team members, and a distribution of points for each member based on participation. If each person contributes equally, each is to be allocated 100 points. If in a team of 3, one person does less and another covers up for him/her, the points may look like this 120,100, 80 (or some other combination that the team decides on). These are peer review points, and I will use those to adjust individual grades on the project.

b. Introduction: Begin with an introduction of what the report is about, why you are thinking of starting this business.

c. Executive Summary of your findings – what was the breakeven? Is the business worth starting? What fees would you recommend charging based on your analysis?

d. Assumptions in your model: What basic costs and revenues did you assume? Remember that all the numbers provided in this project (including the ones you made up) are assumptions in the model. List every single one, appropriately labeled. You can discuss how the numbers were obtained, in order to lend credibility to them (you can make up a story here – “The professor gave me the numbers” is not an acceptable answer!). Think of how you might have got the numbers if you were to actually start such a business.

e. Analysis: Show summaries (tables and charts) of your spreadsheet analyses. Do not include every single detail from the spreadsheet. Interpret what you see in the tables or charts.

f. Conclusion/Limitations: Discuss the kinds of things that might occur to make your analysis results less reliable. It is important to recognize limitations of your own analysis, so that you have a realistic view of what could happen in a business.

Deliverables

Turn in the Word file of the report and the Excel Spreadsheet (with one of the files also containing the influence diagram), by emailing them as attachments.

Alternative:

If you are keen on a different business rather than a daycare and would rather analyze that, you may do so, as long as the analysis meets the level of complexity of this assignment. You must show more than one source of revenue, variable costs and fixed costs, as in this project.