Question
In: Finance
Instructions: Using the data for the assignment complete your spreadsheet and answer the five questions. When...
Instructions: Using the data for the assignment complete your spreadsheet and answer the five questions. When you make the changes for question 4 and 5 your spreadsheet will change and your spreadsheet answer from the previous question may change depending on how you build your spreadsheet. That is OK, we can look at your spreadsheet and do some quick checks. Again, we are expecting to see formulas/functions in the spreadsheet. This assignment is similar to the Biz Café Individual Assignment pricing worksheet.
What is your average weekly revenue over the six weeks the students gathered their data? Insert the answer and the cell for answer here
- . We will look for it in your spreadsheet. We need the cell so
that we don’t have to navigate your spreadsheet to find your
calculations.
- Hint: You need to average sales for each cup size separately before determining your revenue.
Coffee Sales by Cup for Six Weeks
|
Week 1 |
Small Coffee |
2045 |
|
Medium Coffee |
1689 |
|
|
Large Coffee |
922 |
|
|
Week 2 |
Small Coffee |
2065 |
|
Medium Coffee |
1751 |
|
|
Large Coffee |
901 |
|
|
Week 3 |
Small Coffee |
2057 |
|
Medium Coffee |
1008 |
|
|
Large Coffee |
868 |
|
|
Week 4 |
Small Coffee |
2032 |
|
Medium Coffee |
1698 |
|
|
Large Coffee |
901 |
|
|
Week 5 |
Small Coffee |
2035 |
|
Medium Coffee |
1723 |
|
|
Large Coffee |
865 |
|
|
Week 6 |
Small Coffee |
2076 |
|
Medium Coffee |
1802 |
|
|
Large Coffee |
903 |
Price by Cup Size
Small - $2.50
Medium - $3.50
Large - $4.50
- What is the Cost of Goods Sold for a large cup of coffee?
Insert the answer and the cell with the answer here
............ We will check the cell in your spread
sheet.
- Hint: We are looking for the Cost of Goods Sold for a single large cup of coffee not the total of all large cups sold.
Cost of Goods Sold (GOGS)
Cost of each Cup
Small Cup - $ .08
Medium Cup- $ .09
Large Cup - $.11
Coffee cost for each Cup Size
1/64 lb. of Coffee (Small) - $.11
2/64 lb. of Coffee (Medium) - $.22
3/64 lb. of Coffee (Large) - $.33
- What is your weekly gross profit based on your average weekly
cup sales? Insert the answer and the cell with the answer here
_______________. We will check the cell in your spread
sheet.
- Hint: This is where you have to use your average cup sales to determine your average cost of goods sold.
No new data required.
- What is your weekly gross profit after raising the price of
each cup size by 25 cents? Insert the answer and the cell with the
answer here _______________. We will check the cell in
your spread sheet.
- Hint: You have to determine the forecasted change in average weekly sales based on each cup size.
New Cup Prices by Size
Small - $2.75
Medium - $3.75
Large - $4.75
Customer Response to Coffee Price Change of +25 cents. (How the customer reacts to the price increase)
Small- +4% (increase in demand)
Medium - -10% (decrease in demand)
Large- +6% (increase in demand)
- Your coffee supplier has increased their coffee price to $7.68
per pound. What is your new weekly gross profit? Insert the answer
and the cell with the answer here _______________. We
will check the cell in your spread sheet.
- Hint: You have to determine your new coffee cost for each cup size.
Solutions
Expert Solution
| Week 1 | Week 2 | Week 3 | Week 4 | Week 5 | Week 6 | ||||||||||||
| Small Coffee | Medium Coffee | Large Coffee | Small Coffee | Medium Coffee | Large Coffee | Small Coffee | Medium Coffee | Large Coffee | Small Coffee | Medium Coffee | Large Coffee | Small Coffee | Medium Coffee | Large Coffee | Small Coffee | Medium Coffee | Large Coffee |
| 2045 | 1689 | 922 | 2065 | 1751 | 901 | 2057 | 1008 | 868 | 2032 | 1698 | 901 | 2035 | 1723 | 865 | 2076 | 1802 | 903 |
| SALES | after change in price | ||||||||||||||||
| Avg small coffee | 2052 | 2134 | |||||||||||||||
| Avg medium coffee | 1612 | 1451 | |||||||||||||||
| Avg large coffee | 893 | 947 | |||||||||||||||
| Cup Size | Price | COGS(cup) | COGS(coffee) | total cogs | |||||||||||||
| Small - | 2.50 | 0.08 | 0.11 | 0.19 | |||||||||||||
| Medium - | 3.50 | 0.09 | 0.22 | 0.31 | |||||||||||||
| Large - | 4.50 | 0.11 | 0.33 | 0.44 | |||||||||||||
| Increase in price | 0.25 | ||||||||||||||||
| Weekly gross profit | before increase in price | after increase of price | after change in demand | ||||||||||||||
| Small - | USD 4,739.35 | USD 5,252.27 | USD 5,462.36 | ||||||||||||||
| Medium - | USD 5,141.75 | USD 5,544.71 | USD 4,990.24 | ||||||||||||||
| Large - | USD 3,626.93 | USD 3,850.27 | USD 4,081.28 |
jeff jeffy answered 1 year agoRelated Solutions
Following the instructions on the Data tab, complete the spreadsheet for your Chief Financial Officer. Estimates...
Following the instructions on the Data tab, complete the
spreadsheet for your Chief Financial Officer.
Estimates at the beginning of the year
Assembly
Production
Total
Manufacturing Overhead Costs
$ 480,000
$
528,000
$
108,000
Direct Labor-hours
12,000
7,200
19,200
Machine hours
4,800
24,000
28,800
Job 142
Direct labor-hours
25
17
42
Machine hours
17
20
37
1. Calculate the amount of manufacturing overhead
applied to Job 142. Converse uses a plantwide predetermined
overhead rate...
Instructions: Please answer the following questions to the best of your abilities. Write complete sentences to...
Instructions: Please answer the following
questions to the best of your abilities. Write complete sentences
to clearly communicate your ideas
1. Describe Stress and provide an example of a stressor to
bone?
2. Compare and contrast elasticity and viscoelasticity?
3. What movement related roles do flat bones play?
4. Provide 3 example activities that help maintain bone
integrity.
5. Explain Wollf’s law
6. Explain the role of osteoblasts during episodes of high
stress.
7. Explain the role of osteoclasts during...
Instructions: Please answer the following questions to the best of your abilities. Write complete sentences to...
Instructions: Please answer the following questions to the best
of your abilities. Write complete sentences to clearly communicate
your ideas. Do not change the format of the document. Due to the
school’s computer systems only Microsoft Word or PDF documents are
accepted. Submit the completed assignment by uploading it to
blackboard.
1. Sensory Neuron enter the CNS through the dorsal horn of the
spinal cord. Is also called ?
2. Motoneurons leave the CNS through the ventral horn of the...
Using your Excel TVM Calculators answer the following questions, be sure to submit your spreadsheet with...
Using your Excel TVM Calculators answer the following
questions, be sure to submit your spreadsheet with your answers
highlighted.
At what annual interest rate must $137,000 be invested so that
it will grow to be $475,000 in 10 years?
If you wish to accumulate $175,000 in 13 years, how much must
you deposit today in an account that pays an annual interest rate
of 15%?
How many years will it take for $125,000 to grow to be $500,000
if it...
Instructions Complete the following problems using either a financial calculator or a spreadsheet program. Do not...
Instructions
Complete the following problems using either a financial
calculator or a spreadsheet program. Do not use interim rounding,
state your answers as positive values, to two decimal places for
dollar or period values and four places for percentages stated as
decimals; do not label answers with symbols such as $ or %. For
example, 10.5% should be input as .1050.
An investment with quarterly compounding has a nominal rate of
0.0917. The effective rate is:
Using the information on the Data tab of the spreadsheet , complete the cost of goods...
Using the information on the Data tab of the
spreadsheet , complete the cost of goods
manufactured for January.
Show calculations for all items/questions
Below is manufacturing data from Webster Company for January,
2020.
Beginning raw materials inventory
$
321,300
Raw materials purchased
$
845,150
Ending raw materials inventory
$
275,475
Direct labor costs
$
104,590
Indirect materials
$
47,560
Indirect labor
$
29,876
Factory utilities and maintenance
$
148,890
Factory depreciation
$
19,876
Other factory related overhead
$
7,890...
Instructions: Using the following data, complete the requirements given below. When you are given amounts to...
Instructions: Using the following data,
complete the requirements given below. When you are given amounts
to assume as the answers to previous requirements, be careful to
use such assumed amounts rather than your answers (in order to
minimize carry-through errors).
The Finishing Department of Curtis Corporation reports the
following for January 2011:
Production: All materials are added at the beginning of the
process. Beginning work in process 20,000 units, 60% complete.
Units started into production 240,000 units. Ending work in...
Instructions: After you have completed the calculations on your spreadsheet that correlates to the questions below,...
Instructions: After you have completed the calculations on your
spreadsheet that correlates to the questions below, you will copy
and paste the portion of that spreadsheet into the Word document
for each of the questions. Be sure to also provide a sentence that
explains the solution.
To save for her newborn son ’s college education, Lea Wilson
will invest $1,000 at the end of each year for the next 20 years.
The interest rate is 10%. What is the future...
Proocess Costing Instructions: Using the following data, complete the requirements given below. When you are given...
Proocess Costing
Instructions: Using the following data,
complete the requirements given below. When you are given amounts
to assume as the answers to previous requirements, be careful to
use such assumed amounts rather than your answers (in order to
minimize carry-through errors).
The Finishing Department of Curtis Corporation reports the
following for January 2011:
Production: All materials are added at the beginning of the
process. Beginning work in process 20,000 units, 60% complete.
Units started into production 240,000 units. Ending...
Using these results, complete the table below and then use your results to answer the questions...
Using these results, complete the table below and then use your
results to answer the questions that follow. (Please
remember to round all answers to two decimal places: 0.00 at each
step.) (23 points)
X
X-M
(X-M)2
1
14
-15.85
251.22
2
16
-13.85
191.82
3
18
-11.85
140.42
4
20
-9.85
97.02
5
23
-6.85
46.92
6
23
-6.85
46.92
7
25
-4.85
23.52
8
27
-2.85
8.12
9
28
-1.85
3.42
10
29
-0.85
0.72
11
30
0.15...
ADVERTISEMENT
ADVERTISEMENT
Latest Questions
- Explain what a project is amd how priject management can contribute to an organization. Briefly outline...
- Instructions: Each of the following scenarios illustrates one of Freud’s defense mechanisms from the list below....
- What are the SHDN, LNA in opamp What are the TXCO coding algorithm for binary and...
- Need to program a maze traversal program using dynamic array and stacks. The problem is faced...
- what were some of Ida B Wells significant contributions to the civil rights movement?
- In 2007, there were many people in Sri Lanka who have an opinion that the government...
- Example #2: Write the following set of four linear equations with 4 unknowns x1, x2, x3,...
ADVERTISEMENT