Business Analysis

profileschoolgirl8907
IndivProjectP1_jaylaredding-2.xlsm
Home>Business & Finance homework help>Management homework help>Business Analysis

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 $5,800.00
Revenue: Cup of Coffee $2.99 Varible Costs
Revenue: Donut $2.50 Coffee $0.50
Donut ingredients per donunt) per donut $0.60 Donut $0.60
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
Monthly Production Lost Sales Day old revenue High demand, % above Average Low Demand, % below average
4000 3 1.25 22% 26%
Franchise
Monthly Fixed Expense increase 4,350.00
Monthly Production Increase 32%
Monthly demand increase 22%
States of Natures probabilities
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 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 3 0 4350 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 3035.70 3136.82 3439.18 3911.92 3987.30 4590.04 3393.31 3567.48 3670.67 4507.92 5586.23 6756.77 49583.33
satisfied demand 3.00 3 3 3 3 3 3 3 3 3 3 3 36.00
Extra donuts(over) 0 0 0 0 0 0 0 0 0 0 0 0 0.00
Unsatisfied customers (short) 3032.6977356828 3133.823516625 3436.1752308071 3908.9214168293 3984.2996944967 4587.0369907336 3390.3091872033 3564.4784732171 3667.6665965359 4504.923682874 5583.230917686 6753.769996114 49547.33
Revenue
coffee 8.97 8.97 8.97 8.97 8.97 8.97 8.97 8.97 8.97 8.97 8.97 8.97 107.64
donut 7.5 7.5 7.5 7.5 7.5 7.5 7.5 7.5 7.5 7.5 7.5 7.5
Revenue from day old sales 0 0 0 0 0 0 0 0 0 0 0 0
Total Revenue 16.47 16.47 16.47 16.47 16.47 16.47 16.47 16.47 16.47 16.47 16.47 16.47 197.64
Expenses
Fixed Expenses $5,800.00 $5,800.00 $5,800.00 $5,800.00 $5,800.00 $5,800.00 $5,800.00 $5,800.00 $5,800.00 $5,800.00 $5,800.00 $5,800.00 69600.00
Total Fixed Expense $5,800.00 $5,800.00 $5,800.00 $5,800.00 $5,800.00 $5,800.00 $5,800.00 $5,800.00 $5,800.00 $5,800.00 $5,800.00 $5,800.00
Variable Expenses
Coffee Variable expense 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 1.50 18.00
Donut Variable expense 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 1.80 21.60
Total Variable Expenses 3.30 3.30 3.30 3.30 3.30 3.30 3.30 3.30 3.30 3.30 3.30 3.30 39.60
Expenses: due to lost sales 0 0 0 0 0 0 0 0 0 0 0 0 0.00
Total Expenses 5803.30 5803.30 5803.30 5803.30 5803.30 5803.30 5803.30 5803.30 5803.30 5803.30 5803.30 5803.30 69639.60
Profit Donuts and Coffee -5786.83 -5786.83 -5786.83 -5786.83 -5786.83 -5786.83 -5786.83 -5786.83 -5786.83 -5786.83 -5786.83 -5786.83 -69441.96
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 5280 3 1.25
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 3703.55 3826.92 4195.79 4772.54 4864.51 5599.85 4139.84 4352.32 4478.21 5499.67 6815.20 8243.26 60491.67
satisfied demand 3703.551237533 3826.9246902825 4195.7937815847 4772.5441285317 4864.5056272859 5280 4139.8372083881 4352.3237373248 4478.2132477738 5280 5280 5280 55453.69
Extra donuts(over) 1576.448762467 1453.0753097175 1084.2062184153 507.4558714683 415.4943727141 0 1140.1627916119 927.6762626752 801.7867522262 0 0 0 7906.31
Unsatisfied customers (short) 0 0 0 0 0 319.8451286949 0 0 0 219.6668931062 1535.2017195769 2963.2593952591 5037.97
Revenue
coffee 11073.62 11442.50 12545.42 14269.91 14544.87 15787.20 12378.11 13013.45 13389.86 15787.20 15787.20 15787.20 165806.54
donut 9258.88 9567.31 10489.48 11931.36 12161.26 13200.00 10349.59 10880.81 11195.53 13200.00 13200.00 13200.00 138634.23
Revenue from day old sales 1970.5609530838 1816.3441371469 1355.2577730192 634.3198393354 519.3679658926 0 1425.2034895149 1159.595328344 1002.2334402828 0 0 0 9882.88
Total Revenue 22303.0572471399 22826.1606867978 24390.165633919 26835.5871049744 27225.5038596924 28987.2 24152.9097635655 25053.8526462572 25587.6241705609 28987.2 28987.2 28987.2 314323.66
Expenses 0.00
Fixed Expenses 10150 10150 10150 10150 10150 10150 10150 10150 10150 10150 10150 10150 121800.00
Total Fixed Expense 10150 10150 10150 10150 10150 10150 10150 10150 10150 10150 10150 10150 121800.00
0.00
Variable Expenses 0.00
Coffee Variable expense 2640 2640 2640 2640 2640 2640 2640 2640 2640 2640 2640 2640 31680.00
Donut Variable expense 3168 3168 3168 3168 3168 3168 3168 3168 3168 3168 3168 3168 38016.00
Total Variable Expenses 5808 5808 5808 5808 5808 5808 5808 5808 5808 5808 5808 5808 69696.00
Expenses: due to lost sales 0 0 0 0 0 959.5353860848 0 0 0 659.0006793187 4605.6051587307 8889.7781857772 15113.92
Total Expenses 15958 15958 15958 15958 15958 16917.5353860848 15958 15958 15958 16617.0006793187 20563.6051587307 24847.7781857772 206609.92
0.00
Profit Donuts and Coffee 6345.0572471399 6868.1606867978 8432.165633919 10877.5871049744 11267.5038596924 12069.6646139152 8194.9097635655 9095.8526462572 9629.6241705609 12370.1993206813 8423.5948412693 4139.4218142228 107713.74
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