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 18,555 floral arrangements whose average Unit Cost will be $10.14, and the average Unit Price will be $28.18

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 Depreciation Expense, and each year you will have a Depreciation Expense of $500,000 / 5 = $100,000

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 $11.96 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. 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.

Take all the information above, and create a Data Model, which will hold all your input variables. Make sure it at least contains entries for Yearly Demand, Unit Cost, Unit Price, Hourly Salary, Monthly Marketing,and Monthly Rent

Then, create a financial spreadsheet over a 5 year period. Make it a YEARLY spreadsheet. DO NOT make it monthly.

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

Q2. In this Question 2, your Data Model should have the following values:

Unit Cost: $10.00

Unit Price: $25.37

Demand: 15,000

Salary: $10.00 per hour

Monthly Marketing: $5,000 per month

Monthly Rent: $3,000 per month

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

Q3. This question uses the financial spreadsheet created in Qn1.

In this Question 3, your Data Model should have the following values:

Unit Cost: $10.00

Unit Price: $20.00

Demand: 15,000

Salary: $16.75 per hour

Monthly Marketing: $5,000 per month

Monthly Rent: $3,000 per month

Now, answer the following question: What is the Total Salary Expense in Year 2?

Q4: This question uses the financial spreadsheet created in Qn1.

In this Question 4, your Data Model should have the following values:

Unit Cost: $10.00

Unit Price: $20.00

Demand: 15,000

Salary: $10.00 per hour

Marketing: $8,562 per month

Monthly Rent: $3,000 per month

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

Q5: This question uses the financial spreadsheet created in Qn1.

In this Question 5, your Data Model should have the following values:

Unit Cost: $10.00

Unit Price: $20.00

Demand: 15,000

Salary: $10.00 per hour

Marketing: $5,000 per month

Monthly Rent: $3,900 per month

Now, answer the following question: What is the PROFIT in Year 5? (Please note that this is asking for Year 5, not year 2)