The 5 questions of this test are related to the information given below:

I want you to NOTE that six inputs in your data model will KEEP CHANGING from one question to the next, and from one submission to the next. So, keep these six inputs at the TOP of your data model, and you should be able to copy and paste from blackboard to excel.

The value of these SIX inputs for THIS QUESTION, are:

Unit Cost	$11.46
First Year Unit Price	$21.76
First Year Hourly Salary	$13.70
First Year Demand	18732
First Year monthly Marketing	$5,000
Monthly Rent	$3,000

Now, here is your Business Plan:

(Don't start creating anything in your spreadsheet as yet. Just read, and understand. Wait till you get to the STEPS below.)

You are thinking about starting a flower store in Syracuse. Customers will come to your store to buy different floral arrangements. You are estimating that in the first year, you will sell 18732 floral arrangements whose average Unit Cost will be $11.46, and the average Unit Price in the first year will be $21.76

To start this business, you are going to have capital expenditure (walk-in refrigerators, automatic watering, Point of Sale systems, hardware, software) of $500,000, which you are going to depreciate over 5 years. (This means that in your spreadsheet, you will have a row called Annual Depreciation Expense, and each year you will have a Annual Depreciation Expense of $500,000 / 5 = $100,000. (So, even though you have a large expense BEFORE you start your business, you will be representing it by spreading out that large expense, over 5 years in your financial spreadsheet.)

Your rent will be $3,000 per month, and due to a signed lease, it will be the same for the first 5 years.

You will be paying your employees $13.70 per hour, in the first year. Your store will be operational 12 hours per day, 6 days a week, for 50 weeks a year, and will be manned by 3 employees at all times. Your utilities will be $500 per month. Your marketing costs will be $5000 per month. As you get better in your business, you believe that your average Unit Price will increase by 4% every year (in other words, you will be able to charge MORE, for your product). You believe that salary will be increasing 3% every year. You think that demand will increase by 30% every year. To sustain this growth, you plan to increase your marketing budget by 20% every year.

Now that I have given you all the information, you are ready to create your financial spreadsheet for your business. Here are my steps, that you should follow:

Click HERE to see an image of the Input Data Model Structure

Step 1. I am going to ask you to create an INPUT DATA MODEL, with 17 (seventeen) items. I am telling you what they are, and the order in which they should be written, and in what units they are in. DON'T DEVIATE FROM THESE INSTRUCTIONS! I want you to start with the SIX entries at the very top, which we know will keep changing in blackboard, followed by the remaining inputs. So, the list is: Unit Cost (The cost of producing ONE unit in dollars), First Year Unit Price (The selling price of ONE unit in dollars during the first year), First Year Hourly Salary (The salary PER HOUR during the first year), First Year Demand (The number of units sold during the first year), First Year Monthly Marketing (monthly marketing expense during first year), Monthly Rent (rent expense for a month in dollars), Monthly Utilities (Utilities expense in a month), Capital Expenditure (What is the total capital expenditure), Depreciation over number of years (Over how many years are you planning to depreciate that capital expense), Growth in Demand in % (what is the Demand increasing by, every year, in percentage), Growth in Unit Price in % (what is the Unit Price increasing by, every year, in percentage) , Growth in Salary in % (what is the Hourly Salary increasing by, every year, in percentage) , Growth in Marketing in % (what is the Marketing budget increasing by, every year, in percentage) , Number of hours store is open per day, Number of Days store is open per week, Number of weeks store is open per year, Number of employees

So, in the end, you will have 17 entries in the INPUT DATA MODEL. It will be good to use "Name Manager" to give names to the above 17 entries.

If you have not done it as yet, please go ahead and FILL IN the values for these 17 inputs. (NOTE: YOU SHOULD NOT DO ANY CALCULATIONS IN THIS SET OF 17 INPUT VALUES)

Step 2. Now, you want to create a financial spreadsheet over a 5 year period. Make it a YEARLY spreadsheet. DO NOT make it monthly. So, you will have a column for all your labels, followed by 5 columns, headed with 1,2,3,4 and 5.

NOW REMEMBER MY MOST IMPORTANT RULE: DO NOT HARD CODE ANY VALUES IN YOUR FORMULAS. ALWAYS POINT TO THE INPUT DATA MODEL.

Click HERE to see an image of the SpreadSheet Structure

Step 3. Ready to compute the Intermediate Variables? Let us start with the first four that change every year. They are: Demand in each year, Unit Price in each year, Hourly Salary in each year, Monthly Marketing in each year. So, calculate these values for Year 1 and Year 2. Remember to lock anything that needs locking. Once you have the correct formula in Year 2, then you should be able to EXTEND them through the rest of the years. (Hint: DO NOT multiply anything by 12 in this step). So, for instance, if the Hourly Salary in Year 1, is $10, and the increase was 5%, then the Hourly Salary in Year 2, will work out to $10.50

Step 4. Now, list the ANNUAL Expenses, one by one. You should be able to find SIX expenses. Expenses are everything that the company spends money on. They are: Annual Variable Cost Annual Rent Annual Salary for all employees Annual Utilities Annual Depreciation Expense Annual Marketing

Lock anything that needs to be locked, and remember my important rule above. (don't hardcode - point to the Input Data Model)

Step 5. Now, list any Income. This will be "Revenue" - and there is only one source of revenue.

6. Almost done. Ready to compute the Profit for each year!

Make sure you have ended up with a spreadsheet that you are proud of! You will be changing some values in the Input Data Model, and pulling out information from the spreadsheet.

The value of the SIX inputs for THIS QUESTION, are:

Unit Cost	$11.46
First Year Unit Price	$21.76
First Year Hourly Salary	$13.70
First Year Demand	18732
First Year monthly Marketing	$5,000
Monthly Rent	$3,000

Now, answer the following question: What is the Demand in Year 2?

(Provide the answer to ZERO places of decimal. So, for instance, if the answer is 3,220.85, then provide the answer as 3221, or as 3,221 - the commas are OK)

2 points

QUESTION 2

This question uses the financial spreadsheet created in Qn1.

The value of the SIX inputs for THIS QUESTION, are:

Unit Cost	$10.00
First Year Unit Price	$29.93
First Year Hourly Salary	$10.00
First Year Demand	15,000
First Year monthly Marketing	$5,000
Monthly Rent	$3,000

Now, answer the following question: What is the Unit Price in Year 2?

(Provide the answer to TWO places of decimal. So, for instance, if the answer is $3,220.85, then provide the answer as 3220.85, or as 3,220.85 - the commas are OK)