W07 - Conversion Table Names:
Instructions
Using the data below, fill in the yellow cells. Watch the video at W07 Prepare: Semester Project. It will help you. This
assignment asks you to convert the raw materials you purchase into individual unit quantities and costs. Make sure you do
the following:
Each ingredient must be converted from the purchase quantity to the quantity you use per cup of lemonade to how many
servings you could make per purchase.
You must calculate the cost per cup of each ingredient.
Once you have the cost per cup for each ingredient (row), add up how much it costs to make one cup of regular lemonade
and one cup of fancy lemonade.
Please display the 'Purchase Price' and 'Price per Cup' data (each cell in that column) in United States dollars. The data in the
cells should look something like this: “$0.01” not this: “0.00810141”. Do the same thing for the total cost per cup.
Helpful Links:
[Article] How to format numbers as currency
[Video] How to Save and Close a Workbook in Excel (01:33 mins) Transcript
[Video] How to Adjust Column Width in Excel (02:11 mins) Transcript
Regular Lemonade Fancy Lemonade Recipe Material Costs Unit Conversions
Recipe (Per Cup) (Per Cup) Sugar (10 lbs(pounds)) $5.88 108.87 tsp = 1 pound of sugar
1 Cup Water 1 Cup Water Lemons (1 box) $3.98 120 ice cubes = 1 bag
Lemon Lemon
0.5 0.5 Ice Cubes (1 bag) $2.50 12 lemons = 1 box of lemons
(Squeezed) (Squeezed)
1.5 tsp Sugar 1.5 tsp Sugar Paper Cup (1 case) $10.48 6 tsp = 1 ounce
4 Ice Cubes 4 Ice Cubes Fruit (12 ounces) $4.98 768 tsp = 1 gallon syrup
2 tsp Flavor
1 Paper Cup Syrup Flavor Syrup (3 gallons) $38.68 240 fancy cups = 1 case
3 tsp Fruit Fancy Cup (1 case) $21.47 264 paper cups = 1 case
1 Fancy Cup
Regular Lemonade Recipe (1 Cup)
Item Quantity Purchase Purchase Quantity Ingredient Conversion Factor: Individual Cost Per Cup
Purchased Units Price Needed Per Units Purchase Units to Quantities
Lemonade Ingredient Units Available
Sugar 10 pounds $5.88 1.5 teaspoons 108.87 725.8 $0.01
Lemons 1 box $3.98 0.5 squeezed 12 24 $0.17
Ice Cubes 1 bag $2.50 4 Ice cubes 120 30 $0.08
Paper Cups 1 case $10.48 1 paper cup 261 261 $0.04
Total Cost Per Cup #NAME?
Fancy Lemonade Recipe (1 Cup)
Item Quantity Purchase Purchase Quantity Ingredient Conversion Factor: Individual Cost Per Cup
Purchased Units Price Needed Per Units Purchase Units to Quantities
Lemonade Ingredient Units Available
Sugar 10 pounds $5.88 1.5 teaspoons 108.87 725.8 $0.01
Lemons 1 box $3.98 0.5 squeezed 12 24 $0.17
Ice Cubes 1 bag $2.50 4 Ice cubes 120 30 $0.08
Fruit 12 ounces $4.98 3 teaspoons 6 24 $0.21
Flavor Syrup 3 gallons $38.68 2 teaspoons 768 1152 $0.03
Fancy Cups 1 case $21.47 1 cup 240 240 $0.09
Total Cost Per Cup #NAME?
W08 - Graphing Sales Data Names:
Instructions
Create a bar graph in Excel using the data from the table “Average Number of Cups Sold per Lemonade Stand.”
Insert it in the 'Graph Area' at the bottom of this sheet. Your bar graph should have the following attributes:
Correct Axis and Labels. The X-axis should be months and the Y-axis should be the number of units sold. Include
axis labels on your chart. Include a title for your chart.
Include All Three Markets: Include sales data for all three markets in the same chart.
TIP: Everyone in your group will need to reference your graph to answer questions in the personal reflection.
Make sure everyone has a copy of it.
Average Number of Cups Sold per Lemonade Stand
Month Neighborhood Event High-end
January 254 6,854 10,254
February 568 8,541 10,542
March 985 10,528 10,254
April 1,858 12,584 11,525
May 2,985 14,010 12,524
June 3,854 16,585 13,250
July 2,858 18,955 13,589
August 2,985 20,584 13,558
September 3,521 14,586 12,958
October 2,515 11,584 11,901
November 1,958 9,514 13,201
December 689 7,102 11,255
Graph Area
W10 - Break-Even Analysis Name:
Instructions
Complete the steps below by filling in the yellow cells. If you need additional help and videos, see W10 Prepare: Semester Project.
Locate your submission for Week 08 Management Framework Part 1. On that document, you wrote your strategy as a response to question 4.
Copy and paste your strategy into the space provided below.
Select your market by clicking on the cell and choosing a market from the drop-down menu. You chose your market in Week 8 with your team
and may have adjusted it in Week 9. Now, stay with this market for the rest of the semester project. Notice the following will automatically
update: 'Fixed Costs per Month' table; Yearly Cost & Revenue chart and data table
Enter your annual fixed costs (the monthly fixed costs are found in the tables below).
The Variable Costs value is brought from the W07 Coversion Table tab, Total Cost Per Cup.
Look at the Price Demand Graph at the bottom of this sheet. Choose a price per cup for your chosen market, then find the corresponding
percent of demand. If you need help, refer to the video called "Price" at W10 Prepare: Semester Project.
Enter the price per cup you chose as well as its associated percent of demand.
Try different combinations of price and demand (referencing the graph) until you find a combination that matches your strategy (for example,
maximize profit, or maximize sales, etc.).
Calculate the break even point using the instructions provided. You will use this information later in the project.
Scroll to the bottom of this tab and answer the reflection question.
Save and submit your file at W10 Application Activity: Semester Project.
Copy and paste your strategy here (from your Week 08 Management Framework Part 1)
1. Your Strategy
2. Your Market Choose your market 0
Fixed Costs per Month
3. Annual Fixed Costs 0 $0.00
Variable Costs $0.00 0 $0.00
Price 0 $0.00
5.
% of Demand
Break Even Point*
*Using Excel, calculate the break even point to find out
7. how many units you'll need to sell to cover your fixed costs.
Make sure you put parentheses around price and variable
cost.
Choose your market Yearly Cost & Revenue
$1 Monthly Price Adjusted
Demand Demand
Choose Your #VALUE!
Market
Choose Your
Market #VALUE!
Choose Your
#VALUE!
Market
Choose Your #VALUE!
Market
$
Choose Your
$1 Market #VALUE!
Annual Revenue Choose Your
#VALUE!
Annual Costs Market
Choose Your #VALUE!
Market
Choose Your
Market #VALUE!
Choose Your
Market #VALUE!
Choose Your
#VALUE!
Market
$0 Choose Your
Market #VALUE!
0 #VALUE!
Choose Your
Market #VALUE!
Units
Begin End
Total Units sold 0 #VALUE!
Annual Costs $0 #VALUE!
Annual Revenue $0 #VALUE!
Net Profit #VALUE!
Profit Margin #VALUE!
W11 - Profit Graph Name:
Instructions
Complete the steps below by filling in the yellow cells and making a chart. If you need additional help and videos, see W11
Prepare: Semester Project.
The cost and revenue equations are filled out for you using data from the tab called "W10 Break-Even Analysis." Make sure that
the data here is correct and what you want.
Create your profit equation in the space provided. Note that is will not be an Excel formula. Start it with "Profit = ".
From your profit equation, complete the data table.
Begin by putting 0 in the first cell of the "x" column. If you are doing the neighborhood market, increase the rest of the cells in
the "x" column by 1,000 each. If you are doing the event or high-end markets, increase the rest of the cells in the "x" column by
5,000 each.
Next, place an Excel formula in the first cell of the "y" column. Use that same formula for the rest of the cells in that column. If
you are unsure about how to do this, please review the video called "How to Graph an Equation in Excel."
Insert a scatter plot chart with a line that is based on the data table to show your profit equation
Annual fixed costs must be negative on the y-axis
Break-even should be the x-intercept
Uses informative labels and titles
Scroll to the bottom of this tab and answer the reflection question.
Save and submit your file at W11 Application Activity: Semester Project.
1. Cost Equation; Revenue Equation Choose your market
Cost = Annual fixed cost + (Variable cost per cup) (Number of units sold)
$0.00 + $0.00 x
Revenue = (Price per cup of lemonade) (Number of Units Sold)
$0.00 x
2. Profit Equation
Profit = Revenue - Cost = $0.00 x - ( $0.00 + $0.00 x )
Hint: Remember you must distribute the minus to everything in
the parentheses.
3. Data Table
x y
43
44
45
46
47
48
49
4. Scatter Plot Chart Area
50
51
52
53
54
55
56
57
58
59 Reflection Question
60 You now have more data and experience than you had when you first created your
61 strategy. Does your price per cup you used on the Profit Graph match your strategy
(maximize sales vs. profit) and mission? Why or why not? Give at least one reason.
62
63 Write your response here.
64
65
66
67
68
69
70
71
72
W12 - Choose your market Income Statement Name:
Instructions
This tab uses information from the tab called "W10 Break-Even Analysis." Make sure that the data there is correct and what you want.
You will need to create a forecasted monthly income statement in Excel based on the projections that you’ve created the last few weeks. Normally, income statements show actual
performance not forecasted values, but in our project, you’ll use forecasted values. Also note that the loan is being used to pay for your operating expenses, so when you think about paying
for your operating expenses, this should be thought of as a loan payment.
Complete the steps below by filling in the yellow cells. Use formulas and cell references in as many yellow cells as possible. View the instructions at W12 Prepare: Semester Project for help.
The variable cost and price have been automatically filled in based on data in the "W10 Break-Even Analysis" tab.
Calculate the sales revenue for each month and for the year. This is the price per cup of lemonade multiplied by the number of units sold.
Calculate the variable costs for each month and for the year. This is the variable cost per cup of lemonade multiplied by the number of units sold.
Calculate the gross profit for each month and for the year. This is the sales revenue minus the variable costs.
Calculate the total operating expenses each month and for the year. This is the sum of the operating expenses. Note that the monthly interest expense will be automatically updated once
you perform the loan calculations below. Use the SUM function to add the cell references.
Calculate the net income for each month and for the year. This is the gross profit minus the total operating expenses.
Note that the loan amount is automatically filled in based on the market you have chosen.
Choose the loan term. Click on the blank cell to choose from the dropdown menu.
Note the annual interest rate and period rate are automatically filled in based on your loan term selection above.
Calculate the loan elements as instructed on the right of each yellow cell.
Answer the reflection question.
Variable Cost per Cup of Lemonade: $0.00
1.
Price per Cup of Lemonade: $0.00
% of demand: 0%
Income Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total
Units (Cups of Lemonade) Sold #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
2. Sales Revenue
3. Variable Costs
4. Gross Profit
Operating Expenses (fixed costs)
0 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00
0 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00
0 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00
Average Monthly Interest Expense $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00
5. Total Operating Expenses
Net Income
6.
Loan Calculations 11. Reflection Questions
7. Loan Amount (pv) $0.00 What effect does the length of a loan (Loan Term) have on monthly payments and interest?
Write your response here.
8. Click on the yellow cell to select from the drop
Loan Term or Number of Years down. See the optons in the table below.
Annual Interest Rate 0.0%
9.
Payments per Year 12
Period Rate (rate) =annual interest rate/number of payments per year
Total Number of Payments (nper) =number of payments per year*number of years
Monthly Payment =use the PMT function = PMT(rate,nper,pv)
10.
Loan Amount with Interest =monthly payment*nper
Interest Expense =loan amount with Interest + loan amount
Average Monthly Interest Expense =interest Expense/nper What is the best loan option for your lemonade stand? Why?
Write your response here.
Small Business Loan Information
Loan Term (number of Years) Payments per Term (year) Annual Interest Rate
1 12 4.5%
2 12 5.6%
3 12 6.2%
