Accounting 204 - What If Budget Analysis
nicodass
NicolasDassumTASTYCOOKIES1.xlsxRecipe
| 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: | |||||||||||
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 | <<<from input sheet | ||
| Interest payments | $2,730 | $2,522 | $2,340 | >>>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
