[email protected]

+1-316-444-1378

benderindividualproject.xlsm

Published by on

5.ID

Do Not Alter or Delete this Worksheet or you submission cannot be graded! DirVer
Action Name Panther ID Date/Time S01
Start S01 Ahadu Solomon 202020202 20-Sep-2021 05:31 Microsoft Office User
Starter Sheet None 0 Jan-01 00:00
Welcome to Microsoft Excel version 16.53 build 912 running on Macintosh (Intel) Version 11.2.3 (Build 20D91)!

Financial information

Donut Information Spring 2021
Based on the below data, create the profit model for Donuts to Go. Ahadu Solomon
Assume that each customer will buy one donut and one cup of coffee
Enter totals
Time period Fixed Costs
Revenue: Cup of Coffee $2.99 Varible Costs
Revenue: Donut $2.50 Coffee
Donut ingredients per donunt) per donut $0.60 Donut
paper products: napkins, plates etc
Insurance month $300.00
Maintenance & Repairs to equipment month $0.00
Marketing & Promotion: Advertising month $100.00
Coffee per cup $0.35
Coffee cups per cup $0.15
Payroll: Wages (Owner/ Manager) month $2,400.00
Payroll: Wages (per Employees) month $1,200.00
Donut and Coffee equipment rent month $500.00
Professional Fees: Accounting month $50.00
Professional Fees: Legal month $25.00
Powdered and Liquid Beverages $0.00
Rent month $1,000.00
Previous research expense for Donuts advancements $1,500.00
Supplies: Office month $25.00
Utilities month $200.00
Additional Data
Operations
Monthly Production 4000
Lost Sales 3
Day old revenue 1.25
High demand, % above Average 22%
Low Demand, % below average 26%
Franchise Operations
Monthly Fixed Expense increase 4,350.00
Monthly Production Increase 32%
Monthly demand increase 22%
States of Natures
High demand 20.00%
Average demand 55.00%
Low demand 25.00%
Total

SI and regression Pt 1

Month Demand Yearly average Seasonal Index Average SI Deseasonalized Time period Regression Output
1/1/19 2422 0.8226 0.7817 3098.3966 1 CLICK CELL J2 as output cell for regression SUMMARY OUTPUT
2/1/19 2564 0.8708 0.8020 3196.9936 2
3/1/19 3080 1.0460 0.8793 3502.7679 3 Regression Statistics
4/1/19 3004 1.0202 0.9846 3050.9105 4 Multiple R 0.7763705501
5/1/19 2595 0.8813 0.9926 2614.4145 5 R Square 0.6027512311
6/1/19 3654 1.2410 1.1140 3280.0933 6 Adjusted R Square 0.5910674438
7/1/19 2725 0.9255 0.8306 3280.6930 7 Standard Error 258.1234968348
8/1/19 2383 0.8093 0.8672 2747.8748 8 Observations 36
9/1/19 2432 0.8260 0.8803 2762.6766 9
10/1/19 2744 0.9319 1.0535 2604.6423 10 ANOVA
11/1/19 3600 1.2227 1.2843 2803.0318 11 df SS MS F Significance F
12/1/19 4130 2944.4167 1.4027 1.5298 2699.6276 12 Regression 1 3437237.51395852 3437237.51395852 51.5886856384 0.0000000263
1/1/20 2634 0.7794 0.7817 3369.6022 13 Residual 34 2265343.14701949 66627.7396182204
2/1/20 2508 0.7421 0.8020 3127.1684 14 Total 35 5702580.66097802
3/1/20 2653 0.7850 0.8793 3017.1569 15
4/1/20 3270 0.9676 0.9846 3321.0644 16 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
5/1/20 3770 1.1155 0.9926 3798.2053 17 Intercept 2816.1576303201 87.8655628239 32.0507550377 5.4439539849223E-27 2637.593322713 2994.7219379271 2637.593322713 2994.7219379271
6/1/20 3588 1.0617 1.1140 3220.8469 18 Time period 29.744677185 4.1412555255 7.1825264106 0.0000000263 21.3286333815 38.1607209884 21.3286333815 38.1607209884
7/1/20 2658 0.7865 0.8306 3200.0301 19
8/1/20 3065 0.9069 0.8672 3534.2998 20
9/1/20 3095 0.9158 0.8803 3515.8240 21
10/1/20 3634 1.0753 1.0535 3449.4425 22
11/1/20 4350 1.2871 1.2843 3386.9967 23
12/1/20 5330 3379.5833 1.5771 1.5298 3484.0230 24
1/1/21 2810 0.7431 0.7817 3594.7541 25
2/1/21 2999 0.7931 0.8020 3739.3852 26
3/1/21 3051 0.8069 0.8793 3469.7873 27
4/1/21 3653 0.9661 0.9846 3710.0453 28
5/1/21 3709 0.9809 0.9926 3736.7489 29
6/1/21 3930 1.0393 1.1140 3527.8507 30
7/1/21 2949 0.7799 0.8306 3550.3720 31
8/1/21 3348 0.8854 0.8672 3860.6315 32
9/1/21 3400 0.8992 0.8803 3862.2946 33
10/1/21 4361 1.1533 1.0535 4139.5209 34
11/1/21 5079 1.3432 1.2843 3954.6106 35 Use the average seasonal Index in the column for 2019
12/1/21 6087 3781.3333 1.6097 1.5298 3978.8458 36 Deseasonalized forecast Seasonalized forecast
1/1/22 37 3916.71 3061.67
2/1/22 38 3946.46 3165.07
3/1/22 39 3976.20 3496.29
4/1/22 40 4005.94 3944.35
5/1/22 41 4035.69 4005.72
6/1/22 42 4065.43 4528.86
7/1/22 43 4095.18 3401.53
8/1/22 44 4124.92 3577.20
9/1/22 45 4154.67 3657.38
10/1/22 46 4184.41 4408.29
11/1/22 47 4214.16 5412.34
12/1/22 48 4243.90 6492.49
Highest yearly average Highest Average SI Total 3 year deseasonalized demand Put yearly average in cell I50
3781.3333 1.5298 121191.6297 When you move your forecast to the profit models, you must use an equation, not just copy the values
Total 3 year demand Average 3 year deseasonalized demand
121264 3366.4342
Average 3 year demand
3368.4444
average si for 2023 12.0000
Ahadu Solomon
Spring 2021

Current operations Pt2 & Pt3

CURRENT OPERATIONS Reminder: Format Cells to show 2 decimal places Monthly Production Lost Sales Day old revenue test
Part 2 4000 3 1.25 Summer 2020
AVERAGE DEMAND
Month Jan-22 Feb-22 Mar-22 Apr-22 May-22 Jun-22 Jul-22 Aug-22 Sep-22 Oct-22 Nov-22 Dec-22 Yearly Total
Demand 3061.67 3165.07 3496.29 3944.35 4005.72 4528.86 3401.53 3577.20 3657.38 4408.29 5412.34 6492.49 49151.20
satisfied demand 3061.67 3165.07 3496.29 3944.35 4000.00 4000.00 3401.53 3577.20 3657.38 4000.00 4000.00 4000.00 44303.49
Extra donuts(over) 938.33 834.93 503.71 55.65 0.00 0.00 598.47 422.80 342.62 0.00 0.00 0.00 3696.51
Unsatisfied customers (short) 0.00 0.00 0.00 0.00 5.72 528.86 0.00 0.00 0.00 408.29 1412.34 2492.49
Revenue
coffee
donut
Revenue from day old sales
Total Revenue
Expenses
Fixed Expenses
Total Fixed Expense
Variable Expenses
Coffee Variable expense
Donut Variable expense
Total Variable Expenses
Expenses: due to lost sales
Total Expenses
Profit Donuts and Coffee
Part 3 You should be able to copy from average to high and low and then just make some modifications
HIGH DEMAND
Month Jan-22 Feb-22 Mar-22 Apr-22 May-22 Jun-22 Jul-22 Aug-22 Sep-22 Oct-22 Nov-22 Dec-22 Yearly Total
Demand
satisfied demand
Extra donuts(over)
Unsatisfied customers (short)
Revenue
coffee
donut
Revenue from day old sales
Total Revenue
Expenses
Fixed Expenses
Total Fixed Expense
Variable Expenses
Coffee Variable expense
Donut Variable expense
Total Variable Expenses
Expenses: due to lost sales
Total Expenses
Profit Donuts and Coffee
Part 3
LOW DEMAND
Month Jan-22 Feb-22 Mar-22 Apr-22 May-22 Jun-22 Jul-22 Aug-22 Sep-22 Oct-22 Nov-22 Dec-22 Yearly Total
Demand
satisfied demand
Extra donuts(over)
Unsatisfied customers (short)
Revenue
coffee
donut
Revenue from day old sales
Total Revenue
Expenses
Fixed Expenses
Total Fixed Expense
Variable Expenses
Coffee Variable expense
Donut Variable expense
Total Variable Expenses
Expenses: due to lost sales
Total Expenses
Profit Donuts and Coffee
Ahadu Solomon
Spring 2021

Franchise operations Pt2 & Pt3

FRANCHISE Reminder: Format Cells to show 2 decimal places Monthly Production Lost Sales Day old revenue
Part 2
AVERAGE DEMAND
Month Jan-22 Feb-22 Mar-22 Apr-22 May-22 Jun-22 Jul-22 Aug-22 Sep-22 Oct-22 Nov-22 Dec-22 Yearly Total
Demand
satisfied demand
Extra donuts(over)
Unsatisfied customers (short)
Revenue
coffee
donut
Revenue from day old sales
Total Revenue
Expenses
Fixed Expenses
Total Fixed Expense
Variable Expenses
Coffee Variable expense
Donut Variable expense
Total Variable Expenses
Expenses: due to lost sales
Total Expenses
Profit Donuts and Coffee
Part 3 You should be able to copy from average to high and low and then just make some modifications
HIGH DEMAND
Month Jan-22 Feb-22 Mar-22 Apr-22 May-22 Jun-22 Jul-22 Aug-22 Sep-22 Oct-22 Nov-22 Dec-22 Yearly Total
Demand
satisfied demand
Extra donuts(over)
Unsatisfied customers (short)
Revenue
coffee
donut
Revenue from day old sales
Total Revenue
Expenses
Fixed Expenses
Total Fixed Expense
Variable Expenses
Coffee Variable expense
Donut Variable expense
Total Variable Expenses
Expenses: due to lost sales
Total Expenses
Profit Donuts and Coffee
Part 3
LOW DEMAND
Month Jan-22 Feb-22 Mar-22 Apr-22 May-22 Jun-22 Jul-22 Aug-22 Sep-22 Oct-22 Nov-22 Dec-22 Yearly Total
Demand
satisfied demand
Extra donuts(over)
Unsatisfied customers (short)
Revenue
coffee
donut
Revenue from day old sales
Total Revenue
Expenses
Fixed Expenses
Total Fixed Expense
Variable Expenses
Coffee Variable expense
Donut Variable expense
Total Variable Expenses
Expenses: due to lost sales
Total Expenses
Profit Donuts and Coffee

Expected Values Pt3

Remember that we use profits to fill in payoff tables like the ones we forecasted in the previous two sheets. It would help if you filled in the table in C5:E6 and referenced those values to the other tables below. Use the probabilities found in Finanicl Information B42:44 for the regret tables PAYOFF TABLE Reminder: Format Cells to show 2 decimal places
State of Nature Summary of Results Methods Fill in the space below for each method solved under each Decision Alternative
Decision Alternatives Low Average High Decision Alternatives Maximin MaxiMax Laplace MinMax regret EVUII EOL
CURRENT OPERATIONS CURRENT OPERATIONS
FRANCHISE FRANCHISE
Decision Alternatives
DM UNDER IGNORANCE
Kiana should choose to:
Maximin FRANCHISE
State of Nature
Decision Alternatives Low Average High Because:
CURRENT OPERATIONS Franchise Operations is proving to be the best decision alternative with most methods.For example, if we evaluate the EOL of the two, Franchise Operations has the lowest EOL. This is the best decision as we always want the lowest "loss" or EOL!
FRANCHISE
Maximax
State of Nature
Decision Alternatives Low Average High
CURRENT OPERATIONS
FRANCHISE
Laplace
State of Nature
Decision Alternatives Low Average High
CURRENT OPERATIONS
FRANCHISE
Minimax Regret
Regret table State of Nature
Decision Alternatives Low Average High
CURRENT OPERATIONS
FRANCHISE
DM UNDER RISK
EVUII
State of Nature
Decision Alternatives Low Average High
Probability
CURRENT OPERATIONS
FRANCHISE
EVUPI
State of Nature
Decision Alternatives Low Average High
Probability
Payoff
EVPI
EOL
Regret table State of Nature
Decision Alternatives Low Average High
Probability
CURRENT OPERATIONS
FRANCHISE
Categories: Uncategorized

Related Posts

Uncategorized

BENCHMARK – EFFECTS OF CHILDHOOD TRAUMA WORKSHEET

Academic Level University           Subject Healthcare Type of Paper    Other (Not listed)            Paper Format    APA Assessment Traits Benchmark Requires Lopeswrite Assessment Description Complete the “Effects of Childhood Trauma Worksheet” document attached. While APA format is not Read more…

Uncategorized

BENCHMARK – EFFECTS OF CHILDHOOD TRAUMA WORKSHEET

Academic Level University           Subject Healthcare Type of Paper    Other (Not listed)            Paper Format    APA Assessment Traits Benchmark Requires Lopeswrite Assessment Description Complete the “Effects of Childhood Trauma Worksheet” document attached. While APA format is not Read more…

Uncategorized

HW

  Scenario The HIT Innovation Steering Committee within an organization is proactively looking at data breaches of other organizations to assist them with minimizing the risk to their data security and privacy. As an intern at this Read more…