Detailed calculation to understand valuation (2)

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 198,924 99,462 159,139 457,525 Price/unit $ 52.50 $ 52.50 $ 52.50 $ 52.50 Total Sales $ 10,443,510 $ 5,221,755 $ 8,354,808 $ 24,020,073 4 Collections and A/R July August Sept 3rd Quarter Beg Balance $ 26,367 $ - $ - $ 26,367 July Sales $ 7,832,633 $ 1,566,527 $ 1,044,351 $ 10,443,510 August Sales $ - $ 3,916,316 $ 783,263 $ 4,699,580 September Sales $ - $ - $ 6,266,106 $ 6,266,106 Total $ 7,858,999 $ 5,482,843 $ 8,093,720 $ 21,435,562 4 Production Budget July August Sept 3rd Quarter October November Sales needs 198,924 99,462 159,139 457,525 24,111 11,667 End Inv 24,866 39,785 6,028 70,678 2,917 Total Needs 223,790 139,247 165,167 528,203 27,028 Begin Inv (2,310) (24,866) (39,785) (66,960) (6,028) Production Needs 221,480 114,381 125,382 461,243 21,000 4 Direct Materials Purchases Budget July August Sept 3rd Quarter October Production Needs 221,480 114,381 125,382 461,243 21,000 Pounds per Unit 2.10 2.10 2.10 2.10 2.10 Direct Material Needs for Producton 465,107 240,201 263,303 968,610 44,100 Ending Inventory 43,236 47,394 7,938 98,569 Total Direct Material Needs 508,343 287,595 271,240 1,067,179 Begin Inv (3,413) (43,236) (47,394) (94,043) Direct Material Purchases - Pounds 504,931 244,359 223,846 973,136 Cost per pound $ 10.00 $ 10.00 $ 10.00 $ 10.00 Direct Material Purchases - Cost $ 5,047,286 $ 2,442,613 $ 2,237,565 $ 9,727,464 4

Cash Disbursements - DM July August Sept 3rd Quarter $ 547,281 $ 5,047,286 $ 2,442,613 $ 8,037,180 4 Direct Labor Budget July August Sept 3rd Quarter Production Units 221,480 114,381 125,382 461,243 Direct Labor per Unit 3 3 3 3 Labor Hours Required 664,439 343,144 376,146 1,383,729 DL Rate per hour $8 $8 $8 $8 Direct Labor Cost $ 5,315,508 $ 2,745,151 $ 3,009,172 $ 11,069,831 4 Overhead Costs July August Sept 3rd Quarter Production Units 221,480 114,381 125,382 461,243 Variable Costs $ 639,522 $ 330,276 $ 362,041 $ 1,331,839 Fixed Costs $ 5,565 $ 5,565 $ 5,565 $ 16,695 Total Manufacturing Overhead $ 645,087 $ 335,841 $ 367,606 $ 1,348,534 Less: Noncash items $ (630) $ (630) $ (630) $ (1,890) Cash Disbursements $ 644,457 $ 335,211 $ 366,976 $ 1,346,644 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 $ 2.92 $ 2.92 Budgeted Cost per Unit $ 47.92 6 Selling and Administrative July August Sept 3rd Quarter Variable Costs $ 1,242,778 $ 621,389 $ 994,222 $ 2,858,389 Fixed Costs $ 2,410 $ 2,410 $ 2,410 $ 7,229 Selling and Administrative Costs $ 1,245,187 $ 623,799 $ 996,632 $ 2,865,618 4 Cash July August Sept 3rd Quarter Beginning Balance $ 36,867 $ 114,432 $ 115,828 $ 36,867 Cash Receipts $ 7,858,999 $ 5,482,843 $ 8,093,720 $ 21,435,562 Total Cash Available $ 7,895,866 $ 5,597,275 $ 8,209,548 $ 21,472,429 4 Cash Disbursements Direct Materials $ (547,281) $ (5,047,286) $ (2,442,613) $ (8,037,180) Direct Labor $ (5,315,508) $ (2,745,151) $ (3,009,172) $ (11,069,831) Manufacturing Overhead $ (644,457) $ (335,211) $ (366,976) $ (1,346,644) Selling & Administrative Expense $ (1,245,187) $ (623,799) $ (996,632) $ (2,865,618) Total Disbursements $ (7,752,433) $ (8,751,447) $ (6,815,393) $ (23,319,273) 6 Net Cash Inflow $ 143,432 $ (3,154,172) $ 1,394,156 $ (1,846,844) Amount Borrowed $ (29,000) $ 3,270,000 $ - $ 3,241,000 Amount Repaid $ - $ - $ (3,241,000) $ (3,241,000) Interest (only if borrowings are repaid) $ - $ - $ (72,270) $ (72,270) 6 $ (29,000) $ 3,270,000 $ (3,313,270) $ (72,270) Cash Balance $ 114,432 $ 115,828 $ (1,919,114) $ (1,919,114) 2

Quarterly Income Statement Sales $ 24,020,073 Cost of Goods Sold $ (21,922,455) Gross Margin $ 2,097,618 Selling and Admin Expenses $ (2,865,618) Operating Income $ (768,000) Interest Expense $ (72,270) Net Income $ (840,270) 12 Balance Sheet (as of Sept 30th) Assets: Cash $ (1,919,114) Accounts Receivable $ 2,610,878 Raw Materials Inventory $ 79,348 Finished Goods Inventory $ 288,821 Total Assets $ 1,059,932 8 Liabilities and Stockholder's Equity Accounts Payable $ 2,237,565 Common Stock $ 5,460 Retained Earnings $ (941,512) Total Liabilities and Stockholder's Equity $ 1,301,513 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 198,924 Variable overhead per unit $ 2.89 July 198,924 Fixed overhead per month $ 5,565 August 99,462 Depreciation portion of FOH $ 630 September 159,139 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.