+1443 776-2705 panelessays@gmail.com
  

CSEPUB QUESTIONS

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
Lost Sales
Day old revenue
High demand, % above Average
Low Demand, % below average
Franchise Operations
Monthly Fixed Expense increase
Monthly Production Increase
Monthly demand increase
States of Natures
High demand
Average demand
Low demand
Total

SI and regression Pt 1

Month Demand Yearly average Seasonal Index Average SI Deseasonalized Time period Regression Output
1/1/19 2272 0.8132 0.7749 2931.87 1 CLICK CELL J2 as output cell for regression SUMMARY OUTPUT
2/1/19 2416 0.8647 0.7936 3044.43 2
3/1/19 2893 1.0354 0.8624 3354.76 3 Regression Statistics
4/1/19 2798 1.0014 0.9723 2877.79 4 Multiple R 0.806790669
5/1/19 2401 0.8593 0.9824 2444.08 5 R Square 0.6509111836
6/1/19 3494 1.2505 1.1211 3116.57 6 Adjusted R Square 0.6406438654
7/1/19 2581 0.9238 0.8217 3141.01 7 Standard Error 276.8537358173
8/1/19 2241 0.8021 0.8565 2616.31 8 Observations 36
9/1/19 2279 0.8157 0.8739 2607.84 9
10/1/19 2633 0.9424 1.0643 2473.98 10 ANOVA
11/1/19 3482 1.2462 1.3079 2662.22 11 df SS MS F Significance F
12/1/19 4038 2794.00 1.4452 1.5690 2573.61 12 Regression 1 4859208.01600838 4859208.01600838 63.3964171837 0.0000000028
1/1/20 2603 0.8093 0.7749 3359.01 13 Residual 34 2606031.69522399 76647.9910359998
2/1/20 2455 0.7633 0.7936 3093.57 14 Total 35 7465239.71123238
3/1/20 2533 0.7876 0.8624 2937.30 15
4/1/20 3117 0.9691 0.9723 3205.89 16 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
5/1/20 3567 1.1091 0.9824 3631.00 17 Intercept 2608.8313878038 94.2413597204 27.682446386 6.63101391134469E-25 2417.3099019614 2800.3528736462 2417.3099019614 2800.3528736462
6/1/20 3447 1.0717 1.1211 3074.65 18 Time period 35.3661051585 4.4417578302 7.9621867087 0.0000000028 26.3393671964 44.3928431206 26.3393671964 44.3928431206
7/1/20 2449 0.7614 0.8217 2980.37 19
8/1/20 2825 0.8784 0.8565 3298.12 20
9/1/20 2857 0.8883 0.8739 3269.24 21
10/1/20 3427 1.0655 1.0643 3220.03 22
11/1/20 4174 1.2978 1.3079 3191.30 23
12/1/20 5141 3216.25 1.5984 1.5690 3276.60 24
1/1/21 2660 0.7023 0.7749 3432.56 25
2/1/21 2851 0.7527 0.7936 3592.57 26
3/1/21 2894 0.7641 0.8624 3355.92 27
4/1/21 3584 0.9462 0.9723 3686.20 28
5/1/21 3707 0.9787 0.9824 3773.51 29
6/1/21 3943 1.0410 1.1211 3517.07 30
7/1/21 2954 0.7799 0.8217 3594.95 31
8/1/21 3368 0.8892 0.8565 3932.05 32
9/1/21 3476 0.9177 0.8739 3977.55 33
10/1/21 4488 1.1849 1.0643 4216.95 34
11/1/21 5226 1.3798 1.3079 3995.62 35 Use the average seasonal Index in the column for 2019
12/1/21 6300 3787.58 1.6633 1.5690 4015.28 36 Deseasonalized forecast Seasonalized forecast
1/1/22 37 3917.38 3035.70
2/1/22 38 3952.74 3136.82
3/1/22 39 3988.11 3439.18
4/1/22 40 4023.48 3911.92
5/1/22 41 4058.84 3987.30
6/1/22 42 4094.21 4590.04
7/1/22 43 4129.57 3393.31
8/1/22 44 4164.94 3567.48
9/1/22 45 4200.31 3670.67
10/1/22 46 4235.67 4507.92
11/1/22 47 4271.04 5586.23
12/1/22 48 4306.40 6756.77
Highest yearly average Highest Average SI Total 3 year deseasonalized demand Put yearly average in cell I50 4131.94
3787.58 1.5690 117471.76 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
117574 3263.10
Average 3 year demand
3265.94
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 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
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
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

Data 10

Month Demand
1/1/19 2272
2/1/19 2416
3/1/19 2893
4/1/19 2798
5/1/19 2401
6/1/19 3494
7/1/19 2581
8/1/19 2241
9/1/19 2279
10/1/19 2633
11/1/19 3482
12/1/19 4038
1/1/20 2603
2/1/20 2455
3/1/20 2533
4/1/20 3117
5/1/20 3567
6/1/20 3447
7/1/20 2449
8/1/20 2825
9/1/20 2857
10/1/20 3427
11/1/20 4174
12/1/20 5141
1/1/21 2660
2/1/21 2851
3/1/21 2894
4/1/21 3584
5/1/21 3707
6/1/21 3943
7/1/21 2954
8/1/21 3368
9/1/21 3476
10/1/21 4488
11/1/21 5226
12/1/21 6300