Accounting 204 – What If Budget Analysis What are the major benefits of budgeting? Contribution Margin (CM) Income Statement Profitability Analysis:

Accounting 204 – What If Budget Analysis What are the major benefits of budgeting?
Contribution Margin (CM) Income Statement Profitability Analysis:

Analyze what your MOS% means. How is this meaning substantiated by your CM Income statement results for the different months?
Net Profit Margin automatically becomes part of your Vertical Analysis (or common-sized percents) within your CM Income Statement. Analyze why Net Profit Margin % is different when comparing the different volumes each month?

Analysis of Profitability, Liquidity, Solvency of your business relative to benchmark

Describe the company and/or industry you are using as a benchmark for your business. Indicate the related strengths and/or limitations of using this as a benchmark.
In your original budget, evaluate your business’ profitability, liquidity, and solvency relative to your benchmark’s performance for those ratios in each category. Why might there be a significant difference between your performance and your benchmark’s performance in each category? Recipe

Link to recipe https://www.allrecipes.com/recipe/10813/best-chocolate-chip-cookies/

GREEN CELLS ARE THE ONLY CELL YOU ARE ALLOWED TO ENTER NUMBERS.

YELLOW CELLS ARE CALCULATED AND MUST CONTAIN A CELL REFERENCE OR AN EXCEL FORMULA.

EXCLUDE WATER

DO NOT ADD/DELETE ROWS TO THIS SPREADSHEET

Direct Materials (DM) SHOW SHOPPING CART PICTURE BELOW REQUIRES LINK IF NOT OBVIOUS

Number of dozen 4

**Sugars/Flour cups cups/ dozen package cost amount in package unit in package cups in package cost/cup cost per dozen cookies avg cost/ cup

Sugar 1 cups 0.25 $ 10.50 25 lbs 56.700 $ 0.185 $ 0.0463 https://www.calculateme.com/recipe/recipe-weight-to-volume-conversion

Brown sugar 1 cups 0.25 $ 5.14 4 lbs 8.360 $ 0.615 $ 0.1537 https://www.calculateme.com/recipe/recipe-weight-to-volume-conversion

Flour 3 cups 0.75 $ 3.24 10 lbs 31.280 $ 0.104 $ 0.0777 https://www.calculateme.com/recipe/recipe-weight-to-volume-conversion

Place link here

Place link here

Total 1.25 >>feeds raw materials purchase budget 0.2222 >>feeds raw materials purchase budget

**Butter/Shortening/Oil cups cups/ dozen package cost amount in package unit in package cups in package cost/cup cost per dozen cookies avg cost/ cup

Butter 1 cups 0.25 $ 7.47 8 sticks 4 $ 1.8675 $ 0.4669

Shortening cups Place link here

Total 0.25 >>feeds raw materials purchase budget 1.8675 >>feeds raw materials purchase budget

* 1 Stick of butter = .5 cup

**Eggs eggs/ dozen package cost amount in package unit in package eggs in package cost/egg cost per dozen cookies avg cost/ egg

Eggs 2 egg(s) 0.5 $ 2.88 36 eggs 36 $ 0.0800 $ 0.0400

0.5 >>feeds raw materials purchase budget 0.0800 >>feeds raw materials purchase budget

**Chips/Nuts cups cups/ dozen package cost amount in package unit in package cups in package cost/cup cost per dozen cookies avg cost/cup

Chocolate chips 2 cups 0.5 $ 6.98 36 oz 5.676 $ 1.2297 $ 0.61 https://www.convert-me.com/en/convert/cooking/?u=w.ounce&v=36&s=chocolate%20chips

Nuts 1 cups 0.25 $ 13.12 32 oz 7.825 $ 1.6767 $ 0.42 https://www.convert-me.com/en/convert/cooking/?u=w.ounce&v=32&s=walnuts%2C%20chopped

Place link here

Total 0.75 >>feeds raw materials purchase budget 1.3787 >>feeds raw materials purchase budget

Total Direct Material (DM) Cost Per Dozen $ 1.8186 >>>feeds DM Cost on Input sheet

Indirect Materials recipe unit recipe unit/ dozen package cost amount in package unit in package recipe units in package cost per recipe unit cost per dozen cookies

Vanilla 2 teaspoons 0.5 $ 35.90 16 fl oz 96 $ 0.3740 0.1870 https://www.convert-me.com/en/convert/cooking/?u=v.floz&v=16&s=vanilla%20extract

Baking soda 1 teaspoons 0.25 $ 2.24 4 lbs 423.1 $ 0.0053 0.0013 https://www.convert-me.com/en/convert/cooking/?u=w.pound&v=4&s=baking%20soda

Salt 0.5 teaspoons 0.125 $ 0.92 26 oz 146.6 $ 0.0063 0.0008 https://www.convert-me.com/en/convert/cooking/?u=w.ounce&v=26&s=salt

Place link here

Total Indirect Material (IM) Cost Per Dozen $ 0.1891 >>>feeds IM cost on Input sheet

SHOPPING CART PICTURE(S) HERE

SEMESTER IDENTIFIER : SPRING 2021

SECTION IDENTIFIER:

https://www.calculateme.com/recipe/recipe-weight-to-volume-conversion
https://www.allrecipes.com/recipe/10813/best-chocolate-chip-cookies/
https://www.calculateme.com/recipe/recipe-weight-to-volume-conversion
https://www.calculateme.com/recipe/recipe-weight-to-volume-conversion
https://www.convert-me.com/en/convert/cooking/?u=w.ounce&v=36&s=chocolate%20chips
https://www.convert-me.com/en/convert/cooking/?u=w.ounce&v=32&s=walnuts%2C%20chopped
https://www.convert-me.com/en/convert/cooking/?u=w.ounce&v=26&s=salt
https://www.convert-me.com/en/convert/cooking/?u=v.floz&v=16&s=vanilla%20extract
https://www.convert-me.com/en/convert/cooking/?u=w.pound&v=4&s=baking%20soda

MOH Regression

MOH Regression BLUE CELLS = randomly generated numbers (must be converted to VALUES before modifying)

YELLOW CELLS = calculated cells (MUST include a cell reference or an excel formula)

Dozens MOH Utilities, Supplies, and Labor Variable Cost /dozen Fixed cost

4,640 1,862 (VC/unit) (FC)

4,852 1,985 Cost function for Utilities, supplies, and Labor = 0.244751 x + 755.8036

4,757 2,005 >>>Both VC per unit and FC feed the Input Sheet

5,015 1,966

4,892 1,960

4,680 1,998

4,938 1,832

4,781 1,983

4,938 1,938

4,910 1,836

4,931 1,916

4,944 1,959

4,791 1,952

4,996 1,918

4,880 1,930

5,008 2,043

4,715 1,884

4,872 1,916

4,976 1,956

4,913 1,930

5,001 1,930

5,190 1,892

4,868 2,041

5,047 1,959

5,124 1,944

5,109 1,926

5,155 1,997

4,949 1,950

4,901 1,942

5,273 1,905

5,242 1,936

4,977 1,897

5,071 2,012

5,182 2,056

5,359 2,075

5,357 2,023

4,892 1,977

5,362 2,019

5,346 1,914

4,949 2,065

5,176 1,934

5,074 2,046

5,306 1,974

5,095 2,117

5,067 2,007

5,387 1,991

5,041 1,980

5,197 2,025

5,027 2,106

5,083 2,068

5,263 2,098

5,274 2,143

5,288 2,151

5,291 2,004

5,440 1,970

5,444 1,984

5,419 2,052

5,162 1,980

5,328 2,038

5,295 2,158

5,453 2,078

5,574 2,074

5,566 2,176

5,626 2,016

5,482 2,022

5,310 2,165

5,534 2,201

5,333 2,193

5,336 2,078

5,634 2,042

5,592 2,175

5,344 2,165

5,324 2,066

5,780 2,176

5,600 2,125

5,868 2,229

5,372 2,133

5,724 2,090

5,488 2,169

5,568 2,153

5,782 2,117

5,657 2,148

5,761 2,063

5,767 2,231

5,624 2,096

5,576 2,172

5,482 2,228

5,637 2,226

5,797 2,158

5,961 2,244

5,922 2,158

5,539 2,273

5,588 2,203

5,829 2,232

5,714 2,244

5,895 2,135

5,674 2,191

5,783 2,134

MOH Utilities, Supplies, and Labor 4640 4852 4757 5015 4892 4680 4938 4781 4938 4910 4931 4944 4791 4996 4880 5008 4715 4872 4976 4913 5001 5190 4868 5047 5124 5109 5155 4949 4901 5273 5242 4977 5071 5182 5359 5357 4892 5362 5346 4949 5176 5074 5306 5095 5067 5387 5041 5197 5027 5083 5263 5274 5288 5291 5440 5444 5419 5162 5328 5295 5453 5574 5566 5626 5482 5310 5534 5333 5336 5634 5592 5344 5324 5780 5600 5868 5372 5724 5488 5568 5782 5657 5761 5767 5624 5576 5482 5637 5797 5961 5922 5539 5588 5829 5714 5895 5674 5783 1862 1985 2005 1966 1960 1998 1832 1983 1938 1836 1916 1959 1952 1918 1930 2043 1884 1916 1956 1930 1930 1892 2041 1959 1944 1926 1997 1950 1942 1905 1936 1897 2012 2056 2075 2023 1977 2019 1914 2065 1934 2046 1974 2117 2007 1991 1980 2025 2106 2068 2098 2143 2151 2004 1970 1984 2052 1980 2038 2158 2078 2074 2176 2016 2022 2165 2201 2193 2078 2042 2175 2165 2066 2176 2125 2229 2133 2090 2169 2153 2117 2148 2063 2231 2096 2172 2228 2226 2158 2244 2158 2273 2203 2232 2244 2135 2191 2134

Regression Output

SUMMARY OUTPUT

Regression Statistics

Multiple R 0.744956

R Square 0.554959

Adjusted R Square 0.550323

Standard Error 72.60814

Observations 98

ANOVA

df SS MS F Significance F

Regression 1 631107.1 631107.1 119.7105 1.44E-18

Residual 96 506106.5 5271.942

Total 97 1137214

Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%

Intercept 755.8036 118.4519 6.380678 6.21E-09 520.6783 990.9288 520.6783 990.9288

Dozens 0.244751 0.02237 10.94123 1.44E-18 0.200348 0.289154 0.200348 0.289154

Input

BLUE CELLS = randomly generated numbers (must be converted to VALUES before modifying)

YELLOW CELLS = calculated cells (MUST include a cell reference or an excel formula)

Variable Cost Fixed Cost

(per dozen) (per month)

Total Direct Materials Cost per dozen (from recipe sheet) $ 1.82

Direct Labor Costs: Since the facility is highly

automated, direct labor is not separately calculated

and is included as part of manufaturing overhead.

Manufacturing Overhead:

Indirect materials (from recipe sheet) $0.19

Utilities, supplies, and labor (from regression sheet) $0.24 $756

Maintenance $470

Rent, insurance, and property taxes $5,000

Depreciation $1,900

Labor/Supervision $5,000

Operating Expenses:

Sales commission $0.50

Shipping cost $0.70

Salaries $6,800

Depreciation $180

Rent $1,090

Other $2,100

Total Variable Costs (per dozen) $3.45

Sales Price

Mark-up on Total Variable Costs 130%

Markup Per Dozen (dollars) $4.49

Sales Price Per Dozen $7.94

Variable Cost (per dozen) $3.45

Contribution Margin (per dozen) $4.49

Total Fixed Costs (monthly) $23,296

Break-even point (monthly) 5,190

Monthly Sales Projections (in dozens of cookies)

January 7,080

February 8,696

March 7,821

April 9,051

Collection of Sales

% Collection in month of sale 30%

% Collection in month after sale 70%

Production

The company produces and sells cookies daily.

There are no work in process or finished goods inventories.

Raw Materials Inventories

The company plans to maintain an ending inventory of raw materials at the end of each month that is equal to a desired percentage of next month’s production needs

Desired ending raw materials- % of next month’s production needs 14%

Payment for Raw Materials

% Payment in month of purchase 41%

% Payment in month after purchase 59%

Capital contributions

January 48,000

February 2,000

March 0

Purchase of equipment (January)

January 220,000

February 0

March 0

Income tax rate 26%

Interest rate per month 1.3%

&”Arial,Bold”INPUT SHEET

Sales and Cash Collections

YELLOW CELLS = calculated cells (MUST include a cell reference or an excel formula)

January February March Quarter

Expected sales in dozens of cookies 7,080 8,696 7,821 23,597

Selling Price per dozen $7.94 $7.94 $7.94 $7.94

Sales in Dollars $56,220 $69,048 $62,104 $187,371 >>>feeds income statement

Cash Collections:

Cash collected month of sale 16,866 20,714 18,631 56,211

Cash collected month after sale $0 $39,353.70 $48,333.34 87,687

Total Cash Collections $16,866 $60,068 $66,964 $143,898 >>>feeds cash budget

Accounts Receivable @ 3/31 $43,472 >>>feeds A/R on Bal sheet

&”Arial,Bold”Sales and Cash Collections&”Arial,Regular”

Overhead and Operating Exp.

Manufacturing Overhead Budget

YELLOW CELLS = calculated cells (MUST include a cell reference or an excel formula)

January February March Quarter

Dozens of cookies to be produced 7,080 8,696 7,821 23,597

Variable manufacturing overhead per dozen $0.43 $0.43 $0.43 $0.43

Budgeted variable overhead. $3,072 $3,772 $3,393 $10,237

Budgeted fixed overhead $13,126 $13,126 $13,126 $39,377

Total budgeted manufacturing overhead $16,197 $16,898 $16,519 $49,614

Less: Depreciation $1,900 $1,900 $1,900 $5,700

Cash disbursements for manufacturing overhead $14,297 $14,998 $14,619 $43,914 >>>feeds cash budget

Operating Expense Budget

January February March Quarter

Dozens of cookies to be produced 7,080 8,696 7,821 23,597

Variable operating expenses per dozen $1.20 $1.20 $1.20 $1.20

Budgeted variable expenses $8,496 $10,435 $9,385 $28,316

Budgeted fixed expenses $10,170 $10,170 $10,170 $30,510

Total budgeted operating expenses $18,666 $20,605 $19,555 $58,826

Less: Depreciation $180 $180 $180 $540

Cash disbursements for operating expenses $18,486 $20,425 $19,375 $58,286 >>>feeds cash budget

&”Arial,Bold”Manufacturing Overhead Budget&”Arial,Regular”

Raw Mat. Purchases and Disbur.

YELLOW CELLS = calculated cells (MUST include a cell reference or an excel formula)

Ending

Inventory

Sugar/Flour January February March Quarter Cost April

Dozens of cookies to be produced 7,080 8,696 7,821 23,597 9,051

Total cups per dozen 1.25 1.25 1.25 1.25 1.25

Total production needs (cups) 8,850 10,869 9,776 29,496 11,314

Plus: Desired Ending Inventory (cups) 1,522 1,369 1,584 1,584 $351.87

Less: Beginning Inventory (cups) 0 1,522 1,369 0

Purchases Required (cups) 10,372 10,716 9,992 31,080

Cost per cup $0.22 $0.22 $0.22 $0.22

Total cost of raw materials purchased $2,304 $2,381 $2,220 $6,904

Butter, Shortening, Oil January February March Quarter April

Dozens of cookies to be produced 7,080 8,696 7,821 23,597 9,051

Total cups per dozen 0.25 0.25 0.25 0.25 0.25

Total production needs (cups) 1,770 2,174 1,955 5,899 2,263

Plus: Desired Ending Inventory (cups) 304 274 317 317 $591.60

Less: Beginning Inventory (cups) 0 304 274 0

Purchases Required (cups 2,074 2,143 1,998 6,216

Cost per cup $1.87 $1.87 $1.87 $1.87

Total cost of raw materials purchased $3,874 $4,003 $3,732 $11,608

Eggs January February March Quarter April

Dozens of cookies to be produced 7,080 8,696 7,821 23,597 9,051

Total eggs per dozen 0.50 0.50 0.50 0.50 0.50

Total production needs (eggs) 3,540 4,348 3,911 11,798 4,526

Plus: Desired Ending Inventory (eggs) 609 547 634 634 $50.69

Less: Beginning Inventory (eggs) 0 609 547 0

Purchases Required (eggs) 4,149 4,287 3,997 12,432

Cost per egg $0.08 $0.08 $0.08 $0.08

Total cost of raw materials purchased $332 $343 $320 $995

Chips/Nuts January February March Quarter April

Dozens of cookies to be produced 7,080 8,696 7,821 23,597 9,051

Total cups per dozen 0.75 0.75 0.75 0.75 0.75

Total production needs (cups) 5,310 6,522 5,866 17,697 6,788

Plus: Desired Ending Inventory (cups) 913 821 950 950 $1,310.27

Less: Beginning Inventory (cups) 0 913 821 0

Purchases Required (cups 6,223 6,430 5,995 18,648

Cost per cup $1.38 $1.38 $1.38 $1.38

Total cost of raw materials purchased $8,580 $8,865 $8,265 $25,710

Total Raw Material Cost $15,090 $15,591 $14,536 $45,217

Schedule of Expected Cash Disbursements January February March Quarter

Cash paid in month of purchase $6,187 $6,392 $5,960 $18,539

Cash paid in subsequent month $0 $8,903 $9,199 $18,102

Total Cash Disbursements $6,187 $15,295 $15,159 $36,641 >>>feeds cash budget

Total Raw Materials Ending Inventory (March 31) $2,304 >>>feeds raw materials on balance sheet

Accounts Payable @ 3/31 $8,577 >>>feeds A/P on balance sheet

&”Arial,Bold”Raw Materials Purchases and Cash Disbursements&”Arial,Regular”

CM Income Statement

YELLOW CELLS = calculated cells (MUST include a cell reference or an excel formula)

Jan % Feb % Mar % Quarter %

Dozens of cookies>>>>>> 7,080 8,696 7,821 23,597

$/unit

Sales $7.94 $56,220 100.0% $69,048 100.0% $62,104 100.0% $187,371 100.0%

Variable Cost $3.45 $24,443 44.0% $30,021 44.0% $27,002 44.0% $81,466 43.0%

Contribution Margin $4.49 $31,776 56.0% $39,027 56.0% $35,102 56.0% $105,905 57.0%

Fixed Cost $23,296 62.0% $23,296 50.0% $23,296 56.0% $69,887 56.0%

Net Operating Income $8,480 5.0% $15,731 6.0% $11,806 1.0% $36,018 1.0%

Interest Expense $2,730 7.0% $2,522 6.0% $2,340 6.0% $7,592 6.0%

Net Income before Tax $5,750 12.0% $13,209 0.5% $9,466 6.0% $28,426 5.0%

Income Tax $1,495 3.0% $3,434 0.2% $2,461 2.0% $7,391 2.0% >>>feeds cash budget

Net Income $4,255 15.0% $9,775 0.4% $7,005 7.0% $21,035 7.0% >>>quarter N/I feeds retained earnings on B/S

Margin of Safety (MOS) dollars $15,004 $27,832 $20,888 $146,155

MOS% 26.7% 40.3% 33.6% 78.0%

&”Arial,Bold”CM Income Statement

Cash Budget

Cash Budget

YELLOW CELLS = calculated cells (MUST include a cell reference or an excel formula)

GREEN CELLS ARE THE ONLY CELL YOU ARE ALLOWED TO ENTER NUMBERS.

January February March

Beginning Cash Balance $0 $11,671 $1,064

Plus: Capital Contributions $48,000 $2,000 $0

Plus: Cash Collections $16,866 $60,068 $66,964

Total Cash Available $64,866 $73,739 $68,029

Less: Cash Disbursements below:

Raw Materials $6,187 $15,295 $15,159

Manufacturing Overhead $14,297 $14,998 $14,619

Operating Expenses $18,486 $20,425 $19,375

Income Taxes $1,495 $3,434 $2,461

Equipment Purchases $220,000 $0 $0

Total Disbursements $260,465 $54,152 $51,614

Excess (Deficiency) of Cash ($195,599) $19,586 $16,415

Financing:

Borrowing $210,000 $0 $0 Notes: 1. Input borrowing and repayments necessary to maintain a minimum cash balance of $10,000 at month-end.

Principal Repayments $0 $16,000 $14,000 2. Borrow as little as possible and repay the maximum possible to stay within the $10K requirement.

Interest Payments $2,730 $2,522 $2,340 3. Borrow and repay in increments of $1000 at the beginning of each month.

Total Financing $207,270 ($18,522) ($16,340) 4. Interest is incurred and paid monthly at the end of each month on total month-end note payable balance.

Ending Cash Balance $11,671 $1,064 $75 >>>feeds balance sheet

Loan balance beginning of period $0 $210,000 $194,000

+ Borrowing $210,000 $0 $0

– Principle repayments $0 $16,000 $14,000

=Loan balance end of period $210,000 $194,000 $180,000 >>>feeds balance sheet

Interest Rate/month 0.013 0.013 0.013 <<>>feeds cash budget (line 22 above), and CM income statement

&”Arial,Bold”Cash Budget

Balance Sheet

Balance Sheet as of March 31st

YELLOW CELLS = calculated cells (MUST include a cell reference or an excel formula)

GREEN CELLS ARE THE ONLY CELL YOU ARE ALLOWED TO ENTER NUMBERS.

Assets

Current Assets:

Cash $ 75

Accounts Receivable 43,472

Raw Materials Inventory 2,304

Total Current Assets $ 45,852

Fixed Assets (less accumulated depreciation) 212,400

Total Assets $ 258,252

Liabilities and Equity

Liabilities:

Accounts Payable $ 8,577

Notes Payable 180,000

Total Liabilities $ 188,577

Equity:

Capital Contributions 48,000

Retained Earnings 21,035

Total Equity 69,035

Total Liabilities and Equity $ 257,612

Ratios Tasty Cookies Benchmark

Current Ratio 0.24 3.72

A/R Turnover 17.24 7.64

Debt Ratio (Debt to Assets) 0.73 0.12

Times Interest earned (Interest Coverage) 4.74 204.69

Return on Assets 0.33 1.76

Return on Sales (Net Profit Margin Ratio) 0.11 1.79

Degree of Operating leverage (DOL) 2.94

Balance Sheet

Looking for this or a Similar Assignment? Click below to Place your Order