QuestionQuestion

Transcribed TextTranscribed Text

Crunchy Cones, Inc. Management of an ice cream cone retail company, Crunchy Cones, Inc., is considering opening specialty stores that sell whole wheat ice cream cones lined on the inside with bittersweet chocolate. The cones would be manufactured by a company experienced in the manufacture of chocolate products and Crunchy Cones would be a retailer for the cones. You have been asked to prepare a generic ternplate that can be used for "what-if' analyses of monthly financial projections for the first three months of operations. The spreadsheet should include an Income Statement, Balance Sheet, Statement of Retained Earnings and Statement of Cash Flows. The spreadsheet should be able to accommodate changes in any of the variables or assumptions specified in the input section. An acceptable solution to the project for sets ofinputs is attached. You may use any layout that conforms reasonably to U.S. GAAP. There are many correct ways to derive account relationships. An Input Sheet (Input "A") and blank spreadsheets with the desired format for the statements are available on Blackboard (see "Course Documents"). While preparing the spreadsheet, please make the following assumptions: (1) Unless otherwise stated, all cash receipts occur on the first day of the month. (2) All cash disbursements occur on the last day of the month. (3) Inventory purchases are paid for in the month after purchase. (4) Assume that bank loans are received on the last day of the month. Interest accrued on the bank loan is paid in the same month. (5) Selling, general and administrative expenses are paid in the month they are expensed. (6) Income taxes are paid at the end of the quarter. If the sum of the three month's income taxes is negative, the company does not get a refund until the end of the year. (7) The units sold is a positive function of the investment in property, plant and equipment and a negative function of the selling price: units sold = 5 x gross investments in PPE end of the month (sales price)2 1 This project is adapted from "Financial Statement Articulation Project" by Alan Mayer-Sommer and "Financial Accounting Simulation Project" in Financial Accountin in an Economic Context, 3rd edition (Cincinnati, Ohio: Southwest Publishing Company) by Jamie Pratt. (8) The desired minimum cash balance at the end of every month is given in the "Input Section." If the cash balance (before borrowing) is less than the desired minimum, the company will borrow the difference on the last day of the month. If the cash balance (before borrowing/repayment) is more than the desired amount and the company has a loan payable,the company will repay the loan to the extent of excess cash available on the last day of the month. (9) Payments for PP&E purchases are made in the month of purchase. When developing the spreadsheet, ít may be best to prepøre the statements in the following order: íncome statement, cash budget, bølønce sheet, ønd støtement of cøsh flows. When finished with the spreadsheet, please e-mail me your EXCEL spreadsheet. Please make sure you have your name on the spreadsheet. Also, answer the following questions using Input Sheet'cA)) as the base spreadsheet: (1) What amount of net income (or loss, be specific) did the company report for the quarter? (2) What net income (or loss) will be reported for the quarter if the company increases its sales price from $2.50 to $2.75? V/hat sales price will generate the largest net income for the quarter, and atthat price, how many units will be sold? (Hint: Use the trial-and-error method, inputting different sales prices to find the sales price that maximizes net income.) (3) Assuming a $2.50 price, what income (or loss) will be reported for the quarter if the company uses an 8-year life instead of a 2-year life for purposes of depreciating its property, plant and equipment? How would this change affect the company's net cash from operations for the quarter? Explain. The output from two other versions of the input is attached. You can test the accuracy of your answers by using these inputs (B and C). Input B is the same as your Input A, except that the company issues 500,000 shares in January. Similarly, input C differs from Input A in that it has 1) selling price set at$.3.25,2) number of shares issued in January is 50,000, 3) number of shares issued in February is 50,000, and 3) number of shares issued in March is 30,000. If you change your input sheet to conform to "B" or "C",yotJ should get the same answers provided for these two differentinputs. Just changing the input should change your answers automatically (no other changes should be made on the spreadsheet itselfl. lnput Section | 0l ¡)ü lil i:l,l.l ii f"; n r (( I 11 ¡iuÌ i:1,\) Operating Activities: Selling price per unit of inventory sold Cost per unit of inventory purchased Percent of sales on credit Percent of next month's units sold stocked as ending inventory Selling, generaland administrative expenses per month: Variable expenses (% of revenues) Fixed expenses lncome tax rate (% of net income before taxes) Percent of credit sales collected in: month of sale 1 month after sale 2 months after sale Financing Activities Number of common shares sold ( in January) Selling price per share Number of common shares sold ( in February) Selling price per share Number of common shares sold (in March) Selling price per share Desired minimum cash balance Annual lnterest Rate Dividends paid per month lnvesting Activities: Property, Plant and Equipment purchased during the month January February March April May Estimated useful life of PPE (in months) Estimate residualvalue of PPE INPUT A $2.50 $2.00 80.00o/o 80o/o 10.00o/o $25,000 30.00% 50% 20o/o 15o/o 100,000 $2.75 1,000 $3.00 2,000 $4.00 $150,000 9.00% $1,000 $100,000 $40,000 $30,000 $15,000 $10,000 24 4o/o lnput Page - lnputA lncome Statement Sales Cost of Goods Sold Beginning lnventory Purchases Goods Available for Sale Ending lnventory Total Cost of Goods Sold Gross Profit Selling and Administrative Expenses Variable Fixed Depreciation Expense Total GS & A Net lncome from Operations lnterest Expense Net lncome Before Taxes lncome Tax Expense Net lncome Balance Sheet Assets Cash Accounts Receivable lnventory Total Current Assets Property, Plant and Equipment Less: Accumulated Deprection Net Property, Plant and Equipment Total Assets Liabilities Account Payable lnterest Payable Loan payable lncome Tax Payable Total Current Liabilities Stockholders' Equity CapitalStock Retained Earnings Total Stockholders' Equity Total Liabilities and Stockholders Equity Statement of Retained Earnings Beginning Retained Earnings Add; Net lncome Deduct: Dividends Ending Retained Earnings January February March 1st Quarter $200,000 $280,000 $340,000 $820,000 $0 $339,200 $179,200 s262,400 $217,600 $291,200 $o $892,800 $339,200 $441,600 $508,800 $892,800 179 200 17 800 800 $20,000 $25,000 $4,000 $28,000 $25,000 $5,600 $34,000 $25,000 $6,800 $82,000 $75,000 $16,400 $49,000 $58,600 $65,800 $173,400 ($e,ooo) ($2,600) $2,200 ($e,+oo¡ $0 $0 $e84 $984 ($e,ooo) ($2,600) $1,216 ($10,384) 365 115 2 $249,000 $80,000 $179,200 7 $150,000 $160,000 $217,600 $150,000 $227,200 $236,800 $150,000 $227,200 $236,800 $508,200 $100,000 $4,000 $527,600 $140,000 $9,600 $614,000 $170,000 $16,400 $6'14,000 $170,000 $16,400 $96,000 $130,400 $153,600 $153,600 $604,200 $658,000 $339 ($z ,200 $0 $0 ,700) $262,400 $0 $131,200 ($3,+eo¡ $291,200 $0 $203,784 ($3, r 15) $291,200 $o 9203,784 ($3,115) $336,500 $390,120 $491,869 $491,869 $275,000 ($7,300) $278,000 ($r o,120) $286,000 ($10,26e) $286,000 ($ 10,26e) 9267,700 $267,880 $275,731 $275,731 $604,200 $658,000 $767,600 $767,600 0 ($6,300) 000 INPUT A ($7,300) ($1,820) ($10,120) $851 $0 ($7,26e) 1 000 Page I - lnputA 000 000 000 Cash Flow January February March 1st Quarter Statement of Cash Flows Cash Flows from Operating Activities From Customers To suppliers To landlord, employees, etc To bank for lnterest To IRS for taxes Net cash flow from operations Cash Flows from lnvesting Activities Payments of PP&E purchases Net cash flow from investing activities Cash Flows from Financing Activities From sale of stock From bank loan To repay loan For dividends Net Cash flow from financing activities Net Change in cash Beginning of period cash balance Ending cash balance Cash Budget Beginning of period cash balance Collections from Cash Sales Credit Sales Stock Sales Cash Available Disbursements for lnventory Selling and Administrative Expenses lnterest lncome Taxes Property, Plant and Equipment Dividends Total Disbursements Cash balance before loan adjustment Loan Taken Loan Repayment Month-end cash balance Month-end loan balance $75,000 ($192,200) ($49,584) ($166,784) ($100,000) ($40,000) ($30,000) ($170,000) ($r 00,000) ($40,000) ($30,000) ($170,000) $120,000 $0 ($45,ooo) $0 $0 $200,000 ($33e,200) ($53,ooo) $0 $0 $272,800 ($262,400) ($5e,000) ($ea+¡ $0 $592,800 ($601,600) ($157,000) ($e84) $0 $27 $40,000 $80,000 $275,000 $3,000 $131,200 $0 $56,000 $144,000 $3,000 $8,000 $72,584 $0 $68,000 $204,800 $8,000 $286,000 $203,784 $0 784 150,000 50 $o $164,000 $428,800 $286,000 5, 000 $o $0 1 000 000 000 $0 $249,000 $150,000 0 000 000) $395,000 $452,000 $430,800 $878,800 $0 $339,200 $262,400 $601,600 $45,000 $53,000 $59,000 $157,000 $0 $0 $e84 $e84 $o $o $o $0 $100,000 $40,000 $30,000 $170,000 $1,000 $1,000 $1,000 $3,000 $146,000 $433,200 $353,384 $932,584 $24e,000 $18,800 $77,416 ($53,784) $0 $131,200 $72,584 $203,784 $249,000 $150,000 $150,000 $150,000 $0 $0 $0 $0 $249,000 $150,000 $0 $131,200 $203,784 $203,784 INPUT A 133 200 000 Page2 - lnputA lnput Section I irl prl [, ii.ì'l'] il i")ir i( iilrìt)I I I iir]) Operating Activities: Selling price per unit of inventory sold Cost per unit of inventory purchased Percent of sales on credit Percent of next month's units sold stocked as ending inventory Selling, generaland administrative expenses per month: Variable expenses (% of revenues) Fixed expenses lncome tax rate (7o of net income before taxes) Percent of credit sales collected in: month of sale 1 month after sale 2 months after sale Financing Activities Number of common shares sold ( in January) Selling price per share Number of common shares sold ( in February) Selling price per share Number of common shares sold (in March) Selling price per share Desired minimum cash balance Annual lnterest Rate Dividends paid per month lnvesting Activities: Property, Plant and Equipment purchased during the month January February March April May Estimated useful life of PPE (in months) Estimate residualvalue of PPE INPUT B $2.50 $2.00 80.000/o 80% 10,00% $25,000 30.00% 50% 20%o 15o/o $100,000 $40,000 $30,000 $15,000 $10,000 24 4o/o $2.7s 1,000 $3.00 2,000 $4.00 $150,000 9.00% $1,000 l:dclrì (cli}lÛl lnput Page - lnput B lncome Statement Sales Cost of Goods Sold Beginning lnventory Purchases Goods Available for Sale Ending lnventory Total Cost of Goods Sold Gross Profit Selling and Administrative Expenses: Variable Fixed Depreciation Expense TotalGS &A Net lncome from Operations lnterest Expense Net lncome Before Taxes lncome Tax Expense Net lncome Balance Sheet Assets Cash Accounts Receivable lnventory Total Current Assets Property, Plant and Equipment Less: Accumulated Deprection Net Property, Plant and Equipment Total Assets Liabilities Account Payable lnterest Payable Loan payable lncome Tax Payable Total Current Liabilities Stockholders'Equity CapitalStock Retained Earnings Total Stockholders' Equity Total Liabilities and Stockholders Equity Statement of Retained Earnings Beginning Retained Earnings Add; Net lncome Deduct: Dividends Ending Retained Earnings January February March 1st Quarter $200,000 $280,000 $340,000 $820,000 $0 $339,200 $r79,200 $262,400 $217,600 $291,200 $0 $892,800 $339,200 $441,600 $508,800 $892,800 179 200 17 600 36 0 800 000 ,000 $20,000 $28,000 $34,000 $82,000 $25,000 $25,000 $25,000 $75,000 $4,000 $5,600 $6,800 $16,400 $49,000 $58,600 $65,800 $173,400 ($e,ooo) ($2,600) $2,200 ($e,+oo¡ $0 $0 $0 $o ($e,ooo) ($2,600) $2,200 ($9,400) 580 $1,349,000 $80,000 $179,200 $1 ,118,800 $160,000 $2r7,600 $1,047,200 $227,200 $236,800 $1,047,200 $227,200 $236,800 $1,608,200 $100,000 $4,000 $1,496,400 $140,000 $9,600 $1,511,200 $1,5r1,200 $170,000 $170,000 $16,400 $16,400 $e6,000 $130,400 $153,600 $153,600 s1,704,200 $1,626,800 $r,664,800 $1,664,800 $339,200 $0 $0 ($2,zoo¡ $262,400 $0 $0 ($3,+ao¡ $291,200 $0 $0 ($2,820) 9291,200 $0 $0 ($2,820) $336,500 $258,920 $288,380 $288,380 $1,375,000 $1,378,000 $1,386,000 $1,386,000 ($7,300) ($10,120) ($e,580) ($e,580) $1,367,700 $1,367,880 $1,376,420 $1,376,420 $1,704,200 $1,626,800 $1,664,800 $1,664,800 0 ($6,300) 000 INPUT B ($7,300) ($1,820) ($1 o,120) $1,540 $o ($6,580) 000 000 Pagel-lnputB 000 000 Cash Flow January February March 1st Quarter Statement of Cash Flows Cash Flows from Operating Activities From Customers To suppliers To landlord, employees, etc To bank for lnterest To IRS for taxes Net cash flow from operations Cash Flows from lnvesting Activities Payments of PP&E purchases Net cash flow from investing activities Cash Flows from Financing Activities From sale of stock From bank loan To repay loan For dividends Net Cash flow from financing activities Net Change in cash Beginning of period cash balance Ending cash balance Cash Budget Beginning of period cash balance Collections from Cash Sales Credit Sales Stock Sales Cash Available Disbursements for lnventory Selling and Administrative Expenses lnterest lncome Taxes Property, Plant and Equipment Dividends Total Disbursements Cash balance before loan adjustment Loan Taken Loan Repayment Month-end cash balance Month-end loan balance $75,000 ($r e2,200) ($48,600) ($165,800) ($100,000) ($40,000) ($30,000) ($170,000) ($100,000) ($40,000) ($30,000) ($170,000) $120,000 $0 ($45,ooo) $0 $0 $200,000 ($339,200) ($53,000) $0 $0 $272,800 ($262,400) ($5e,ooo) $0 $0 $592,800 ($601,600) ($157,000) $o $o $1,375,000 $0 $0 ($1,000) $3,000 $0 $0 ($1,000) $0 $0 ($1,000) $8,000 $1,386,000 $o $0 ($3,000) $1,374,000 $2,000 $7,000 $1,383,000 $1,349,000 ($230,200) ($71,600) $1,047,200 0 349 000 1 11 800 1 047 $0 $1,349,000 $1,118,800 $o 1 $40,000 $80,000 $1,375,000 $56,000 $144,000 $3,000 $68,000 $204,800 $8,000 $164,000 $428,800 $1.386,000 $1,495,000 $1,552,000 $1 ,399,600 $1,978,800 $0 $45,000 $0 $0 $100,000 $53,000 $339,200 $0 $0 $40,000 s262,400 $59,000 $0 $0 $30,000 $601,600 $157,000 $o $0 $170,000 000 000 000 000 1,11 7 $0 $0 $0 $1,349,000 $1,118,800 $0 $0 $1,047,200 $1,047,200 $0 $ 1 ,349,000 $ 1 , 1 1 8, 800 $1 ,047 ,200 $1 ,047 ,200 $0 $0 $0 $o INPUT B I $0 1 118 800 1 047 Page2 - lnput B lnput Section û1it Sl*.jil[") ¡] ((lrl etq {l ,11 (c.ì, Operating Activities: Selling price per unit of inventory sold Cost per unit of inventory purchased Percent of sales on credit Percent of next month's units sold stocked as ending inventory Selling, generaland administrative expenses per month: Variable expenses (% of revenues) Fixed expenses lncome tax rate (% of net income before taxes) Percent of credit sales collected in: month of sale 1 month after sale 2 months after sale Financing Activities Number of common shares sold ( in January) Selling price per share Number of common shares sold ( in February) Selling price per share Number of common shares sold (in March) Selling price per share Desired minimum cash balance Annual lnterest Rate Dividends paid per month lnvesting Activities: Property, Plant and Equipment purchased during the month January February March April May Estimated useful life of PPE (in months) Estimate residualvalue of PPE INPUT C 00 80.00% 80o/o 10.00o/o $25,000 30.00% 5Ùo/o 20Yo 15o/o ç2.75 $3.00 $4.00 $150,000 9.00% $1,000 $100,000 $40,000 $30,000 $15,000 $10,000 24 4o/o i;illJ2l:ì l:fol,(oiolcì l5í.{ì1,{{}l0l{)l l3lül,i0tr0lrrl lnput Page - lnput C lncome Statement Sales Cost of Goods Sold Beginning lnventory Purchases Goods Available for Sale Ending lnventory Total Cost of Goods Sold Gross Profit Selling and Administrative Expenses: Variable Fixed Depreciation Expense Total GS & A Net lncome from Operations lnterest Expense Net lncome Before Taxes lncome Tax Expense Net lncome Balance Sheet Assets Cash Accounts Receivable lnventory Total Current Assets Property, Plant and Equipment Less: Accumulated Deprection Net Property, Plant and Equipment Total Assets Liabilities Account Payable lnterest Payable Loan payable lncome Tax Payable Total Current Liabilities Stockholders' Eq uity CapitalStock Retained Earnings Total Stockholders' Eq uity Total Liabilities and Stockholders Equity Statement of Retained Earnings Beginning Retained Earnings Add; Net lncome Deduct: Dividends Ending Retained Earnings January February March 1st Quarter $153,846 $215,385 $261,538 $630,769 $0 $200,710 $106,036 $155,266 $128,757 $r 72,308 $s28,284 $o $200,710 $261,302 $106,036 $128,757 $301,065 $528,284 $140,118 $140,118 $94,675 $132,544 $160,947 $388,166 $59, I 72 $82,840 $r 00,592 $242,604 $15,385 $25,000 000 14,787 $0 $21,538 $25,000 0 ,702 $26,1 54 $25,000 800 $63,077 $75,000 1 77 88,127 $462 $348 $810 $14,787 $30,240 942,290 $87,316 072 12 195 $10 351 1 122 $150,000 $61,538 $106,036 $150,000 $123,077 $128,757 $169,442 $174,769 $140,118 9169,442 $174,769 $140,118 $317,574 $100,000 000 $41 3 $200,710 $0 $61,577 $401,834 $140,000 600 $155,266 $0 $46,441 $484,329 $170,000 1 $484,329 $170,000 1 I 308 $0 $0 $0 $r 72,308 $172 $0 $0 $¿ 436 $13 8 $o 723 $215,215 $172,308 $172,308 $137,500 $287,500 $407,500 $407,500 351 I 5'19 122 122 929 0 $9,351 $29,s19 $0 $10,351 $21 ,168 $29,603 $61,122 $1,000 $1,000 $1,000 $3,000 $9,351 $2e,519 $58 INPUT C 603 13 400 1 600 317 019 622 234 Pagel-lnputC Cash Flow Januarv Februarv March lst Quañer Statement of Cash Flows Cash Flows from Operating Activities From Customers To suppliers To landlord, employees, etc To bank for lnterest To IRS for taxes Net cash flow from operations Cash Flows from lnvesting Activities Payments of PP&E purchases Net cash flow from investing activities Cash Flows from Financing Activities From sale of stock From bank loan To repay loan For dividends Net Cash flow from financing activities Net Change in cash Beginning of period cash balance Ending cash balance Cash Budget Beginning of period cash balance Collections from Cash Sales Credit Sales Stock Sales Cash Available Disbursements for lnventory Selling and Administrative Expenses lnterest lncome Taxes Property, Plant and Equipment Dividends Total Disbursements Cash balance before loan adjustment Loan Taken Loan Repayment Month-end cash balance Month-end loan balance $51,e23 ($e3,864) ($23,117) ($65,058) ($100,000) ($40,000) ($30,000) ($170,000) ($r00,000) ($40,000) ($30,000) ($170,000) $137,500 $150,000 $120,000 $407,500 $61,577 $0 $0 961,577 $o ($15,136) ($46,441) ($61,577) ($1,000) ($t 'ooo) ,t'''ooo', ,n.'ooo, $198,077 $133,864 $72,559 $404,500 $150,000 $0 $1e,442 ç169,442 000 150 000 69 442 $0 $150,000 $150,000 $o $92,308 $0 ($40,385) $0 $0 $153,846 ($200,710) ($46,538) ($+oz¡ $0 $209,846 ($155,266) ($51,154) ($3+e¡ ($26,1 95) $456,000 ($355,e76) ($138,077) ($810) ($26,195) $30,769 $43,077 $61 ,538 $110,769 $137,500 $150,000 $52,308 $1s7,538 $120,000 9126,154 $329,846 $407,500 $229,808 $453,846 $479,846 $863,500 $40, $ 00,000 $0 385 $0 $0 $200,710 $46,538 $462 $0 $40,000 $155,266 $51,1 54 $348 $26,1 95 $30,000 $355,976 $138,077 $81 0 $26,19s $170,000 1 000 000 $141 385 71 $88,423 $61,577 5, $0 $0 $61,577 $150,000 $165,136 $215,883 $231,019 $0 $15,136 $46,441 $61,577 $150,000 $150,000 $61,577 $46,441 $0 $o INPUT C $1 000 1 000 058 1 000 Page2-lnputC

Solution PreviewSolution Preview

This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.

Operating Activities:
Selling price per unit of inventory sold $2.50
Cost per unit of inventory purchased $2.00
Percent of sales on credit 80.00%
Percent of next month's units sold stocked as ending inventory 80%
Selling, general and administrative expenses per month:
Variable expenses (% of revenues) 10.00%
Fixed expenses $25,000
Income tax rate (% of net income before taxes) 30.00%
Percent of credit sales collected in:
    month of sale 50%
    1 month after sale 20%
    2 months after sale 15%

Financing Activities
Number of common shares sold ( in January) 100,000
Selling price per share $2.75
Number of common shares sold ( in February) 1,000
Selling price per share $3.00
Number of common shares sold (in March) 2,000
Selling price per share $4.00
Desired minimum cash balance $150,000
Annual Interest Rate 9.00%
Dividends paid per month $1,000...
$25.00 for this solution

PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

Find A Tutor

View available Accounting Tutors

Get College Homework Help.

Are you sure you don't want to upload any files?

Fast tutor response requires as much info as possible.

Decision:
Upload a file
Continue without uploading

SUBMIT YOUR HOMEWORK
We couldn't find that subject.
Please select the best match from the list below.

We'll send you an email right away. If it's not in your inbox, check your spam folder.

  • 1
  • 2
  • 3
Live Chats