Detailed calculation to understand valuation (12)

xlsx

School

Delhi Public School, R.K. Puram *

*We aren’t endorsed by this school

Course

210

Subject

Accounting

Date

Nov 24, 2024

Type

xlsx

Pages

Uploaded by Ultra_Flower_Hawk11

Mervis Inc. July August Sept 3rd Quarter POINTS Sales Budget Sales - units 30,319 15,160 24,255 69,734 Price/unit $ 52.50 $ 52.50 $ 52.50 $ 52.50 Total Sales $ 1,591,748 $ 795,874 $ 1,273,398 $ 3,661,019 4 Collections and A/R July August Sept 3rd Quarter Beg Balance $ 26,367 $ - $ - $ 26,367 July Sales $ 1,193,811 $ 238,762 $ 159,175 $ 1,591,748 August Sales $ - $ 596,905 $ 119,381 $ 716,286 September Sales $ - $ - $ 955,049 $ 955,049 Total $ 1,220,177 $ 835,667 $ 1,233,604 $ 3,289,449 4 Production Budget July August Sept 3rd Quarter October November Sales needs 30,319 15,160 24,255 69,734 24,111 11,667 End Inv 3,790 6,064 6,028 15,881 2,917 Total Needs 34,109 21,223 30,283 85,615 27,028 Begin Inv (2,310) (3,790) (6,064) (12,164) (6,028) Production Needs 31,799 17,433 24,219 73,451 21,000 4 Direct Materials Purchases Budget July August Sept 3rd Quarter October Production Needs 31,799 17,433 24,219 73,451 21,000 Pounds per Unit 2.10 2.10 2.10 2.10 2.10 Direct Material Needs for Producton 66,778 36,610 50,860 154,248 44,100 Ending Inventory 6,590 9,155 7,938 23,683 Total Direct Material Needs 73,367 45,765 58,798 177,931 Begin Inv (3,413) (6,590) (9,155) (19,157) Direct Material Purchases - Pounds 69,955 39,175 49,643 158,774 Cost per pound $ 10.00 $ 10.00 $ 10.00 $ 10.00 Direct Material Purchases - Cost $ 699,270 $ 391,595 $ 496,235 $ 1,587,100 4

Cash Disbursements - DM July August Sept 3rd Quarter $ 547,281 $ 699,270 $ 391,595 $ 1,638,146 4 Direct Labor Budget July August Sept 3rd Quarter Production Units 31,799 17,433 24,219 73,451 Direct Labor per Unit 3 3 3 3 Labor Hours Required 95,397 52,300 72,657 220,354 DL Rate per hour $8 $8 $8 $8 Direct Labor Cost $ 763,173 $ 418,402 $ 581,260 $ 1,762,835 4 Overhead Costs July August Sept 3rd Quarter Production Units 31,799 17,433 24,219 73,451 Variable Costs $ 91,819 $ 50,339 $ 69,933 $ 212,091 Fixed Costs $ 5,565 $ 5,565 $ 5,565 $ 16,695 Total Manufacturing Overhead $ 97,384 $ 55,904 $ 75,498 $ 228,786 Less: Noncash items $ (630) $ (630) $ (630) $ (1,890) Cash Disbursements $ 96,754 $ 55,274 $ 74,868 $ 226,896 4 Ending Finished Goods Inventory Production Costs per Unit: Quantity Cost Total Direct Materials 2.10 $ 10.00 $ 20.99 Direct Labor 3.00 $8 $ 24.00 Overhead 1 $ 3.11 $ 3.11 Budgeted Cost per Unit $ 48.11 6 Selling and Administrative July August Sept 3rd Quarter Variable Costs $ 189,418 $ 94,709 $ 151,534 $ 435,661 Fixed Costs $ 2,410 $ 2,410 $ 2,410 $ 7,229 Selling and Administrative Costs $ 191,828 $ 97,119 $ 153,944 $ 442,891 4 Cash July August Sept 3rd Quarter Beginning Balance $ 36,867 $ 115,008 $ 115,610 $ 36,867 Cash Receipts $ 1,220,177 $ 835,667 $ 1,233,604 $ 3,289,449 Total Cash Available $ 1,257,044 $ 950,675 $ 1,349,215 $ 3,326,315 4 Cash Disbursements Direct Materials $ (547,281) $ (699,270) $ (391,595) $ (1,638,146) Direct Labor $ (763,173) $ (418,402) $ (581,260) $ (1,762,835) Manufacturing Overhead $ (96,754) $ (55,274) $ (74,868) $ (226,896) Selling & Administrative Expense $ (191,828) $ (97,119) $ (153,944) $ (442,891) Total Disbursements $ (1,599,036) $ (1,270,065) $ (1,201,667) $ (4,070,768) 6 Net Cash Inflow $ (341,992) $ (319,390) $ 147,548 $ (744,452) Amount Borrowed $ 457,000 $ 435,000 $ - $ 892,000 Amount Repaid $ - $ - $ (892,000) $ (892,000) Interest (only if borrowings are repaid) $ - $ - $ (30,353) $ (30,353) 6 $ 457,000 $ 435,000 $ (922,353) $ (30,353) Cash Balance $ 115,008 $ 115,610 $ (774,805) $ (774,805) 2

Quarterly Income Statement Sales $ 3,661,019 Cost of Goods Sold $ (3,354,637) Gross Margin $ 306,382 Selling and Admin Expenses $ (442,891) Operating Income $ (136,508) Interest Expense $ (30,353) Net Income $ (166,861) 12 Balance Sheet (as of Sept 30th) Assets: Cash $ (774,805) Accounts Receivable $ 397,937 Raw Materials Inventory $ 79,348 Finished Goods Inventory $ 289,973 Total Assets $ (7,547) 8 Liabilities and Stockholder's Equity Accounts Payable $ 496,235 Common Stock $ 5,460 Retained Earnings $ (268,103) Total Liabilities and Stockholder's Equity $ 233,592 6 Sensitivity Analysis Questions (treat each question independently): Answer How much will need to be borrowed in July and August if the price per unit is $50? July $ 122,000 1 August $ 81,000 1 What is net income if the Variable S&A cost is only $4.00 per unit? Net income: $ 370,892 3 Note: Remember to change your spreadsheet back to the original information about price and Variable S&A cost before submitting it. Excel formatting 13

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Mervis Inc. Budgeted sales 1.05 Month Units Budgeted overhead June 30,319 Variable overhead per unit $ 2.89 July 30,319 Fixed overhead per month $ 5,565 August 15,160 Depreciation portion of FOH $ 630 September 24,255 All OH is paid for in the same month October 24,111 November 11,667 Budgeted cash flow Selling price per unit $ 52.50 End of June Cash Balance $ 36,867 $ 26,367 Minimum cash balance $ 131,367 Monthly interest on borrowing 2.65% Cash collection pattern Ending cash balance must be at least $125,111 Month of sale 25% Borrowing must be in even increments of $1111 Month following sale 25% Repayments made when cash is available; 2 Months following sale 50% The entire principal and interest must be repaid at the same time Repayments are done on a FIFO basis Budgeted production Required ending inventory in terms of next month's sales 23% Beginning Retained Earnings $ (101,242) Beg. inv. of finished goods 2,310 Common Stock $ 5,460 Budgeted direct materials Beg. raw materials in lbs. 3,413 Lbs. needed per unit 2.1 End. inventory of raw materials in terms of next month's demand 26% Cost of materials per lb. $ 10.00 Balance of accts. Payable on 6/30 $ 547,281 Materials are paid for in the following month Budgeted direct labor Hours required per unit 3 Direct labor cost per hour $8 Wages are always paid in the same month Total sales for the quarter $ 2,924,460 Production needs - August 19,688 Budgeted selling and administrative costs Total Manuf. Overhead - Sept $ 61,900 Variable S&A per unit sold $ 6.25 Cash receipts - July $ 605,346 Fixed S&A per month $ 2,410 All S&A is paid for in the same month Beg. balance of accts. rec.- assume all collected in July Check Figures (to assist with budget completion) Hint: You shouldn't have to borrow cash in all 3 months Use the information on this page to fill in the budget on the next page. All budget numbers must be referenced from this page and cannot be manually typed in. Assume that any borrowing is done on the 1st of the month for the month in which the cash is needed. For example, if you calculate that cash will be borrowed for August, assume that it is borrowed on August 1st. Also, assume that any repayments are made on the last day of the month in which the repayment occurs.