def create_cost_for_products(wb, sht, products, product): for i in range(1, int(products + 1)): next_row = h.lastRow('CostDetail', wb, 5) + 2 sht.range('B' + str(next_row)).value = '=OperatingAssumptions!$E' + str( i * (len(product.rows) + 1) - (len(product.rows) - 3)) sht.range('D' + str(next_row)).value = [['Units Ordered'], ['Discount'], ['Monthly Units'], ['Monthly Cost']] sht.range('E' + str(next_row) + ':P' + str(next_row)).value = '=Inventory!E' + str(next_row + 2 + (i - 1)) sht.range('E' + str(next_row + 1) + ':P' + str(next_row + 1)).value = 0 sht.range('E' + str(next_row + 2) + ':P' + str(next_row + 2)).value = '=Inventory!E' + str(next_row + 1 + (i - 1)) sht.range('E' + str(next_row + 3) + ':P' + str(next_row + 3)).value = '=monthly_cost_per_product(Product' + str( i) + ', E' + str(next_row + 2) + ')' last_row = h.lastRow('CostDetail', wb, 5) sht.range('E4:P' + str(last_row)).name = 'AllCost' sht.range('D' + str(last_row + 2)).value = 'Total Cost' sht.range('E' + str(last_row + 2) + ':P' + str(last_row + 2) ).value = '=all_products_cost(E2, AllCost,' + str(products) + ')' sht.range('E' + str(last_row + 2) + ':P' + str(last_row + 2)).name = 'AllProductCost'
def create_assets(idx, wb, num_of_assets, asset_type): sht = wb.sheets[idx] next_row = h.lastRow(idx, wb, 16) + 1 sht.range('P' + str(next_row)).value = asset_type for i in range(int(num_of_assets)): next_row = h.lastRow(idx, wb, 16) + 1 sht.range('P' + str(next_row)).value = [['Name'], ['Month Acquired'], ['CapEx'], ['Useful Life']] sht.range('Q' + str(next_row) + ":Q" + str(next_row + 3)).color = (255, 242, 204) sht.range('Q' + str(next_row) + ":Q" + str(next_row + 3)).value = [['Asset ' + str(i + 1)], [1], [10000], [5]]
def create_seed(idx, wb, num_of_rds): sht = wb.sheets[idx] next_row = h.lastRow(idx, wb, 19) + 1 sht.range('S' + str(next_row)).value = 'Equity' for i in range(int(num_of_rds)): next_row = h.lastRow(idx, wb, 19) + 1 sht.range('S' + str(next_row)).value = [ ['Seed ' + str(i + 1)], ['Month'], ['Cash Amount'], ['Shares Amount'], ] sht.range('T' + str(next_row + 1) + ':T' + str(next_row + 3)).color = (255, 242, 204) sht.range('T' + str(next_row + 1) + ':T' + str(next_row + 3)).value = [[1 + i], [160000], [1000000]]
def create_channel(idx, wb, channel, flex='Base Number', flex2='Sales Rate', flex3='Base Units'): next_row = h.lastRow(idx, wb) + 1 sht = wb.sheets[idx] sht.range('A' + str(next_row)).value = [[channel], [flex], ['Initial Month'], ['Growth Frequency'], ['Growth Amount'], [flex2], [flex3], ['Initial Month of Sales'], ['Growth Frequency'], ['Growth Amount']] sht.range('B' + str(next_row + 1) + ":B" + str(next_row + 4)).color = (255, 242, 204) sht.range('B' + str(next_row + 6) + ":B" + str(next_row + 9)).color = (255, 242, 204) sht.range('B' + str(next_row + 1) + ":B" + str(next_row + 4)).value = [[1], [1], [1], [1]] sht.range('B' + str(next_row + 6) + ":B" + str(next_row + 9)).value = [[10], [1], [1], [10]] sht.range('A' + str(next_row) + ":B" + str(next_row + 9)).name = channel
def create_employees(wb, sht, sales, rnd, oh): for i in range(1, int(sales + 1)): next_row = h.lastRow('Employees', wb, 5) + 1 sht.range('C' + str(next_row)).value = '=OperatingAssumptions!$H' + str( (i * 6) - 2) sht.range( 'E' + str(next_row) + ':P' + str(next_row) ).value = '=employee_cost(E2, General_Sales_and_Marketing' + str( i) + ')' for i in range(int(sales + 1), int(sales + rnd + 1)): next_row = h.lastRow('Employees', wb, 5) + 1 sht.range('C' + str(next_row)).value = '=OperatingAssumptions!$H' + str( (i * 6) - 1) sht.range( 'E' + str(next_row) + ':P' + str(next_row) ).value = '=employee_cost(E2, Research_and_Development' + str( i - int(sales)) + ')' for i in range(int(sales + rnd + 1), int(sales + rnd + oh + 1)): next_row = h.lastRow('Employees', wb, 5) + 1 sht.range('C' + str(next_row)).value = '=OperatingAssumptions!$H' + str( i * 6) sht.range('E' + str(next_row) + ':P' + str(next_row)).value = '=employee_cost(E2, Overhead' + str( i - int(sales) - int(rnd)) + ')' sht.range('C' + str(next_row + 2)).value = [['Full Time']] sht.range('E' + str(next_row + 2) + ':P' + str(next_row + 2)).value = '=SUM(E' + str(next_row - int(sales) - int(rnd) - int(oh) + 1) + ':E' + str(next_row) + ')' sht.range('C' + str(next_row + 3)).value = [['Taxes & Benefits']] sht.range('E' + str(next_row + 3) + ':P' + str(next_row + 3)).value = '=tax_and_benefits(E' + str( next_row + 2) + ', EmployeeBonus)' sht.range('C' + str(next_row + 4)).value = [['Total Employee Cost']] sht.range('E' + str(next_row + 4) + ':P' + str(next_row + 4)).value = '=SUM(E' + str( next_row + 2) + ':E' + str(next_row + 3) + ')' sht.range('C' + str(next_row + 2) + ':P' + str(next_row + 4)).name = 'TotalEmployeeCost'
def create_products(idx, wb, prod_num, retail, ws, event, ecom): sht = wb.sheets[idx] for i in range(int(prod_num)): next_row = h.lastRow(idx, wb, 4) + 1 product_info = [['Type'], ['Product Name'], ['Price and Revenue Share'], ['Price'], ['Annual Price Change'], ['Third Party Revenue Share'], ['Customer Acquisition Cost'], ['Sales Commission'], ['Sales per Month per Salesperson'], ['Sales Base Salary'], ['Cost of Good'], ['Cost'], ['Annual Cost Change'], ['Direct Labor Hours'], ['Direct Labor Wage per Hour'], ['Shipping Expense'], ['Payment Processing Percentage'], ['Acct Mgmt & Support by Revenue'], ['Acct Mgmt Employee Base Salary'], ['Inventory'], ['Beginning Inventory'], ['Reorder Point'], ['Growth Frequency'], ['Growth Amount'], ['Channels']] num_of_defaults = [] if retail == "yes": product_info += [['Stores']] num_of_defaults += [['yes']] if ws == "yes": product_info += [['Wholesale']] num_of_defaults += [['yes']] if event == "yes": product_info += [['Events']] num_of_defaults += [['yes']] if ecom == "yes": product_info += [['Website']] num_of_defaults += [['yes']] sht.range('D' + str(next_row)).value = product_info sht.range('E' + str(next_row + 1)).color = (255, 242, 204) sht.range('E' + str(next_row + 3) + ":E" + str(next_row + 5)).color = (255, 242, 204) sht.range('E' + str(next_row + 7) + ":E" + str(next_row + 9)).color = (255, 242, 204) sht.range('E' + str(next_row + 11) + ":E" + str(next_row + 18)).color = (255, 242, 204) sht.range('E' + str(next_row + 20) + ":E" + str(next_row + 23)).color = (255, 242, 204) sht.range('E' + str(next_row + 25) + ":E" + str(next_row + (len(product_info) - 1))).color = (255, 242, 204) sht.range('E' + str(next_row + 1)).value = "Product " + str(i + 1) sht.range('E' + str(next_row + 3) + ":E" + str(next_row + 5)).value = [[20], [0], [0]] sht.range('E' + str(next_row + 7) + ":E" + str(next_row + 9)).value = [[0], [0], [0]] sht.range('E' + str(next_row + 11) + ":E" + str(next_row + 18)).value = [[5000], [0], [0], [0], [1250], [0.02], [100000], [40000]] sht.range('E' + str(next_row + 20) + ":E" + str(next_row + 23)).value = [[600], [100], [1], [0]] sht.range('E' + str(next_row + 25) + ":E" + str(next_row + (len(product_info) - 1))).value = num_of_defaults sht.range('D' + str(next_row + 1) + ":E" + str(next_row + (len(product_info) - 1))).name = "Product" + str(i + 1)
def create_sales_for_products(wb, sht, products, product): for i in range(1, int(products + 1)): next_row = h.lastRow('SalesDetail', wb, 5) + 2 sht.range('B' + str(next_row)).value = '=OperatingAssumptions!$E' + str( i * (len(product.rows) + 1) - (len(product.rows) - 3)) sht.range('D' + str(next_row)).value = [['Retail Price'], ['WS Price'], ['Total Sales'], ['Retail Units'], ['WS Units']] sht.range('E' + str(next_row) + ':P' + str(next_row)).value = '=OperatingAssumptions!$E' + str( i * (len(product.rows) + 1) - (len(product.rows) - 5)) sht.range('E' + str(next_row + 1) + ':P' + str(next_row + 1)).value = '=get_ws_price(E' + str( next_row) + ', OperatingAssumptions!$E' + str( i * (len(product.rows) + 1) - (len(product.rows) - 7)) + ')' sht.range('E' + str(next_row + 2) + ':P' + str(next_row + 2)).value = '=total_sales(E' + str( next_row) + ', E' + str(next_row + 1) + ', E' + str( next_row + 3) + ', E' + str(next_row + 4) + ')' sht.range('E' + str(next_row + 4) + ':P' + str(next_row + 4) ).value = '=wholesale_units(E2, Wholesale, Product' + str( i) + ', All_Products)' sht.range('E' + str(next_row + 3) + ':P' + str(next_row + 3)).value = '=retail_units(Inventory!E' + str( next_row + 1) + ', E' + str(next_row + 4) + ')' last_row = h.lastRow('SalesDetail', wb, 5) sht.range('E4:P' + str(last_row)).name = 'AllSales' sht.range('D' + str(last_row + 2)).value = 'Total Retail Sales' sht.range('D' + str(last_row + 3)).value = 'Total WS Sales' sht.range('D' + str(last_row + 4)).value = 'Total All Sales' sht.range( 'E' + str(last_row + 2) + ':P' + str(last_row + 2) ).value = '=all_products_retail_sales(E2, AllSales,' + str(products) + ')' sht.range( 'E' + str(last_row + 3) + ':P' + str(last_row + 3) ).value = '=all_products_ws_sales(E2, AllSales,' + str(products) + ')' sht.range('E' + str(last_row + 4) + ':P' + str(last_row + 4)).value = '=SUM(E' + str( last_row + 2) + ':E' + str(last_row + 3) + ')' sht.range('E' + str(last_row + 4) + ':P' + str(last_row + 4)).name = 'AllProductSales'
def create_job(idx, wb, num_of_jobs, job_type): sht = wb.sheets[idx] next_row = h.lastRow(idx, wb, 7) + 1 sht.range('G' + str(next_row)).value = job_type for i in range(int(num_of_jobs)): next_row = h.lastRow(idx, wb, 7) + 1 sht.range('G' + str(next_row)).value = [['Job Title'], ['Base Salary'], ['Base Number of Employees'], ['Initial Month'], ['Growth Frequency'], ['Growth Amount']] sht.range('H' + str(next_row) + ":H" + str(next_row + 5)).color = (255, 242, 204) sht.range('H' + str(next_row) + ":H" + str(next_row + 5)).value = [[job_type + str(i + 1)], [40000], [1], [1], [6], [1]] sht.range('G' + str(next_row) + ":H" + str(next_row + 5)).name = job_type + str(i + 1)
def create_pl(wb, sht, expenses): shtemp = wb.sheets['Employees'] sht.range('B4').value = 'Total Income' sht.range('E4:P4').value = '=total_income(E2, AllProductSales)' sht.range('B6').value = 'Total Cost of Sales' sht.range('E6:P6').value = '=total_cost(E2, AllProductCost)' sht.range('B8').value = 'Gross Margin' sht.range('E8:P8').value = '=E4 - E6' sht.range('B10').value = 'Fixed Business Expenses' sht.range('C11:P13').value = shtemp.range('TotalEmployeeCost').value for i in range(0, len(expenses)): sht.range('C' + str(i + 14)).value = expenses[i][0] sht.range('E' + str(i + 14) + ':P' + str(i + 14)).value = expenses[i][1] next_row = h.lastRow('P&L', wb, 5) + 1 sht.range('E14:P' + str(next_row - 1)).name = 'FixedExpPL' sht.range('B' + str(next_row)).value = 'Total Fixed Business Expenses' sht.range('E' + str(next_row) + ':P' + str(next_row)).value = '=SUM(E13:E' + str(next_row - 1) + ')' sht.range('B' + str(next_row + 2)).value = 'EBITDA' sht.range('E' + str(next_row + 2) + ':P' + str(next_row + 2)).value = '=E8 - E' + str(next_row) sht.range('E' + str(next_row + 2) + ':P' + str(next_row + 2)).name = 'EBITDA' sht.range('B' + str(next_row + 4)).value = 'Other Expenses' sht.range('C' + str(next_row + 5)).value = 'Amortization' sht.range('E' + str(next_row + 5) + ':P' + str(next_row + 5)).value = '=amortization_amt(E2, Amortization)' sht.range('C' + str(next_row + 6)).value = 'Depreciation' sht.range('E' + str(next_row + 6) + ':P' + str(next_row + 6)).value = '=depr_amt(E2, Depreciation)' sht.range('C' + str(next_row + 7)).value = 'Prefered Return' # sht.range('E' + str(next_row + 7) + ':P' + str(next_row + 7)).value = '=' sht.range('C' + str(next_row + 8)).value = 'Interest' sht.range('E' + str(next_row + 8) + ':P' + str(next_row + 8)).value = '=int_amt(E2, Interest)' sht.range('C' + str(next_row + 9)).value = 'Tax' # sht.range('E' + str(next_row + 9) + ':P' + str(next_row + 9)).value = '=' sht.range('B' + str(next_row + 10)).value = 'Total Other Expenses' sht.range('E' + str(next_row + 10) + ':P' + str(next_row + 10)).value = '=SUM(E' + str( next_row + 5) + ':E' + str(next_row + 9) + ')' sht.range('B' + str(next_row + 12)).value = 'Net Income' sht.range('E' + str(next_row + 12) + ':P' + str(next_row + 12) ).value = '=E' + str(next_row + 2) + ' - E' + str(next_row + 10)
def create_inv_for_products(wb, sht, products, product): for i in range(1, int(products + 1)): next_row = h.lastRow('Inventory', wb, 5) + 2 sht.range('B' + str(next_row)).value = '=OperatingAssumptions!$E' + str( i * (len(product.rows) + 1) - (len(product.rows) - 3)) sht.range('D' + str(next_row)).value = [['Starting Units'], ['Units Sold'], ['Units Added'], ['$ Inv Added'], ['Remaining']] # sht.range('E' + str(next_row)).value = '=OperatingAssumptions!$E' + str(i * (len(product.rows) + 1) - (len(product.rows) - 22)) sht.range('E' + str(next_row)).value = 0 sht.range('F' + str(next_row) + ':P' + str(next_row)).value = '=E' + str(next_row + 4) sht.range( 'E' + str(next_row + 1) + ':P' + str(next_row + 1) ).value = '=units_sold_channel_method(E2, All_Channels, Product' + str( i) + ', All_Products)' sht.range( 'E' + str(next_row + 2) + ':P' + str(next_row + 2) ).value = '=units_added(E' + str(next_row) + ', E' + str( next_row + 1) + ', OperatingAssumptions!$E' + str( i * (len(product.rows) + 1) - (len(product.rows) - 23)) + ', OperatingAssumptions!$E' + str( i * (len(product.rows) + 1) - (len(product.rows) - 22)) + ')' sht.range('E' + str(next_row + 2) + ':P' + str(next_row + 2)).name = 'InvAdded' sht.range( 'E' + str(next_row + 3) + ':P' + str(next_row + 3) ).value = '=inv_cost_added(E2, InvAdded, Product' + str(i) + ')' sht.range('E' + str(next_row + 4) + ':P' + str(next_row + 4) ).value = '=remaining_units(E' + str(next_row) + ', E' + str( next_row + 1) + ', E' + str(next_row + 2) + ')' sht.range('E4:P' + str(next_row + 4)).name = 'AllInv' sht.range('D' + str(next_row + 6)).value = "Total Inventory Cost Added" sht.range( 'E' + str(next_row + 6) + ':P' + str(next_row + 6) ).value = '=total_inventory_cost_added(E2, AllInv, ' + str(products) + ')' sht.range('E' + str(next_row + 6) + ':P' + str(next_row + 6)).name = 'TotalInvAddedRow'
def create_loan(wb, sht, loan, seed_rds): seed_rds = int(seed_rds) for i in range(0, (int(loan[3][1]) * 12) + 1): if i == 0: sht.range('A5').value = i if seed_rds == 0: sht.range( 'E5' ).value = '=OperatingAssumptions!$T4 - (OperatingAssumptions!$T4 * OperatingAssumptions!$T6)' else: sht.range('E5').value = '=OperatingAssumptions!$T' + str( (seed_rds * 4) + 5) + ' - (OperatingAssumptions!$T' + str( (seed_rds * 4) + 5) + ' * OperatingAssumptions!$T' + str( (seed_rds * 4) + 7) + ')' else: sht.range('A' + str(i + 5)).value = i if seed_rds == 0: sht.range('F' + str(i + 5)).value = '=OperatingAssumptions!$T5' sht.range('B' + str(i + 5)).value = '=-PMT(F' + str( i + 5) + '/12, (OperatingAssumptions!$T7*12), E' + str(i + 4) + ')' else: sht.range('F' + str(i + 5)).value = '=OperatingAssumptions!$T' + str( (seed_rds * 4) + 6) sht.range('B' + str(i + 5)).value = '=-PMT(F' + str( i + 5) + '/12, (OperatingAssumptions!$T' + str( (seed_rds * 4) + 8) + '*12) - A' + str(i + 4) + ', E' + str(i + 4) + ')' sht.range('C' + str(i + 5)).value = '=ROUND(E' + str( i + 4) + '*F' + str(i + 5) + '/12, 2)' sht.range('D' + str(i + 5)).value = '=B' + str(i + 5) + '-C' + str(i + 5) sht.range('E' + str(i + 5)).value = '=E' + str(i + 4) + '-D' + str(i + 5) last_row = h.lastRow('LoanSchedule', wb, 3) sht.range('C6:C' + str(last_row)).name = 'Interest' sht.range('E5:E' + str(last_row)).name = 'LoanBalance'
def create_assets(wb, sht, tang_assets, intang_assets): sht.range('A3').value = [['Depreciation']] for i in range(1, int(tang_assets + 1)): if i == 1: next_row = h.lastRow('AssetSchedule', wb, 5) + 2 else: next_row = h.lastRow('AssetSchedule', wb, 5) + 1 sht.range('D' + str(next_row)).value = '=OperatingAssumptions!$Q' + str( i * 4) sht.range( 'E' + str(next_row) + ':P' + str(next_row) ).value = '=asset_monthly_cost(E2, OperatingAssumptions!$Q' + str( (i * 4) + 1) + ', OperatingAssumptions!$Q' + str( (i * 4) + 2) + ', OperatingAssumptions!$Q' + str((i * 4) + 3) + ')' next_row = h.lastRow('AssetSchedule', wb, 5) + 1 sht.range('D' + str(next_row)).value = [['Total']] sht.range('E' + str(next_row) + ':P' + str(next_row)).value = '=SUM(E' + str( next_row - int(tang_assets)) + ':E' + str(next_row - 1) + ')' sht.range('E' + str(next_row) + ':P' + str(next_row)).name = 'Depreciation' sht.range('A' + str(next_row + 2)).value = [['Amortization']] for i in range(int(tang_assets + 1), int(intang_assets + tang_assets + 1)): if i == int(tang_assets + 1): next_row = h.lastRow('AssetSchedule', wb, 5) + 3 else: next_row = h.lastRow('AssetSchedule', wb, 5) + 1 sht.range('D' + str(next_row)).value = '=OperatingAssumptions!$Q' + str( (i * 4) + 1) sht.range( 'E' + str(next_row) + ':P' + str(next_row) ).value = '=asset_monthly_cost(E2, OperatingAssumptions!$Q' + str( (i * 4) + 2) + ', OperatingAssumptions!$Q' + str( (i * 4) + 3) + ', OperatingAssumptions!$Q' + str((i * 4) + 4) + ')' next_row = h.lastRow('AssetSchedule', wb, 5) + 1 sht.range('D' + str(next_row)).value = [['Total']] sht.range('E' + str(next_row) + ':P' + str(next_row)).value = '=SUM(E' + str(next_row - int( intang_assets)) + ':E' + str(next_row - 1) + ')' sht.range('E' + str(next_row) + ':P' + str(next_row)).name = 'Amortization'
def create_bonus(idx, wb): sht = wb.sheets[idx] next_row = h.lastRow(idx, wb, 7) + 1 sht.range('G' + str(next_row)).value = [['Bonus and Other Employee Expenses'], ['Recruiting Costs'], ['Computer Equipment'], ['Software and Licensing'], ['Communication Services'], ['Travel & Entertainment'], ['Outside Services'], ['Subscription and Dues'], ['Annual Bonus'], ['Benefits'], ['Tax Percentage'], ['Training Costs'], ['Annual Raise'], ['Employee Turnover Decimal']] sht.range('H' + str(next_row + 1) + ":H" + str(next_row + 13)).color = (255, 242, 204) sht.range('H' + str(next_row + 1) + ":H" + str(next_row + 13)).value = [[0], [0], [0], [0], [0], [0], [0], [0], [263], [5.6], [0], [0], [0]] sht.range('G' + str(next_row + 1) + ":H" + str(next_row + 13)).name = 'EmployeeBonus'
def create_financials(idx, wb): sht = wb.sheets[idx] next_row = h.lastRow(idx, wb, 13) + 1 sht.range('M' + str(next_row)).value = [['Income Statement'], ['Interest Rate'], ['Discount Rate'], ['Bad Debt Percentage'], ['Existing Loss Carry Forward'], ['Balance Sheet'], ['Opening Cash Balance'], ['Acct Receivable Days'], ['Acct Payable Days'], ['Days of Inventory on Hand']] sht.range('N' + str(next_row + 1) + ":N" + str(next_row + 4)).color = (255, 242, 204) sht.range('N' + str(next_row + 1) + ":N" + str(next_row + 4)).value = [[4.25], [0.02], [0.01], [0]] sht.range('N' + str(next_row + 6) + ":N" + str(next_row + 9)).color = (255, 242, 204) sht.range('N' + str(next_row + 6) + ":N" + str(next_row + 9)).value = [[0], [30], [60], [60]]
def create_other(idx, wb): sht = wb.sheets[idx] next_row = h.lastRow(idx, wb, 10) + 2 sht.range('J' + str(next_row)).value = [['Additional Ad & Marketing'], ['Bank Fees'], ['Insurance'], ['License Fees & Permits'], ['Legal & Professional Fees'], ['Office Expenses & Supplies'], ['Rent'], ['Miscellaneous'], ['Utilities'], ['Website']] sht.range('K' + str(next_row) + ":K" + str(next_row + 9)).color = (255, 242, 204) sht.range('K' + str(next_row) + ":K" + str(next_row + 9)).value = [[3000], [300], [274], [0], [500], [100], [3000], [0], [300], [100]]
def create_cap(idx, wb): sht = wb.sheets[idx] next_row = h.lastRow(idx, wb, 19) + 1 sht.range('S' + str(next_row)).value = [['Debt'], ['Loan Amount'], ['Interest Rate'], ['Down Payment Percentage'], ['Length of Loan'], ['Exit Assumptions'], ['Founder Shares'], ['Convertible Note'], ['Liquidation Preference'], ['Exit Valuation Method'], ['Exit Valuation Multiple']] sht.range('T' + str(next_row + 1) + ':T' + str(next_row + 4)).color = (255, 242, 204) sht.range('T' + str(next_row + 1) + ':T' + str(next_row + 4)).value = [[500000], [0.0525], [0.20], [20]] sht.range('T' + str(next_row + 6) + ':T' + str(next_row + 10)).color = (255, 242, 204) sht.range('T' + str(next_row + 6) + ':T' + str(next_row + 10)).value = [[1000000], [0], [False], ['EBITDA'], [9.73]] sht.range('S' + str(next_row + 1) + ':T' + str(next_row + 4)).name = 'Loan'
def create_ops(wb): # sheets shtb = wb.sheets['BasicAssumptions'] shtop = wb.sheets['OperatingAssumptions'] # values retail = str(shtb.range('B7').value).lower() # yes or no wholesale = shtb.range('B8').value.lower() # yes or no event = shtb.range('B9').value.lower() # yes or no ecommerce = shtb.range('B10').value.lower() # yes or no num_of_products = shtb.range('B13').value # int sales_marketing_roles = shtb.range('B16').value # int r_and_d_roles = shtb.range('B17').value # int overhead_roles = shtb.range('B18').value # int tang_assets = shtb.range('B21').value # int intang_assets = shtb.range('B22').value # int seed_rounds = shtb.range('B25').value # int # create sheet shtop.range('A1').value = [['Operating Assumptions'], ['Income']] shtop.range('D2').value = "Products" shtop.range('G2').value = "Staff" shtop.range('J2').value = "Other Expenses" shtop.range('M2').value = "Financials" shtop.range('P2').value = "Asset Investment" shtop.range('S2').value = "Capitalization" if retail == 'yes': create_channel('OperatingAssumptions', wb, 'Stores') if wholesale == 'yes': create_channel('OperatingAssumptions', wb, 'Wholesale') if event == 'yes': create_channel('OperatingAssumptions', wb, 'Events') if ecommerce == 'yes': create_channel('OperatingAssumptions', wb, 'Website', 'Base Visitors', 'Conversion Rate', 'Base %') chan_end_row = h.lastRow('OperatingAssumptions', wb, 1) shtop.range('A3:B' + str(chan_end_row)).name = "All_Channels" if num_of_products is not None: create_products('OperatingAssumptions', wb, num_of_products, retail, wholesale, event, ecommerce) prod_end_row = h.lastRow('OperatingAssumptions', wb, 4) shtop.range('D3:E' + str(prod_end_row)).name = "All_Products" if sales_marketing_roles is not None: create_job('OperatingAssumptions', wb, sales_marketing_roles, "General_Sales_and_Marketing") if r_and_d_roles is not None: create_job('OperatingAssumptions', wb, r_and_d_roles, "Research_and_Development") if overhead_roles is not None: create_job('OperatingAssumptions', wb, overhead_roles, "Overhead") create_bonus('OperatingAssumptions', wb) create_other('OperatingAssumptions', wb) create_financials('OperatingAssumptions', wb) if tang_assets is not None: create_assets('OperatingAssumptions', wb, tang_assets, "Furniture, Fixtures, & Equipment") if intang_assets is not None: create_assets('OperatingAssumptions', wb, intang_assets, "Intangible Assets") if seed_rounds is not None: create_seed('OperatingAssumptions', wb, seed_rounds) create_cap('OperatingAssumptions', wb) cap_end_row = h.lastRow('OperatingAssumptions', wb, 20) shtop.range('S4:T' + str(cap_end_row)).name = 'Capitalization' shtop.autofit('c') wb.macro('CreateButton')()
def create_bs(wb, sht, intang_assets, tang_assets, seed_rds): sht.range('A3').value = 'Assets' sht.range('B4').value = 'Current Assets' sht.range('C5').value = 'Cash' sht.range('C6').value = 'Accounts Receivable' sht.range('E6:P6').value = "=OperatingAssumptions!$N$10/30 * 'P&L'!E4" sht.range('C7').value = 'Raw Materials' sht.range('C8').value = 'Inventory' sht.range('E8:P8').value = "=inventory(E2, 'P&L'!E6, TotalInvAddedRow, D8)" sht.range('C9').value = 'Prepaid Expenses' sht.range('C10').value = 'Other Current' sht.range('B11').value = 'Total Current Assets' sht.range('E11:P11').value = '=SUM(E5:E10)' sht.range('B13').value = 'Intangible Assets' for i in range(int(tang_assets + 1), int(intang_assets + tang_assets + 1)): if i == int(tang_assets + 1): next_row = h.lastRow('BS', wb, 5) + 3 else: next_row = h.lastRow('BS', wb, 5) + 1 sht.range('C' + str(next_row)).value = '=OperatingAssumptions!$Q' + str( (i * 4) + 1) sht.range('E' + str(next_row) + ':P' + str(next_row)).value = '=OperatingAssumptions!$Q' + str( (i * 4) + 3) sht.range('B' + str(next_row + 1)).value = 'Total Intangible Assets' sht.range('E' + str(next_row + 1) + ':P' + str(next_row + 1)).value = '=SUM(E13:E' + str(next_row) + ')' sht.range('E' + str(next_row + 1) + ':P' + str(next_row + 1)).name = 'IntangibleAssets' sht.range('B' + str(next_row + 3)).value = 'Less: Accumulated Amortization' sht.range('E' + str(next_row + 3)).value = '=-amortization_amt(E2, Amortization)' sht.range('F' + str(next_row + 3) + ':P' + str(next_row + 3)).value = '= E' + str( next_row + 3) + ' - amortization_amt(F2, Amortization)' sht.range('E' + str(next_row + 3) + ':P' + str(next_row + 3)).name = 'AccumAmortization' sht.range('B' + str(next_row + 5)).value = 'Fixed Assets' for i in range(1, int(tang_assets + 1)): if i == 1: next_row = h.lastRow('BS', wb, 5) + 3 else: next_row = h.lastRow('BS', wb, 5) + 1 sht.range('C' + str(next_row)).value = '=OperatingAssumptions!$Q' + str( i * 4) sht.range('E' + str(next_row) + ':P' + str(next_row)).value = '=OperatingAssumptions!$Q' + str( (i * 4) + 2) sht.range('B' + str(next_row + 1)).value = 'Total Fixed Assets' sht.range('E' + str(next_row + 1) + ':P' + str(next_row + 1)).value = '=SUM(E' + str( next_row - int(tang_assets)) + ':E' + str(next_row) + ')' sht.range('E' + str(next_row + 1) + ':P' + str(next_row + 1)).name = 'FixedAssetRow' sht.range('B' + str(next_row + 3)).value = 'Less: Accumulated Depreciation' sht.range('E' + str(next_row + 3)).value = '=-depr_amt(E2, Depreciation)' sht.range('F' + str(next_row + 3) + ':P' + str(next_row + 3)).value = '= E' + str(next_row + 3) + ' - depr_amt(F2, Depreciation)' sht.range('E' + str(next_row + 3) + ':P' + str(next_row + 3)).name = 'AccumDepreciation' sht.range('A' + str(next_row + 5)).value = 'Total Assets' sht.range('E' + str(next_row + 5) + ':P' + str(next_row + 5)).value = '=E11+E' + str( next_row - int(tang_assets) - 4) + '+E' + str(next_row - int(tang_assets) - 2) + '+E' + str(next_row + 1) + '+E' + str(next_row + 3) sht.range('A' + str(next_row + 8)).value = "Liabilities and Owner's Equity" sht.range('B' + str(next_row + 9)).value = 'Liabilities' sht.range('C' + str(next_row + 10)).value = 'Accounts Payable' sht.range( 'E' + str(next_row + 10) + ':P' + str(next_row + 10) ).value = "=OperatingAssumptions!$N$11/30 * acct_payable(E2, FixedExpPL, 'P&L'!E6)" sht.range('E' + str(next_row + 10) + ':P' + str(next_row + 10)).name = 'AccountsPayableRow' sht.range('C' + str(next_row + 11)).value = 'Loan Payable' sht.range('E' + str(next_row + 11) + ':P' + str(next_row + 11)).value = '=loan_payable(E2, LoanBalance)' sht.range('E' + str(next_row + 11) + ':P' + str(next_row + 11)).name = 'LoanPayable' sht.range('C' + str(next_row + 12)).value = 'Mortgage Payable' sht.range('C' + str(next_row + 13)).value = 'Credit Card Debt' sht.range('C' + str(next_row + 14)).value = 'Line of Credit Balance' sht.range('B' + str(next_row + 15)).value = 'Total Liabilities' sht.range('E' + str(next_row + 15) + ':P' + str(next_row + 15)).value = "=SUM(E" + str( next_row + 9) + ":E" + str(next_row + 14) + ")" sht.range('B' + str(next_row + 17)).value = "Owner's Equity" sht.range('C' + str(next_row + 18)).value = "Capital Stock" sht.range('E' + str(next_row + 18) + ':P' + str(next_row + 18)).value = '=capital_stock(E2,' + str( seed_rds) + ', Capitalization)' sht.range('E' + str(next_row + 18) + ':P' + str(next_row + 18)).name = 'CapitalStock' sht.range('C' + str(next_row + 19)).value = "Retained Earnings" sht.range('E' + str(next_row + 19) + ':P' + str(next_row + 19) ).value = '=retained_earnings(E2, EBITDA, Interest, D' + str( next_row + 19) + ')' sht.range('B' + str(next_row + 20)).value = "Total Owner's Equity" sht.range('E' + str(next_row + 20) + ':P' + str(next_row + 20)).value = "=SUM(E" + str( next_row + 17) + ":E" + str(next_row + 19) + ")" sht.range('A' + str(next_row + 22)).value = "Total Liabilities and Owner's Equity" sht.range('E' + str(next_row + 22) + ':P' + str(next_row + 22) ).value = "=E" + str(next_row + 15) + "+E" + str(next_row + 20)
def main(): # workbook and sheets wb = xw.Book.caller() shtb = wb.sheets['BasicAssumptions'] shtop = wb.sheets['OperatingAssumptions'] num_of_products = shtb.range('B13').value product = xw.Range('Product1') sales = int(shtb.range('B16').value) rnd = int(shtb.range('B17').value) overhead = int(shtb.range('B18').value) tang_assets = shtb.range('B21').value intang_assets = shtb.range('B22').value loan = shtop.range('Loan').value seed_rds = shtb.range('B25').value last_row_fixed_exp = h.lastRow('OperatingAssumptions', wb, 10) shtop.range('J4:K' + str(last_row_fixed_exp)).name = 'FixedExpenses' f_expenses = shtop.range('FixedExpenses').value # Dataframes # income, products, staff, other, fin, asset, cap = makeDataFrames(wb, shtop) # Inventory h.create_new_sheet(wb, 'Inventory', 'OperatingAssumptions') shtinv = wb.sheets['Inventory'] shtinv.range('A1').value = 'Inventory' shtinv.range('E1').value = 'Month' shtinv.range('E2').value = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12] create_inv_for_products(wb, shtinv, num_of_products, product) # Sales Detail by Product h.create_new_sheet(wb, 'SalesDetail', 'Inventory') shtsales = wb.sheets['SalesDetail'] shtsales.range('A1').value = 'Sales Detail' shtsales.range('E1').value = 'Month' shtsales.range('E2').value = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12] create_sales_for_products(wb, shtsales, num_of_products, product) # Cost Detail by Product h.create_new_sheet(wb, 'CostDetail', 'SalesDetail') shtcos = wb.sheets['CostDetail'] shtcos.range('A1').value = 'Cost Detail' shtcos.range('E1').value = 'Month' shtcos.range('E2').value = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12] create_cost_for_products(wb, shtcos, num_of_products, product) # Employees h.create_new_sheet(wb, 'Employees', 'CostDetail') shtemp = wb.sheets['Employees'] shtemp.range('A1').value = 'Employees' shtemp.range('E1').value = 'Month' shtemp.range('E2').value = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12] create_employees(wb, shtemp, sales, rnd, overhead) # Asset Schedule h.create_new_sheet(wb, 'AssetSchedule', 'Employees') shtass = wb.sheets['AssetSchedule'] shtass.range('A1').value = 'Asset Schedule' shtass.range('E1').value = 'Month' shtass.range('E2').value = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12] create_assets(wb, shtass, tang_assets, intang_assets) # Loan Schedule h.create_new_sheet(wb, 'LoanSchedule', 'AssetSchedule') shtloan = wb.sheets['LoanSchedule'] shtloan.range('A1').value = 'Loan Schedule' shtloan.range('A4').value = [ 'Period', 'PMT', 'Interest Paid', 'Principal', 'Balance', 'Annual Rate', 'Additional Payment' ] create_loan(wb, shtloan, loan, seed_rds) # Profit and Loss h.create_new_sheet(wb, "P&L", "LoanSchedule") shtpl = wb.sheets['P&L'] shtpl.range('A1').value = 'Projected Income Statement' shtpl.range('E1').value = 'Month' shtpl.range('E2').value = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12] create_pl(wb, shtpl, f_expenses) # Balance sheet h.create_new_sheet(wb, "BS", "P&L") shtbs = wb.sheets['BS'] shtbs.range('A1').value = 'Balance Sheet' shtbs.range('E1').value = 'Month' shtbs.range('E2').value = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12] create_bs(wb, shtbs, intang_assets, tang_assets, seed_rds) # Cash Flow h.create_new_sheet(wb, "CF", "BS") shtcf = wb.sheets['CF'] shtcf.range('A1').value = 'Cash Flow Statement' shtcf.range('E1').value = 'Month' shtcf.range('E2').value = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12] create_cf(wb, shtcf) shtbs.range('E5:P5').value = '=CF!E36'