IndivProjectP1_jaylaredding.xlsm

April 1st, 2022

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

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

Posted in Uncategorized

You can leave a response, or trackback from your own site.

How We Hire Writers

How We Ensure Quality

Reasons to Choose us

Flexibility

Full Control over Orders

Self-Motivated Writers

Confidentiality and Security

Recent Posts

Recent Comments

Archives

Categories

Meta

IndivProjectP1_jaylaredding.xlsm

5.ID

Financial information

SI and regression Pt 1

Current operations Pt2 & Pt3

Franchise operations Pt2 & Pt3

Expected Values Pt3

Leave a Reply

Format and Features

We Guarantee

We Accept